Upgrading Clusters to SQL Server 2012 When AlwaysOn Availability Groups Will Be Used

Upgrading a clustered SQL Server 2008 R2 instance to SQL Server 2012 is just as easy as it was upgrading from SQL Server 2005 to SQL Server 2008. However, when upgrading a SQL Server 2008 R2 (or earlier) cluster to SQL Server 2012, there's an additional decision that needs to be made ahead of time if you'll be using SQL Server 2012's new AlwaysOn Availability Groups. If you're going to use these groups, you need to decide whether the instances that'll become replicas within the AlwaysOn Availability Group will be clustered instances. By clustering the instances that will become replicas, you'll have the ability to survive additional physical server failures without losing access to a database replica. As a result, you'll have an additional level of availability within the AlwaysOn Availability Group deployment.

Using nonclustered instances for the replicas in an AlwaysOn Availability Group deployment gives you a much simpler configuration to maintain. However, if servers are taken offline due to system failure or system maintenance, there are fewer database replicas available. As a result, it'll take more time for the replica hosted on the server that was offline to become back in sync with the production database.

How to Upgrade to a Clustered Instance

Upgrading a currently clustered SQL Server 2008 R2 instance to a SQL Server 2012 clustered instance is straightforward. The upgrade process is very similar to the one used to upgrade a SQL Server 2005 clustered instance to SQL Server 2008, as described in "Upgrading a SQL Server 2005 Cluster to SQL Server 2008" (February 2010). You just need to use the SQL Server 2012 installation media instead of the SQL Server 2008 installation media.

Besides being straightforward, the upgrade can occur with minimal downtime to the database application. The downtime is minimized because the database instance will remain configured as a clustered instance after the installation and the AlwaysOn Availability Group will be using the clustered SQL Server instance as the replica.

After you've tested and released the cluster to production, you can create the AlwaysOn Availability Groups. If you're unfamiliar with how to create them, see "Microsoft SQL Server 2012: AlwaysOn Availability Groups Feature" (January 2012).

How to Upgrade to a Nonclustered Instance

Upgrading an installed clustered instance to a nonclustered instance is a complex process, because there's no native upgrade process for this scenario. The best method is to add more nodes to the Windows cluster, then install the standalone instances on these new nodes. This can be done with or without the installation of any additional hardware to the existing Windows cluster.

Installing without new hardware. Management might not approve the purchase of new hardware when upgrading installed clustered instances to nonclustered instances. By installing nonclustered instances on the existing SQL Server 2008 R2 cluster, the need to purchase new nodes for SQL Server is removed or at least delayed until after the initial SQL Server 2012 instances are up and running.

Note that although this upgrade approach can save money, it can be risky. Installing SQL Server 2012 on the existing SQL Server 2008 R2 cluster nodes might cause problems with the existing SQL Server clustered installation. In addition, reconfiguring the SAN storage might cause problems.

To install SQL Server 2012 on the existing SQL Server 2008 R2 nodes of a Windows cluster, follow these steps:

1. Reconfigure the storage array as required by the storage configuration so that storage is provided to the independent nodes while still providing the clustered storage to the existing nodes.

2. Provide independent storage to each cluster node that will have SQL Server 2012 installed on it. Make sure that you use the same drive letters and paths on each node.

3. Install SQL Server 2012 on the appropriate nodes.

Installing with new hardware. Installing new hardware removes the risk of the SQL Server 2012 installation causing problems with the existing SQL Server clustered installation. In addition, you avoid any potential problems associated with reconfiguring the storage for the existing Windows cluster nodes.

Here are the steps for getting the SQL Server 2012 nodes up and running using this method:

1. Install Windows 2008 R2 Enterprise Edition on the new nodes, using the same patch level as the existing Windows cluster.

2. Configure the storage array so that independent storage is available to each new node. Make sure that the drive letters and network paths are configured identically on each new node.

3. Add the new nodes to the existing Windows cluster.

4. Install SQL Server 2012 on the new nodes as standalone instances.

No matter whether you installed the SQL Server 2012 nodes on new or existing hardware, the next step is to test the nodes. Make sure that they're working correctly and that they have the needed performance from the connected storage.

If the nodes are working correctly, you can migrate the databases. Moving the databases will require some downtime, because a more traditional database migration must be completed at this point. The most traditional upgrade path would be to use SQL Server log shipping to get the databases to the SQL Server 2012 instance. This will minimize the downtime when switching the database from the SQL Server 2008 R2 clustered instance to the SQL Server 2012 nonclustered instance.

After the databases have been restored to the new SQL Server 2012 instance and brought into production, you can create the AlwaysOn Availability Groups.

At this point, the SQL Server 2008 R2 instance can be removed by going through the traditional SQL Server 2008 R2 uninstallation process on both nodes of the cluster. Care must be taken to uninstall only the SQL Server 2008 R2 components and not the SQL Server 2012 components.

Clusters Based on Windows Server 2008

SQL Server 2012 will install on servers running Windows Server 2008. However, these clusters must be upgraded to at least Windows Server 2008 SP2 and have the update discussed in the Microsoft article "An update is available for the.NET Framework 3.5 Service Pack 1 in Windows Vista and in Windows Server 2008." This update is needed to install the Visual Studio components that are used by SQL Server 2012.

It's highly recommended that a Windows Server 2008 cluster be upgraded to Windows Server 2008 R2 before SQL Server 2012 is installed on the cluster because SQL Server 2008 R2's Failover Clustering component has had changes made to it that are specifically designed for AlwaysOn Availability Groups and the availability group listeners. This upgrade requires that the cluster be replaced with a new Windows cluster because there's no supported method of upgrading machines that exist within a cluster. If the same physical servers are to be used to build the new Windows cluster, they can be removed from the cluster one node at a time, formatted, installed into the new cluster, and prepared for SQL Server 2012 installation.

Clusters Based on Windows Server 2003 or Windows Server 2003 R2

SQL Server clusters that are based on Windows Server 2003 or Windows Server 2003 R2 must be upgraded to a newer version of Windows before they can be upgraded to SQL Server 2012. Unfortunately, this isn't as easy as it would appear because clustered installations of Windows don't support having nodes within the same cluster running different versions of Windows. This effectively means that you have to build a brand new Windows Server 2008 R2 cluster using a different cluster network name and cluster IP address before you can consider installing SQL Server 2012.

How Complex Is It Really?

The complexity of upgrading a SQL Server 2008 R2 cluster to SQL Server 2012 when you plan to use AlwaysOn Availability Groups will vary depending on the complexity of the final deployment. Some installations will be simple, such as a single Windows cluster with a couple of clustered instances and database replicas being kept in sync on both instances. Other installations will be much more complex to configure, such as multi-site clusters in which all the instances will be clustered and there will be multiple subnets involved.

Although many AlwaysOn installations can be easily done by in-house personnel, larger and more complex installations might benefit from having someone who has previously performed other large AlwaysOn deployments. In complex installations, there are many storage, network, drive-layout, and other issues that need to be addressed before the databases are deployed to the SQL Server 2012 servers. What's a minor issue before the databases are deployed can be difficult, if not impossible, to solve after the databases are deployed without incurring major outages.