SQL Stuff

How To Upgrade SQL 2012 to SQL 2014 With AlwaysOn

Here’s a couple things you need to make sure of before you begin the upgrade from SQL 2012 to SQL 2014 within an AlwaysOn configuration. In this example we’ll be using mostly the default configurations. Additionally; we’ll see a single (most commonly found error) if you have Reporting Services installed, but not yet configured. It will usually throw an error message, but the easiest fix is to configure the Reporting Services Databases ( again using the defaults ) for ReportServer, and ReportServerTempDB. We’ll also cover some peculiar synchronization errors that are often encountered whenever upgrading an AlwaysOn configuration to SQL Server 2014.

Note: This is not the ‘How to’ that shows you everything. This simply gives you a reference on some of the things to expect. This is NOT comprehensive. It shows you various issues that might crop up, quick ways to potentially resolve them, but it will not ‘train’ you on the upgrade process.

For starters; ensure that you are performing the upgrade using the ‘rolling upgrade’ process where you are installing SQL Server 2014 onto the passive node (or nodes) first, then failing over the Availability Group resource ( And other resources to the secondary Node), then doing the former primary node last.

Ensure that your AlwaysOn scheme is in Synchonous Mode (where the primary node maintains ownership).

Whenever you’re ready to do the final server; you’ll need to move the availability resource group to the already upgraded secondary server.

Step 1:
Once you download the software you’ll need to mount it. Simply right-click and select ‘Mount’.

Step 2:

Right-click ‘Setup’ and select ‘Run as Administrator’.

Step 3:

Select ‘Installation’, and choose ‘Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012’.

Step 4:

Enter your product Key and click ‘Next’. In this example I’ve removed the Product Key for obvious reasons.

Step 5:

Accept the ‘License Terms’ and click ‘Next’.

Step 6:
Click ‘Next’.

Step 7:

Click ‘Next’ at the ‘Install Setup Files’ screen.

Step 8:

Use the default selection (In case upgrading the current server using a single default instance), and click ‘Next’.

Step 9:

Click ‘Next’. The feature selection will default to all the current features that exist on the Database Server.

They are preselected. ( This is normal for the upgrade process )
They cannot be unselected or changed. Again… This is normal.

Step 10:

Click ‘Next’ at the instance configuration. We are using the defaults in this example tutorial.

Step 11:

Click ‘Next’.

Step 12:

Step 13:

Step 14:

Note the error boxes. If you get these they are easily corrected. Simply add a Reporting Services Database. If you don’t have this issue you won’t need to do the following step of creating one.

Step 15:

Step 16:

Step 17:

Step 18:

Step 19:

Step 20:

User name has been removed of course.

Step 21:

Step 22:

Step 23:

Rerun the upgrade process and it should proceed to the next step where it will begin the installation. Be patient as this is the longest portion of the installation.

Note: While the above progress bar completely fills… It will appear as though it’s maxed out and hung. Just relax; it’s not totally 100% done. Progress bar for this particular upgrade may not be totally accurate. It’s behind a couple minutes in some cases. It’s normal; just wait a few, then you’ll see the following screen.

Next just follow-through with the remaining prompts however; there are a couple things you should keep in mind.

1. Run DBCC across all databases to ensure they are in a good working state.

2. Run a Backup of all databases.

3. (To keep it simple) Remove the Databases from the AlwaysOn configuration. Yes; this means you’ll need to add them back after the upgrade.

4. Failover the Availability Group, and other Cluster resources to the already upgraded Nodes before you do the last server.

Fyi; If you don’t remove the databases from the AlwaysOn configuration, and decide to run the upgrade on the primary node anyway (even after failing over the Cluster, and Availability group) you might get some peculiar errors. You’ll see this whenever you’re looking at the Failover Cluster Manager, or the AlwaysOn High Availability Dashboard after the upgrade.

Just wanted to give you a heads-up on that one.


Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s