Select Page

Upgrade SQL 2014 Including Availability Group To SQL 2016

As Windows Server 2016 Is coming out shortly I thought I would proceed to upgrade my SQL 2014 Availability Group Cluster to SQL 2016. This is a relatively simple upgrade to complete, however I did encounter an error with SQL Management Studio at the end of the installation which I also created a post about here. I do not specialise in SQL so this may not follow the exact best practices but these steps worked correctly in my environment. It may be advised to create a new SQL 2016 cluster instead of upgrading in a production environment.

You will need to initially login to your current primary instance in your SQL 2014 Availability Cluster. This can be found out using the SQL Availability Group Dashboard. You can find this by expanding the AlwaysOn High Availability section when you are logged in to one of the SQL servers in the cluster.

SQL Management Studio - Upgrade SQL 2014 Including Availability Group To SQL 2016

This is the SQL Availability Group Dashboard. As you can see I’m currently logged in to the Primary and Secondary Node.

SQL Management Studio AAG Dashboard - Upgrade SQL 2014 Including Availability Group To SQL 2016

Before proceeding to complete the upgrade, you will need to Fail Over the databases to make sure the SQL Server you are upgrading is not the primary instance. You will need to right click the Availability Group (JACOB-AAG01 In this case) and select ‘Failover’. You will now need to select the new primary replica as per below –

SQL Management Studio Failover AAG - Upgrade SQL 2014 Including Availability Group To SQL 2016

You will need to make sure you are using the correct account to login to the server instance, when this has been confirmed you will need to select Connect.

SQL Management Studio Failover AAG Connect To Replica - Upgrade SQL 2014 Including Availability Group To SQL 2016

You will be provided with a summary as below –

SQL Management Studio Failover AAG Summary - Upgrade SQL 2014 Including Availability Group To SQL 2016

When you press Finish it will complete the Failover.

SQL Management Studio Failover AAG Complete - Upgrade SQL 2014 Including Availability Group To SQL 2016

We can now proceed to insert the installation media and start the SQL Server Installation Center. The ISO can be found here. You will now need to select the Installation Tab and select Upgrade from a previous version of SQL Server.

SQL Server Installation Center - Upgrade SQL 2014 Including Availability Group To SQL 2016

You will need to insert your product key and accept the license terms, the installer will then proceed to verify the requirements for the installation of SQL 2016. As you can see this server required a reboot before starting the installation. I completed the reboot and proceeded with the installation.

SQL Server Installation Center Global Rules - Upgrade SQL 2014 Including Availability Group To SQL 2016

SQL Server Installation Center Install Setup Files - Upgrade SQL 2014 Including Availability Group To SQL 2016

When the SQL Setup Files have been installed it will verify the upgrade rules. As you can see I already had all of the prerequisites.

SQL Server Installation Center Upgrade Rules - Upgrade SQL 2014 Including Availability Group To SQL 2016

You will now need to select the Instance If you have multiple instances on your SQL Servers.

SQL Server Installation Center Select Instance - Upgrade SQL 2014 Including Availability Group To SQL 2016

The installer will confirm the features that it will be upgrading in the installation.

SQL Server Installation Center Select Features - Upgrade SQL 2014 Including Availability Group To SQL 2016

You can leave this as default or change the Instance ID.

SQL Server Installation Center Instance Configuration - Upgrade SQL 2014 Including Availability Group To SQL 2016

SQL Server Installation Center Server Configuration - Upgrade SQL 2014 Including Availability Group To SQL 2016

SQL Server Installation Center Full Text Upgrade - Upgrade SQL 2014 Including Availability Group To SQL 2016

The installation will now verify the feature rules.

SQL Server Installation Center Feature Rules - Upgrade SQL 2014 Including Availability Group To SQL 2016

You can now upgrade the installation If the feature rules passed successfully.

SQL Server Installation Center Ready To Upgrade - Upgrade SQL 2014 Including Availability Group To SQL 2016

SQL Server Installation Center Upgrading - Upgrade SQL 2014 Including Availability Group To SQL 2016

When completed you will be presented with the following confirmation.

SQL Server Installation Center Complete - Upgrade SQL 2014 Including Availability Group To SQL 2016

It is recommended to also install the latest SQL Server Management Studio. This can be found here.

SQL Management Studio Installation - Upgrade SQL 2014 Including Availability Group To SQL 2016

After this was completed I encountered the following error – “Visual Studio License: Prerelease software this license has expired”. I have detailed how this was resolved here.

You will now need to repeat these steps for the secondary node. When this is completed you can verify this has completed successfully by logging in and navigating back to the SQL Availability Group Dashboard. As you can see in my environment this was successful. You may need to resume data movement on the databases If they have stalled, this can be completed by right clicking the Database in the AlwaysOn High Availability Group’s and Selecting ‘Resume Data Movement’. It may also be worth trying to Failover the Databases to confirm this is also still working correctly. Make sure you also test your applications to make sure they are working correctly! Thanks for reading!

SQL Management Studio AAG Dashboard Success - Upgrade SQL 2014 Including Availability Group To SQL 2016

Pin It on Pinterest

Share This