Upgrade Traccar 5.9 and migrate from MySQL to SQL Server
You can upgrade directly from 5.9 to the latest. Running database anywhere is supported, but remote is not recommended.
I would definitely recommend upgrading on MySQL first instead of trying to combine the two things.
Thank you for the clarification.
When you say that a remote database is not recommended, does this also apply when the Traccar application and database are on separate VMs within the same cloud region and private network, or mainly when there is higher network latency between them?
Even if it's the same network and region, network latency is much higher than local access.
Okay, thank you!!
One final question regarding the database migration.
From the Traccar application perspective, is there any recommended or preferred approach for migrating an existing production database from MySQL to SQL Server while preserving historical data?
Does Traccar have any specific recommendation for migrating from MySQL to Microsoft SQL Server, or is this considered a standard database migration with no application-specific concerns?
Are there any known limitations, performance impacts, or compatibility considerations in Traccar when using SQL Server instead of MySQL?
Thank you.
Nothing Traccar specific to worry about, but make sure you migrate everything, including constraints.
Hi Anton,
We ran SSMA to assess the MySQL-to-SQL Server migration. It flagged foreign-key conversion issues involving TC_DEVICE_GEOFENCE, TC_DEVICE_NOTIFICATION, TC_DEVICE_REPORT and TC_GROUPS (we are confirming one additional affected object). SSMA changed some ON DELETE CASCADE actions to NO ACTION because of multiple cascade paths or a circular reference.
Since you advised us to preserve all constraints, is this change compatible with Traccar’s expected deletion behavior? If not, what approach would you recommend for these relationships on SQL Server?
It can cause issues. But I think it's mostly MS SQL limitation that you have to live with.
Thanks. We understand this is a SQL Server limitation. Since SSMA changes these relationships from ON DELETE CASCADE to NO ACTION, do you know which Traccar deletion operations may be affected? We’ll test the affected flows, but want to make sure we cover the expected application behavior.
Which specific constraints did it change? I believe the only potentially problematic was groups.
This is de query:
SSMA error messages:
* M2SS0041: ON DELETE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
Source:
CREATE
TABLE `tc_user_user`
(
`userid` int NOT NULL,
`manageduserid` int NOT NULL,
KEY `fk_user_user_userid` (`userid`) ,
KEY `fk_user_user_manageduserid` (`manageduserid`) ,
CONSTRAINT `fk_user_user_manageduserid` FOREIGN KEY (`manageduserid`) REFERENCES `tc_users` (`id`) ON DELETE CASCADE ,
CONSTRAINT `fk_user_user_userid` FOREIGN KEY (`userid`) REFERENCES `tc_users` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
Target:
/*
* SSMA informational messages:
* M2SS0003: The following SQL clause was ignored during conversion: COLLATE = utf8mb4_0900_ai_ci.
*/
CREATE TABLE TRACCAR.[tc_user_user]
(
userid int NOT NULL,
manageduserid int NOT NULL,
/*
* SSMA error messages:
* M2SS0041: ON DELETE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
CONSTRAINT [tc_user_user$fk_user_user_manageduserid] FOREIGN KEY (manageduserid) REFERENCES traccar.tc_users (id)
ON DELETE NO ACTION
*/
,
CONSTRAINT [tc_user_user$fk_user_user_userid] FOREIGN KEY (userid) REFERENCES traccar.tc_users (id)
ON DELETE CASCADE
)
GO
CREATE
NONCLUSTERED INDEX fk_user_user_userid
ON TRACCAR.[tc_user_user] (userid ASC)
GO
CREATE
NONCLUSTERED INDEX fk_user_user_manageduserid
ON TRACCAR.[tc_user_user] (manageduserid ASC)
GO
That's only relevant if you use manager users.
Hello,
We are planning to modernize our current Traccar environment.
Current environment:
Traccar 5.9
MySQL 8.0
Traccar and MySQL running on the same server
Existing production database with historical tracking data
Target environment:
Upgrade Traccar to a current 6.x release
Run Traccar and the database on separate servers
Potentially migrate from MySQL to Microsoft SQL Server
Could you please advise:
Is a direct upgrade from Traccar 5.9 to the current 6.x release supported, or should we use intermediate versions?
Is running Traccar and the database on separate servers fully supported?
For preserving the existing history, is there a recommended approach to migrate from MySQL to SQL Server, or would you recommend upgrading Traccar on MySQL first and changing the database in a separate step?
We will validate everything in a non-production environment and keep backups of the database, configuration, and media files.
Thank you.