(This post written by Jon Biggs (t), one of our Senior DBAs)

We are currently performing migrations with upgrade of multiple-instance SQL Servers to new servers. The migrations are going smoothly (knock on wood), and I wanted to relay some information about the migration process. There are four phases you need to perform when migrating a SQL instance to a new server. These phases are Review, Prepare, Test, and Migrate. It sounds simple enough but let’s take a deeper look into what goes into each phase.

In this case, the Test server and instances are migrated, but the matching Production server and instances have not been. A followup blog is expected with any lessons learned.

Review

During the review phase we check the existing environment for best practices, the SQL databases for upgrade readiness and the target SQL server for appropriate sizing.

When it comes to best practices there are Microsoft SQL Server best practices, vendor best practices and industry best practices that need to be considered. Here are two new best practices that I dealt with during the migrations:

• AWS EC2 best practice is to utilize Lock Pages in Memory (LPM). While LPM was not a new best practice to me, it was surprising to learn that AWS recommends this configuration when running SQL Server on EC2 VMs.
• Microsoft SQL Server best practice when using FILESTREAM is to disable “last access time” and to disable “8 dot 3 naming” on the volumes where FILESTREAM data is stored. Disabling 8 dot 3 naming led me to the options of disabling this setting globally or per volume. It turns out that per volume is the default configuration for Windows Server.

All best practices need to be reviewed and discussed on whether they will be implemented or not. Check the current environment and determine why a best practice is not followed and if it should be followed.

If you are upgrading SQL Server to a new version during the migration, Microsoft provides a tool named the Data Migration Assistant to help determine database upgrade readiness. This tool will assess databases for compatibility issues by SQL Compatibility Level. You may find that you have issues that exist in your databases at your current compatibility level.

When migrating to a new SQL server this is a great time to determine if you need to modify the resources on the server. Do you need more or faster CPUs? Do you need more memory? Do you need larger storage volumes? This needs to be discussed and decided during the review step. Microsoft provides an advanced tool named the Database Experimentation Assistant which can be used to determine how your workload will perform on the target SQL Server. It will also provide query compatibility issues and degraded queries and query plans.

Prepare

During the prepare phase we are determining the migration method, making changes to service accounts and permissions, deciding on shared feature migration such as SQL Server Integration Services and SQL Server Reporting Services, configuring the target SQL Servers, and using Query Store for query regression review.

There are multiple methods of migrating the databases to a new SQL Server instance. These include:

• Backup/Restore. You can simply backup the databases, copy the backup files to the new server and restore the databases. This can be a time-consuming process which you may not have the downtime for. A few things to help speed this up:
  • Backup the databases directly to the new server, eliminating the copy step. This requires the network share to allow the source SQL Server service account write permissions. This is the method I am using for the migrations as we have the time to do this, and we are using SQL Server Standard.
  • If you have a SAN, move the volume with the backup files to the target SQL Server, also eliminating the copy step.
  • You can perform a full backup of the databases ahead of time and restore them with NO RECOVERY. When the migration window starts you can perform a differential backup of the databases and restore them with RECOVERY. A caveat here is that you must perform Copy-Only backups if a full backup is required before the migration starts, otherwise you will break the backup chain.

There are a couple of tools to help make the backup/restore method easier.

• DBATools.io is a set of Powershell commands for migrating databases, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, etc. A caveat with using dbatools to migrate databases with the Start-DbaMigration command is that it does not move Full Text Index Catalog(s) to a new location if you are migrating to different paths on the target SQL Server. If the source path does not exist on the target SQL Server, the restore of the database will fail.
  • If this is the case, you can manually backup/restore the database using with MOVE commands and it will be successful.
  • There are other dbatools commands for migration that can be used to avoid this scenario as well.
• Microsoft Database Migration Assistant can be used to migrate databases using the backup/restore method.

• Detach/Copy/Attach. You can simply detach the databases from the source SQL server instance, copy the database files to the target SQL Server and attach them to the target SQL Server instance. This is faster than backup/restore but does require time to copy the files over the network. There is also the risk of data corruption occurring during the copy process.
  • If you have a SAN, move the volume(s) with the database files to the target SQL Server.
  • If you have a SAN, replicate or snapshot the volume(s) with the database files to new volumes. Shut down the source SQL Server services so the databases are in a clean, shutdown state prior to final replication or snapshot.
• Log Shipping. With log shipping you can have the databases restored on the target SQL Server ahead of migration with NO RECOVERY and continuously restore the Transaction Log backups to the target SQL Server to keep the databases synchronized. When it is time to migrate, the final Transaction Log restore is done with RECOVERY and the databases are brought online. This is also an older Disaster Recovery method for SQL Server and the databases can be brought online without a restore of the last set of Transaction Log backups.
• Always On Availability Groups. If you are using the Enterprise version of SQL Server and upgrading from 2012 or later, you can utilize Always-On Availability Groups to migrate and upgrade. Configure a target AG Secondary replica that is a higher version of SQL Server than your source SQL Server and failover to the target AG Secondary replica to finalize your migration with upgrade.

During install and configuration of SQL Server you can choose Windows accounts to use for the various SQL Services. Some of the common services that need Windows accounts are:

• SQL Server database engine service
• SQL Server Agent service
• SQL Full-text Filter Daemon Launcher service
• SSIS service
• SSAS service
• SSRS service

For these services it is an industry best practice that you utilize domain accounts. When setting the services to utilize domain accounts this can be done during installation or using the SQL Server Configuration Manager utilities after installation. It is highly recommended that accounts are set using either method above as the minimum required permissions should be set automatically for the domain accounts. If you set service accounts manually through the services control panel, you will have to manually set permissions for the accounts. It is also an industry best practice that you allow SQL Server to set permissions for domain accounts and do not elevate the permissions for those accounts without an acceptable reason.

