Always On SQL Server Upgrade Checklist

Problem

How can you upgrade a set of SQL Always On servers and Windows operating systems by skipping several versions in a single upgrade without doing multiple rolling upgrades? Microsoft has many documents on upgrading SQL and Windows OS, but I could not find a methodology to directly upgrade a set of SQL 2012 Always On servers to SQL 2019 and upgrade the operating system from Windows 2012 R2 to Windows 2016. Check out the steps below and this methodology should work to upgrade from any SQL version that Microsoft says are compatible!

Solution

First you want to look at Microsoft articles Supported Version & Edition Upgrades to find out what versions of SQL you can upgrade from to a new version. In my case it is possible to upgrade a SQL 2012 database directly to a SQL 2019 database. My configuration of 4 servers included a primary, a synchronous secondary, a DR asynchronous secondary, and an asynchronous reporting server. The steps for additional secondary servers are the same, so to keep this article simple I’ll just explain with a primary and a secondary. Also, in this article I’m not going into much depth because most of the major steps are all documented by Microsoft and other sites.

Steps for Upgrading Always On SQL Servers

The method used includes using Log-Shipping to get your databases over to a set of new servers and reducing downtime when converting over to a Always On availability group. Below I have listed the high-level steps to follow to complete this upgrade successfully. Also, I recommend you test this out in a lab first! Doing so will validate and verify the steps and give you some practice. You can include in the testing, upgrading the database compatibility levels to the current SQL version. Though the individual steps may be documented by Microsoft and others, the end-to-end methodology skipping many versions was not found. The key is that you can have a database log-shipped to multiple servers, recover one of the log-shipping databases, add it to a Always On Availability Group on the Primary, do “Join Only” on the second Log-shipped database on the Secondary and it will automatically synchronize. The following Steps are in 3 Sections: Initial Preparation, Cut-Over/Go-Live, and Post Go-Live.

Initial Preparation Steps

Server Builds:

1. Build new servers
2. Set up a windows cluster. Add the two or more servers to the cluster. Follow best practices, create a witness or file share.
3. Install your target version of SQL server on each server
4. Changed Server optimization options
5. Enable Always On features for SQL server.
6. Set up a listener for the Always On cluster.
7. Create a test database. I named mine “AlwaysOnTest”. This database will be used to set up the initial Always On configurations.
8. Create an Always On availability Group on the primary server.
9. Add the “AlwaysOnTest” database to the Always On availability group.
10. Add your secondary server to the availability group.
11. Add the Always On test database to the availability group and synchronize it to the secondary servers. This step initializes the Always On availability group that will be used later to add additional production databases to. You can delete the test database later.

Log Shipping:

12. Next configure Log Shipping on the old server. For my configuration we were doing log backups on the Always On Secondary so the Always On Secondary because the Log-shipping Primary. (In my set up I had a few large databases, so I manually controlled copying and restore the large backups and manually setting up log shipping.)
    1. If you have encrypted databases, be sure to create the master key and a Certificate from a backup on the New SQL Servers for each encrypted DB.
    2. If you created the Log Shipping DB manually then be sure your login is not the DBOwner as this is hard to change on a secondary for Log Shipping and Always On!
    3. If relevant, Database Chaining is another option that is hard to change after the database is in Always On.
13. At this time you should have both the future Always On primary and secondary servers set up as secondary servers for Log Shipping and receiving the transactions of the log shipping databases.
14. Script all your logins. You might use SP sp_help_revlogin to script the passwords.
15. Script SQL jobs and create those on the new server disabled.
16. Set up linked servers on New Servers
17. Compare your configurations from the old servers to the new servers and set those appropriately including memory settings and other database and server configurations.
18. Plan Application configuration changes to point to new servers!
19. Plan User and Application Testing steps
20. Plan the outage for the cut over.

The Cut-Over for Go-Live

