SQL Server migrating DB from 2008 to 2017
The scenario
We have several databases running on SQL Server 2008 which needs to be decommissioned. The databases are a mixture of production, training and development. We have two destination servers which are SQL Server 2017, one for production and one for development. Each database is quite small around 10-20 GB.
I am using Ola Hallengren’s maintenance scripts on the old SQL Server, the databases are running in full recovery mode and I backup the databases once a day and the transaction logs every five minutes as suggested by Brent Ozar (he has great training).
Testing the test databases
I notified the test users when I was going to move the database. Processing one database at a time I took the databases offline and check the web interface has failed, then restore the database to the dev SQL Server 2017 restoring it to the last transaction backup.
The databases were left at the compatibility level for SQL Server 2008 (100). The other issue is the recovery time as this setting was not available in version 2008 so I checked it and set it to 60 seconds which is the default for 2017.
/* Checking recovery time on the databases... */
SELECT name, target_recovery_time_in_seconds FROM sys.databases;
/* Set the recovery time on the databases... */
ALTER DATABASE <your db name> SET target_recovery_time = 60 seconds;
The connection account was in Microsoft Active Directory (AD) so I was able to add the account and check the access. I configure the web host to point to the new SQL Server and it started working.
The advantage of having accounts in AD is that you do not have to worry about the unique system account id so there is no messing around sorting out the account.
We completed a round of testing the web application which worked fine, the vendor said it should work fine in compatibility level 2017 (140) so we set it and re-ran the tests and it was fine.
Testing the training databases
Training databases are not development databases so we need to run them in a production database. The advantage is, that if it all goes horribly wrong, we can roll back to the previous version of the database (migration is a one-way street).
We will still need to check the compatibility level, I left it at 2008, and I set the recovery time to 60 seconds as the migrated value was 0 seconds.
Going live
We migrated the databases we tested in the development environment as the vendor was very clued up and easy to work with. The database application running in training is business-critical and they seem to be unsure of the technology and testing is not under my control so I am still waiting.
Checking the database
I used Brent’s First Aid Kit I check the health of the databases. All the tables were heaps, there was an index issue but the tables were small and the application runs fine so although I would like to tweak the database to run faster, but the bottleneck seems to be the ColdFusion Server code.
