New-Ish install I want to Convert to MSSQL
Have you tried asking AI about it? This is something it should be pretty good at.
I've got it going now. I did use AI - Or at least Googles AI in the search results - Should have checked if Claude was any better.
The issue was the AI told me to look in preferences of DBCopy and look for "Include Identity/Auto-increment columns" but it wasn't there and I couldn't see anything related.
In the end I copy / pasted tables one by one running the SQL Commands SET IDENTITY_INSERT TARGET_TABLE ON whenever I got the error as I found a lot of tables didn't give the error - I think these were tables that had no records. It was a bit laborious but its done now and I've updated the config and its talking to the MSSQL server again and all of my historic records are there. I'm just about to go for a drive and make sure new records work ok and then I'll call it a success.
I do wonder if there is a better migration tool that is still free but a but cleverer than SQUIRREL but it worked and its done now.
Would I expect any speed improvements in the DB change or just greater robustness??
The reports don't seem any faster - Calling up stops for prior week takes about 13 seconds - I didn't try before and after to accurately compare but it feels about the same - Is it reverse geocoding each stop when I call this report? Not really a big issue for my needs but based on the server specs - All SSD based Xeon Quad core with ~7GB available memory I wouldn't expect the PC to be affecting speed.
It probably won't be faster, but H2 can get corrupted randomly and you lose everything. Personally I would recommend TimescaleDB because then you can easily set history retention.
I'm very much a Microsoft Windows server geek - I've been running Windows Server at home for my small business since SBS 2003 and administering servers for clients since NT4 (before that Novel Netware 3.12 and 4). I've got the infrastructure there and running so prefer to use it rather than putting another service on I'm inexperienced in maintaining.
I'm 52 and slow to change my ways now from my nimble IT youth where constant changes and enhancements were all part of the fun.
I've put traccar in as I have an offline GPS logger that I upload once a month to track onsite billable time and the service is ditching the offline support and insisting on online subscriptions so I figured better to do it myself with some upfront cost and no ongoings. Also means I've just ordered a cheaper 4G tracker from Aliexpress to throw in the Caravan for theft security and tracking our holiday history!
If it's a small scale, then any database other than H2 should be just fine.
I have a new install thats been running exceptionally well for 3 weeks. The data isn't critical but I'd like to keep it and the performance is a little laggy which im picking is the Database which they don't reccomend for production. I'm only likely to have 2 vehicles and 5 at max but still.
I've got MSSQL Express 2022 already on the server. I followed some instructions and backed up the old H2 Database. Setup the Config to talk to the SQL and set ports and firewall rules and it populated tables into the MSSQL database on a service start.
I then stopped the instance and have managed to connect Squirrel SQL to the Database copy on C: and the new SQL Database and tried to copy and paste the first table I got the error "cannot insert explicit value for identity column in table when identity_insert is set to off".
I then ran a SET IDENTITY_INSERT tc_actions ON; via an SQL Query in SQL SMS and it seemed to work but still couldn't copy and paste tables getting the same error. At this point I put the backed up DB and Config back and its working with H2 again at present. I just noticed running reports can be quite slow and its a good windows server with excess Ram for the processes its running and an SSD for both drives.
Couldn't really find any explicit instructions from anyone who has done this so just wondering if anyone has a good write up on going H2 to MSSQL.
Next step might be going Mysql but I have MSSQL running for a website I host so would be good to utilise that rather than also installing MYSql that I have far less experience on.
Would be really cool if traccar have a backup and restore of Data process that was database agnostic for migrations from server to server and platform to platform.
Any tips and pointers from anyone who's done this greatly appreciated. I know there is a lot of any Microsoft sentiment and people will tell me to go to MySQL but I know MS a lot more and I've always found it a very reliable platform.