SQL Server migrations are a necessary and expected part of keeping a modern SQL Server deployment. We have worked with many clients on migration projects, and I have organized some considerations for you and your team before, during, and after your SQL Server migration. We recommend working with a SQL server DBA on these kinds of projects. Especially because this list is not exhaustive, nor does it take everything for your environment into account, a DBA will be best equipped to help keep things on track.
Pre-Migration Considerations
The main set of reasons we encounter for a migration project is that SQL is out of date or it is time for a hardware upgrade or both. Some others may be to change data centers or to move to the cloud. In any case, the things I will ask you to consider will be relevant to any migration project.
First is hardware:
Provisioning is a complex topic, and an in-depth guide is out of the bounds of this post. However, be sure to look into the CPU and network throughput needs of your current production workload on the old server and plan appropriately according to your business needs.
For storage, we recommend getting a different drive for the different workloads in SQL Server. Specifically, having a separate drive for each of the following: the binary files, the data files (.mdf and .ndf), the log files (.ldf), TempDB (.mdf,.ndf, and .ldf), and backups (.bak or .trn), and none of these should live on C:\. This is to help prevent I/O issues across workloads as well as prevent the C:\ drive from ever having a space issue that could take the server down. Be sure to size the new drives in accordance with the usage patterns of the prior server with room for growth. A cloud consideration is to look for a local attached SSD for TempDB to increase performance, also known as ephemeral storage.
We would also recommend provisioning a network storage for local backups and point in time-recoverability with low wait times. This should be paired with a backup to cloud storage for long-term retention and ransomware protection.
Second is Software:
When at all possible, keep the SQL server host clean from other applications and services. Even SQL Server Management studio should not be installed on a high-performance production system!
When installing SQL Server on the new host, be sure only to include the services that will be needed. Refrain from installing everything available, or even things you might use later. Only install things you plan on using. This helps keep the device performant and lean. However, you can run the SQL server discovery report (built into the SQL server installer) to see what SQL Server extensions are currently running on the old server so that the new server can be configured the same.
Third is licensing:
Some migrations occur solely to save money on licensing or to access features only available to the Enterprise edition, such as intelligent query tuning. Be sure to consider the pain points of your current system and investigate if a newer version feature solves it or simplifies its management. Also, consider if a higher edition will provide quality of life improvement or business critical service that may reduce time or cost in other ways.
Fourth is configuration and compatibility
Some organizations use legacy SQL versions because their application requires them. It is worthwhile to consider that there are minimum compatibility levels when upgrading. For example, the minimum compatibility level in SQL server 2019 is 100, which is the 2008 version of the cardinality estimator. Checking with vendors or internal development teams with these new features in a dev environment (SQL server developer edition is free for this purpose!) is a worthwhile investment.
Be sure to consider the current configurations of individual databases on the old server. Some that have tripped up some of our clients have been ‘is_trustrworthy_on’, ‘is_read_committed_snapshot_on’, ‘is_broker_enabled’ among others.
We recommend testing production workloads in a development environment before migrating. Testing app connections and ensuring everything works as it should be a required checkpoint in your project. The last thing you want in the middle of a downtime is to find out there is an unknown compatibility issues on the app side when the database has already been cut over to the new server.
Fifth is downtime:
A well-planned migration can have a cutover of a production database in 5 minutes or less. We typically use log shipping as it is an effective and low-maintenance method to get a production copy of data that is usually less than 1 hour out of sync from production. The process for cutting over for us is the least painful part of a migration. Especially since we typically work through all the above with our clients beforehand.
Considerations During Migration
Since this is typically a short section of our process, I will describe the general flow:
We set up log shipping sometime before the migration, it does not take much as the longest part is typically restoring a copy of the production database to the new server. Once this is set up, we use scripts to configure and start the log shipping process. We make sure the LSbackup, copy, and restore jobs work and then we are set until the day we cut over. We also copy over any relevant jobs or linked servers and ensure other dependencies, such as SSIS packages, are ready at this point in the process.
On cutover day we use scripts to set the database to read-only and cancel all in-flight transactions so the data is in a consistent state. We do one last backup and then disable the job. We use the built-in report to see “Transaction Log Shipping Status” and compare the LSNs of the most recent backup and restore to make sure all the most recent data has been restored to the new server. Then we disable the copy and restore jobs, recover the database on the new server and set it to read/write. Here is where our clients change the connection strings and ensure everything is working as expected. We follow up by changing relevant configurations such as the ones mentioned above and clearing out the log shipping jobs from the old and new servers.
The database is set up and is running on the new server, at this point we would use our final ‘copy-logins’ script to ensure the application and users have the same permissions and login capabilities on the new server. Migration complete!
Post-Migration Considerations
We monitor our client’s servers throughout the year, so we typically look at our maintenance jobs and reports as they come in after the migration to look for any issues, working with the client to resolve any issues that arise. Having alerts set up to catch these issues is important, even if you do have a DBA team!
I hope this helps you on your migration path. Please let us know if you are having trouble or need more information, we would be glad to help you have a successful migration and more!