Prior to migration you need to determine which shared features will be migrated to the target SQL server. Not all SQL shared features have to run on the same server as the SQL Server database engine service. These include SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS). Plan out the migration of these shared features as they will be done separately from the database migrations. Starting with SQL Server version 2017, SQL Server Reporting Services is a stand-alone installation that is no longer part of the SQL Server installation. This means:

• You can only have one SSRS installation per Windows Server now. You can no longer have an installation of SSRS per SQL Server instance.
• If you are migrating multiple SQL instances to a new server and you have multiple installations of SSRS on your source server(s) you will have to plan out how to combine them into one installation or choose to migrate them to separate Windows Servers.
• You can use the rs.exe utility to migrate reports from one SSRS install to another SSRS install. This can be done at various folder levels. In our migration we migrated one SSRS install over to the target server as-is and then migrated a second SSRS install into a sub-folder on the target server.

When planning out the target SQL Server configuration is the time to make changes to the SQL Server environment. Aside from the best practice configurations that have been reviewed and agreed upon, now is the time to clean up the database file structure if necessary. According to industry best practices you should separate your database files as follows:

• System database files
• TempDB database files
• User Database files (.mdf, .ndf)
• User Database log files (.ldf)

Each type of file above has different requirements on the storage sub-system. Depending on your environment you can have separate I/O queues for each volume or you can have physical drives that meet different read and write patterns. Plan this out appropriately when configuring the target SQL Server(s). Logically separating the files can make future infrastructure changes easier.

An option you can use during migration is the Query Store. You can enable Query Store in the databases once they are running on the target SQL Server (SQL 2016 and higher) and leave the database compatibility level to the same level it was on the source SQL Server. After a few weeks of running and collecting data in Query Store you can update the compatibility level of the databases to the new level of the target SQL Server. Query Store provides reports on “Regressed Queries” and “Query Wait Statistics” that can be used to determine which queries are performing worse on the newer version. Query Plans can be forced to quickly revert queries to their previous plan prior to the compatibility level change and can then be evaluated for tuning.

Test

During the test phase we are testing our migration plan. There are always unforeseen issues during a migration regardless of how well you plan. Here are things testing can help with:

• Migration method timing can be gathered during testing. You might have looked at your backup times and doubled it for migration but restore could take longer than anticipated. Getting an accurate length of downtime for the migration will help meet expectations.
• Tool or script usage can be tested. Did you have any unexpected failures during the migration?
  • For me I had issues with dbatools and full text index catalogs that I had to work through.
• SQL feature migration can be tested. Was it straightforward migrating SSIS or SSRS?
  • For me I had issues restoring the SSIS database master key that I had to work through.
  • I also had issues with getting SSRS Subscriptions working again on the target server due to a stored procedure referencing the SQL server by name.

Aside from just testing the migration plan and working through the timing and unforeseen issues you should plan for end user acceptance testing. Here are things end user acceptance testing can help with:

• Once the SQL server has been migrated, do the applications still work correctly?
• Did you have trouble changing DNS or connection strings?
• Did you forget about a server that was part of the application that needed to be changed?
• Were any settings on the SQL Server that affect the application missed?
  • For me I missed setting trace flags in SQL on the target SQL server.
  • I also missed static ports that were set on the source SQL server for the named instances.
  • These are things I know should be done but when juggling moving parts, they can get dropped and the testing phase is the appropriate time to catch them.
• Is all functionality of the application still working when using the target SQL server?
• Is your testing plan comprehensive of all the components affected by the migration?

If you have a test environment for your application, then test phase can also be used to migrate the test environment permanently to the target SQL Server(s). If you do not have a test environment, then this is a good time to consider implementing one. If you are unable to implement a test environment, then use the production target SQL Server for your test migration and consider it a dress-rehearsal for the final production migration.

Migrate

Once you have reviewed, prepared, and tested your migration then it is time to migrate. You should have a solid idea of how long you will need for downtime and how long it will take to test the new environment. Plan for rollback to the source environment in the event of unforeseen issues:

• If issues come up during migration or testing, then determine the window of time allowed to resolve those issues before rolling back to the source environment. If you cannot be down for more than X hours and the testing has not been completed by that time you need a plan to get the source environment back online to maintain business continuity.
• When planning for rollback, define steps that are go/no go for rollback. If you reach a point where it is more beneficial to resolve issues than roll back or where live data is being inserted into the target databases, then you can define a point where rollback is no longer an option.
  • You can mandate that all parties involved in the migration give consent to proceed past a point where rollback is no longer an option.

Define and agree on success criteria for the migration. Success criteria will help define when a migration is considered successful. If this is defined ahead of the migration and is acceptable to all parties involved, when the criteria are all met you can send out communication calling the migration a success. This will help with future communication claiming the migration was a failure or the project stretching out for weeks and having to discuss whether an issue is a “migration” issue or a normal operating issue. Examples of success criteria can be:

• End user acceptance testing is completed with all tests passed.
• Applications Y and Z come online.
• If application X is not online, it will not affect the success of the migration.
• SSIS packages run successfully.
• Reports and schedules run successfully.
• 48 hours of production run time will determine a successful migration regardless of new issues.
• Issues opened during the first 24 hours of production run time need to be resolved to determine a successful migration.

A SQL Server migration is fundamentally the same as any system migration. You may have some considerations which are specific to SQL server but overall, the phases of the migration will be the same. If you properly follow the four phases of Review, Prepare, Test, and Migrate then you will not just have a successful migration of your SQL Server, you will have a successful migration of your SQL Server in the eyes of your client or manager.