It is a reality for a lot of customers – they still have SQL Server 2005 in their environments. It is only now that they are planning for upgrading their databases to the latest version of SQL Server. In a previous blog post, I talked about how to integrate Microsoft Product Lifecycle and Support Policies into IT Operations which concluded with planning upgrades and migration based on the version of SQL Server that you are running.

As I mentioned in a previous blog post, one of the main considerations in upgrading to the latest version of SQL Server is high availability. If the database is considered mission-critical, careful planning should be undertaken to minimize downtime during the upgrade process. There are two main strategies for upgrading: in-place or side-by-side. An in-place upgrade is where you directly replace an older version of SQL Server with a newer version on the same machine and the same instance. A side-by-side upgrade (which I also consider as a migration in some aspects) is where you create a new instance of the target version of SQL Server and copy all of the databases, objects, logins, etc. from the source to the new target. I’m a big fan of side-by-side upgrades because I can perform all of the tasks necessary prior to the upgrade without affecting my existing production workload. This means I can do application testing, performance and workload baseline, and potentially automate the whole process of upgrading/migration to reduce downtime as much as I can.

There are two options that I mainly use when doing side-by-side upgrades for SQL Server databases – log shipping and database mirroring.  If I’m running SQL Server 2005 and higher, I’m more inclined to use database mirroring due to the nature of the synchronization process between the source and the target. Database mirroring gives me the option to reduce the potential downtime as much as I can. In both cases, I can do a what is known as a rolling upgrade – a process by which you upgrade the standby (be it a secondary database in a log shipping configuration or the mirror database in a database mirroring configuration) and migrate/upgrade the databases by failing them over to the upgraded version. What I really like about doing so is it gives me the opportunity to upgrade not just the SQL Server database but the underlying server operating system as well, especially for standalone SQL Server instances. This is a great time to move away from those old 32-bit systems towards 64-bit versions. For example, if I have a SQL Server 2005 instance running on Windows Server 2003, you can create a standby standalone instance running SQL Server 2017 64-bit on top of a Windows Server 2016 64-bit machine. You can then configure either database mirroring or log shipping between the two SQL Server instances, using the older version as the primary and the newer version as the standby.

One piece of advice. If you intend to use database mirroring to perform a side-by-side upgrade, be very careful to break the replication between the two if you will be performing testing prior to the production cutover. This is because the upgrade is a one-way street – once you failover to the newer version using database mirroring, the database version will be upgraded in the process. Once that’s done you can no longer go back to the older version apart from extracting the data and schema from the upgraded version and recreating it back on the old version.

Let’s use this configuration to upgrade a SQL Server 2005 database running on Windows Server 2003 32-bit to SQL Server 2017 running on Windows Server 2016 64-bit. Be sure that you’ve installed SQL Server 2005 Service Pack 4 prior to performing the upgrade/migration. To simplify the upgrade/migration process, use the same SQL Server instance names between the source and target.

1. 1. Configure database mirroring between the two instances, using SQL Server 2005 as the principal and the SQL Server 2017 instance as the mirror. I won’t go into the details of configuring database mirroring between two instances of SQL Server in high safety/synchronous mode without a witness. You can refer to this MSDN documentation for more details. Use the SQL Server Management Studio on the SQL Server 2017 instance to perform this task or you will encounter the “index out of bounds” error message in SQL Server Management Studio similar to what’s described in this Microsoft KB article. Just make sure that you are using the SQL Server 2005 instance for the principal database and the SQL Server 2017 for the mirror database. You won’t be able to do it the other way around.

    

   As I mentioned earlier, you can take this opportunity to perform a test failover, application functionality testing, etc. prior to production cutover. Just make sure you break the replication between the two before you do so. Of course, you would have to repeat this step every time you do.Remember to copy all of the instance-wide objects – SQL Server logins, jobs, SSIS packages, etc. – from the SQL Server 2005 instance to the SQL Server 2017 instance as part of the upgrade/migration process.

 

2. 2. If possible, stop all transactions on the database. This is where your clock starts ticking and your recovery time objective (RTO) starts counting. This is to make sure that there are no additional user transactions generated before I perform the failover to the SQL Server 2017 instance. This includes disabling all applications, SQL Server Agent jobs, replication jobs, batch processes, SSIS packages, etc. that will perform any data modification on the databases. You won’t be able to stop every transaction unless you have a comprehensive list of everything that connects and writes to the database. But at least we’re minimizing the amount of log records generated to make the failover as fast as we possibly can.  Be very sure that your database mirroring session is in SYNCHRONIZED state before proceeding to the next step.

 

