Notes on SQL

Overview

As part of a series of development actions, we need to replace our SQL Server 2012 installations with SQL Server 2017. This is a four-node Availability Group (AG) for a system that is required 24/7 – so downtime is best avoided.

Background

For reasons that will become apparent in later articles, we require SQL Server 2017. The current configuration is SQL Server 2012 on a 4-node AG, with a 7Tb database at its core. Adding nodes with SQL Server 2017 would be preferable, switching over to the 2017 nodes and removing the old 2017 nodes afterwards. However we don’t have the infrastructure to accommodate that, so in-place upgrades are the only option.

Test Environment

For the demonstration purposes, I am using a 3-node AG constructed in a Hyper-V environment, as detailed in this article.

Upgrade Sequence

As with any SQL Server database upgrade, you can go up the versions, but you can’t go down. So, just like you can’t downgrade a 2017 database to 2012, you also can’t send data from a 2017 database to 2012. Once the primary node is upgraded to 2017, the nodes that are lower versions will no longer replicate. Therefore, it is important (in a 24/7 system) that the primary node stays at the lower version for as long as possible.

In this demostration, we have a primary node, a sync secondary node and an async secondary node. We will upgrade the async node first, then the sync node. After that, we will failover to the sync secondary and upgrade the final node.

Figure 1: Initial setup (AG Dashboard)

Upgrade Process

The First Node

The first server to upgrade, is ‘AG-NODE3’. This is the async node and this means that if there is an automatic failover during this process, the failover process itself will still work. The likelihood of such an event is very low, but that doesn’t mean it won’t happen. With a 24/7 system, the chances of making the system unavailable need to be kept to a minimum.

This upgrade to SQL Server 2017 will remove any installation of SQL Server Reporting Services (SSRS), although not the databases associated with it. SSRS is now a seperate installation. If you are using SSRS it is essential that you backup the encryption key before upgrading. See this article for the fun to be had with upgrading SSRS. The upgrade of SQL Server cannot progress unless the checkbox ‘Uninstall Reporting Services’ is checked. Microsoft really wanted to make sure you know this happening.

Figure 2: The End of Included SSRS

When you start the SQL Server installation and select the upgrade option, it does not give you the choice of which features to upgrade – it is all or nothing. And there is another reminder that SSRS will be removed.

Figure 3: All or Nothing

Having worked your way through the various tabs, the summary screen will also inform you that SSRS will be removed. You can’t claim that you weren’t warned.

Figure 4: Last Warning For SSRS

Once the upgrade of AG-Node3 has completed, checking the AG Dashboard will show that replication is still running without issues.

The Second Node

Having upgraded all async nodes, attention is now moved to the sync secondary.

AG-Node2 will now be upgraded in the same manner as AG-Node3 – it is no different.

Failover and the (Apparent) Issues

Having completed the upgrade to AG-Node2, the only node still at version 2012 is ‘AG-Node1’ – the primary node. First of all, we need to failover from ‘AG-Node1’ to ‘AG-Node2’, so users can still access the database during this process.

After the failover, ‘AG-Node1’ will be shown as ‘Not synchronizing’. In addition, there will be errors shown the SQL Server log file, indicating an issue with versions of the database.

Figure 5: The last remaining 2012 cannot synchronize

Figure 6: Cannot Replicate to Lower Versions

The message:

“The database ‘AGTest’ cannot be opened because it is version 869. This server support 706 and earlier. A downgrade is not supported”

certainly looks like trouble, initially. However, this is just telling you that it can’t replicate a lower version than the version that the primary node is now using. Until this node is upgraded too, the database will be set to ‘Not synchronizing’.

It is worth noting that you need to ensure there is enough space on the primary node, for possible log-file expansion. Transactions will be stored in the primary database log file until the replication has caught up.

The Third Node

The final node can now be upgraded in the same manner as the previous two.

Once completed, data movement needs to be resumed for that final node, whereupon the AG dashboard will show that all three nodes are working as expected.

Figure 7: AG Dashboard After Successful Upgrade

Summary

Upgrading an AG is a reasonably simple process, as long as some care and thought goes into it. When performing this on the live system I did have a few moments of panic, when I saw the message that I’ve detailed in Figure 6. An initial search for that message showed recommendations as severe as rebuilding the AG. It took quite a bit of digging to find the true reason. I don’t need frights like that at my age.

References

Create an AG and Cluster with Hyper-V

The Fun to be had with an SSRS upgrade

SSRS Backup Key Encryption Error