21. At this point we should be ready for cut over to the new version SQL Server’s.
22. Finalize all work on the SQL servers.
23. Turn off all applications, services, schedules, that are connecting to and doing work on the Old SQL server.
24. On Old Server: Lockout users. To do this you can run; DENY CONNECT SQL TO [public] (sysadmins will still have connectivity for support)
25. On Old Server: Check Always On Dashboard – to ensure all Old server are in sync!
26. On Old Server: Disable all SQL jobs except for the Log Shipping jobs on all source server.
27. On Log Shipping Primary: run final Log Shipping Transaction Log Backups Jobs
28. On Log Shipping Primary: Disable Log Shipping Jobs & ALL Jobs; run LS Report
29. On New SQL Servers: run LS copy & LS restore jobs & Disable LS Jobs
30. On Each Log-shipping Server: Run Log Shipping Reports from Server Level Reports to validate all are in-sync.
    1. Pay attention that the last transaction log files match the last files restored to the Secondaries. If not re-run the LS Copy and Restore jobs again! This is critical!
31. All should be in sync now!

Always On for New Servers

32. On NEW SQL PRIMARY: recover each Primary database.
33. On New SQL 2019 Secondary Servers: leave the DB in NORECOVERY!
34. On NEW SQL PRIMARY: backup All Databases to be added to the SQL Availability Group (required). This step took the longest as I had large databases to back up! Always On requires a backup before allowing you to add the databases to the Secondaries!
35. While the backups ran, I slept! Timing is everything!
36. On NEW SQL PRIMARY: Ensure the initial DB backups completed
37. On NEW SQL PRIMARY: Add the new Databases to the Availability Group already established with the “AlwaysOnTest” database.
    1. From the Always On wizard, Specify Initial Data Synchronization page, select "JOIN ONLY" to automatically join your new secondary databases to the availability group.
38. Now repeat for other DBs
39. Microsoft Note: If a database is encrypted or even contains a Database Encryption Key (DEK), you cannot use the New Availability Group Wizard to add the database to an availability group. This was unexpected but resolved by adding the encrypted DB via script (thus by-passing a potential bug but in SSMS)
40. Update Database compatibility level to the latest or desired level. Note: some database settings replicate, but some do not! This will replicate to the Secondary.
41. CHECK DBs on Secondary Replicas – Should be in sync and readable depending on your settings.
42. On NEW SQL Server Enable tester Logins
43. May need to Run script to Sync Login SIDs

Data Validation Step – across All Servers

44. CHECK SQL Errorlog
45. CHECK Always On Dashboard: Verify all databases are in a Synchronizing state
46. Update Linked Server Pointing back to new Servers
47. Enable All Logins
48. Point all Application to the New Servers!
49. Begin User and Application Testing
50. Declare SUCCESS!!!!!
51. Enabled all SQL Jobs except the Log Shipping jobs

Post Go-Live

Clean-Up Shut Down Old Servers

52. On OLD Servers: Disable Log Shipping for all DBs on Old Log Shipping Primary
53. On OLD Servers: Stop SQL Service; Set date for decommission!
54. Note: I shut down the LS Primary SQL Servers, I had to remove LS manually from the Secondary (SQL 2019) servers.

Wrap Up

We built the new servers many months before cut-over. We had Log-Shipping running for several months as we waited for a go-live window. We did set up a set of Lab servers to prove out the process prior to doing it on the production systems. The transition from databases that are Log Shipping secondaries to a recovered new Primary Always On databases and Always On synchronized secondaries went surprising smooth! The longest part of the cut over was the database full backup require on the Always On primary prior to adding the secondaries to the Availability group. Because of our large databases we ran the backups overnight and finished the Always On set up in the morning, followed by application testing. The cut over was relatively fast, smooth and most of all successful!

So, if you are tasked with moving off an old version of SQL and want to skip many individual upgrades, or rolling upgrades, try this method in a lab to ensure you are comfortable with the steps and then use this method to do a major upgrade to your SQL Always On environment!

Next Steps

Review these other MSSQLTips articles:

• Choosing a SQL Server Upgrade Method – Part 1
• Quick Guide to Upgrading to SQL Server 2014
• SQL Server Upgrades and Migrations Tips
• Edition Upgrade and Downgrade in SQL Server