3. 3. Failover the database mirroring configuration from the older version of SQL Server to the newer version. Once you are ready to cutover to the new environment, failover the database mirroring configuration to the SQL Server 2017 instance. Once you perform this task, there is no turning back. Your databases will be upgraded to SQL Server 2017. Note that compatibility mode will not be upgraded, only the database version.

    

   USE [master]
GO
ALTER DATABASE [Northwind] SET PARTNER FAILOVER
GO

    

   At this point, the database mirroring session will be in SUSPENDED mode since replication of the log records is not allowed from a higher version to a lower version of SQL Server. 

    

   You will also see the database upgrade messages in the SQL Server error log.Converting database ‘Northwind’ from version 611 to the current version 782.

 

4. 4. Switch the new principal database/s back to read-write. After the failover, you can switch the databases to read-write. You do this before you even allow your applications to connect to the database.

    

   USE [master]
GO
ALTER DATABASE [Northwind] SET READ_WRITE
GO


 

5. 5. Remove database mirroring configuration. Whether you would like to keep your existing database mirroring configuration or not, removing the database mirroring configuration helps avoid log record build up on the new principal database.

    

   USE [master]
GO
ALTER DATABASE [Northwind] SET PARTNER OFF
GO


    

   This is because SQL Server will not be able to mark the virtual log files (VLFs) as inactive on the databases until the old SQL Server instance is upgraded to the same version as the new principal database. And when SQL Server cannot mark VLFs as inactive, it won’t be able to truncate the log which would result in unnecessary log file growth. Depending on your upgrade/migration plan, you can provision a new machine with the same operating system and SQL Server version as the new principal and configure Availability Group for high availability. Alternatively, you can use a different version of the operating system but running the same version of SQL Server as the new principal and stick with database mirroring. The choice is totally up to you.

 

6. 6. Modify DNS entries and network settings. Once you are ready to allow applications to connect to the database, you can now modify the network settings and the DNS entries. Enlist the help of your network administrators for this task. You can change the IP address of the old SQL Server instance or disable the network adapter. This will prevent applications and users from connecting to this machine. On the DNS server, change the IP address of the DNS entry associated with the old instance to point to the new SQL Server 2017 instance. This minimizes the efforts in changing connection strings to redirect client applications to the new SQL Server 2017 instance. You may also need to flush the DNS client cache on the client applications to make sure that they can connect to the new SQL Server 2017 instance using the old SQL Server 2005 instance’s machine name.

 

7. 7. Run post-upgrade tasks. Your RTO clock stops ticking when your applications can connect to your upgraded databases without issues – whether be it immediately after switching the databases to read-write or after changing the DNS entries to point to the new SQL Server instance. But that doesn’t mean that our job as a SQL Server DBA is over. We still need to run some of the post-upgrade tasks to make sure that everything is working as expected. Here’s a list of some of the things that you need to do on your newly upgraded databases.
    
   • – Run DBCC CHECKDB with the DATA_PURITY option. When I first found out about the DATA_PURITY bug on the master and model databases from Paul Randal’s blog post, I started checking all of the SQL Server databases that I encounter just to be very sure. I still do so for databases after the upgrade.

    

   • – Update all of the statistics. If you’ve performed testing prior to the production cutover,  you may encounter query performance issues on your databases as a side-effect of outdated statistics. Make sure you update all of the statistics on the databases immediately after the upgrade. The only time that I don’t recommend doing this is if you are dealing with SharePoint databases

    

   • Run – DBCC UPDATEUSAGE. Run this command on all upgraded databases to update usage counters and make sure that correct values exist for table and index row counts. This is very helpful on very busy databases especially when you are doing tons of data modification. As I mentioned in step #2, you can disable all of the batch processes or SQL Server Agent jobs and switch the database to read-only prior to failing over to minimize data modification during the failover.

    

   • – Take database backups. Once you’ve confirmed that all of your databases are fine, take backups immediately. Plus, you won’t be able to configure Availability Groups if you don’t have at least one full database backup.

    

This is just one of many possible options to upgrade your SQL Server 2005 databases to SQL Server 2017 (or higher). Depending on your configuration, it can be as simple as this or as complex as a SQL Server 2005 failover clustered instance with replication and database mirroring. Use this guide as a starting point and modify it to suit your needs. Better yet, check out the comprehensive SQL Server 2014 Upgrade Technical Guide (the document is still useful for later versions of SQL Server). It covers upgrading highly available SQL Server failover clustered instances in more depth, including considerations for upgrading from as old as SQL Server 2005.

Let me know how you’re planning for your SQL Server upgrades and migration. Maybe I can help out.