Upgrades and Migrations are natural part of a database administrator’s life. If you think about the product cycle of SQL Server, Microsoft reduced it from four years(2000-2005-2008-2012) to two years( 2012-2014-2016-2017 and 2019) . We even have 2016 and 2017 as back to back versions.  That means almost bi-yearly upgrade and migration projects for database administrators.

We all have been there and know how terrible this process can be. Trying to identify all applications connecting to a consolidated server, talking to application specialists and system engineers to change configuration files to point them to new server, legacy applications which doesn’t allow to use new features or no one wants to touch it due to  hidden configs that no one knows where it is.  All those hassles that reminds me bad memories.

7 years ago Starting with SQL Server 2012, AlwaysOn Availability Groups(AG) came along as a High Availability and Disaster Recovery solution. It was an enhanced version of Database Mirroring technology but it allowed people to create groups of databases that can automatically failover together. Moreover, AG Listener gets all client connection requests and directs them to the appropriate availability replica. No need to update DNS records in case of a failover anymore! It has tons of other features as readable secondary, read-only routing lists and so on. Most companies loved this technology and used it as a primary HA solution for SQL Server landscape.

What is going to happen when we have to migrate from SQL Server 2012 to a higher version? Of course, you can create new environment with new SQL Server version, lets say 2016, and migrate applications one by one in a timely fashion. How about when you don’t have that luxury and you have to do it all in one go? Then you start wondering like

“Can I use AlwaysOn technology for this? Can I add new SQL 2016 servers as secondary and failover entire server in a minute?”

Achtung!

Before jump in the pool of joy, There are a few thing to keep in mind with this approach though:

• Microsoft says this is only supported in a rolling upgrade scenario and it shouldn’t be kept for a long time. Upgrading Always On Availability Groups
• Secondary servers with higher versions can not be read. They will have “Synchronized / In Recovery” status which I will show below.
• Once you failover to new servers with higher versions, there is no way back. By design, it is not permitted to replicate data from higher version to lower version. I guess the only reason why this is possible to replicate in reverse is to support this kind of upgrade scenarios.

So Capua, shall I begin?

Setting up Simulation Environment

I am going to perform this test with 4 Virtual Machines to mimic most common 2 node Always On Availability Group setup.

I will not mention all steps in detail such as how to create an availability group via Wizard or T-SQL scripts because that deserves another blog post. Instead, I am going to list the steps:

1. Install SQL Server 2014 on Server A and Server B, then apply latest SP and CU
2. Install SQL Server 2016 on Server C and Server D, then apply latest SP and CU
3. Create WSFC and Add Server A and Server B as Nodes
4. Enable Always On feature on both Server A and B
5. Restart Database Engine Service on both Server A and B
6. Create Always On Availability Group with a Listener
7. Add a database into AG

At this point, we have the same setup with current production environment. Up and running 2 node AG in sync mode. Now it is time to add new Servers into equation and start real testing.

To be able to add them as secondary replicas to existing AG, we need to enable AG feature and that is only possible if those servers are added as Nodes to WSFC. Thus, first add them to cluster.

After enabling AlwaysOn feature and restarting SQL services, I added them as Secondary Replicas to AG without any problem.

Let’s Add Tutorial DB to AG. I usually don’t use wizard for this kind of operations due to tons of valid reasons and I recommend you to do so. If you use the wizard, at least take the script on the last screen and close GUI. Even that is better.

At this point, I noticed that secondary replicas with 2016 versions are synchronized but not readable as stated earlier above. Now, let’s try to fail over to 2016 to finish this simulation.

:Connect NL-SQL16-T1

ALTER AVAILABILITY GROUP [NL-SQLDAGT-AG1] FAILOVER;

GO

As soon as failover completes, 2014 replicas becomes “Not synchronizing” as seen below. As Wolfsheim once said, es gibt kein weg zurück which means there is no way back.

I truly believe that this is a great feature to cover corner cases. On the other hand, I would rather migrate application one by one in a more controlled way rather than moving them all in one go. After all the years I worked as DBA, one thing I learned from the hard way is “Never put all your eggs in one basket”.