Win Server 2008 R2 upgrade to Windows 2012 how to migrate AlwaysOn AG

Windows cluster requires that all versions of Windows in the same cluster be the same, which is a problem when we want to upgrade Windows (for example, from Windows 2008 R2 to Windows 2012) Had to build a new set of Windows cluster. You can choose to build with new hardware, or evict the nodes in an existing Windows cluster, reload/upgrade the system and add them to the new Windows cluster. Specific cluster upgrade plan I'm not here to discuss it. Immediately enter the topic:

One requirement of SQL Server AlwaysOn availability Group (later referred to as AG) is that all replica are required to be subordinate to the same Windows cluster.

So when we upgrade Windows cluster, we can't build ag between the new Windows cluster and the existing Windows cluster. AG will not be able to provide services for a period of time during the migration process.

From the database point of view, we need to do the following things

Next stop the application and remove the listener from the cluster1 to make sure there is no outside access to use SQL SERVER.

Backup Database

Backup Tail Log

Copy a backup file to a new server

Restore to individual servers

and then re-establish AG

Create Listener

Restart Application

We need to back up the database and restore it to the new primary replica and secondary replica. The corresponding downtime time is the time 1+2+3+4+5+6+7+8 want. Maybe you thought of it. Create a mirroring between the old and new cluster, but unfortunately, the database that created the AG is no longer allowed to create mirroring.

How should it be migrated? Starting with SQL Server SP1, you can create an AG between two different sets of Windows cluster. Let's use an example to illustrate

There is a three-node Windows cluster, Windows version is Windows 2008 R2

Domain:liweiyin3.lab

Cluster Name:cluster1

Server002

Server003

Server004

Listener Name:listener1

A standalone instance of SQL Server SP1 is installed on three nodes. Are the default instances.

Between the established AG. Topology diagram is as follows:

Now create a set of two node Windows 2012 Windows cluster

Domain:liweiyin3.lab

Cluster Name:cluster2

Server005

Server006

Backing up the AG database on Cluster1, including full database backup and log backup two cluster in the middle of the creation AG:

Restores the first step of the file to the Cluster2 node and specifies the WITH NORECOVERY.

Next, execute the following statement on the three databases in Cluster2

ALTER SERVER CONFIGURATION SET HADR CLUSTER context= ' Cluster1.liweiyin3.lab '

After this statement is executed, the cluster context of the database is switched to Cluster1. This result can be checked from the DMV below

Select Cluster_Name from Sys.dm_hadr_cluster

The AG can then be established between Cluster1 and Cluster2. We can use the UI or T-SQL statements.

It should be noted that the synchronization mode of at least one SQL Server in Cluster2 is set to synchronous commit to ensure that the migration is free of data loss.

In this way, we have established an AG environment that contains both win 2008r2 and win 2012. And can also provide services to the outside, the whole process does not need downtime.

It should be noted, however, that this is not allowed to be failover between two cluster. The corresponding hint information is as follows

An attempt to fail over or create a availability group failed. This operation isn't supported when AlwaysOn availability Groups is running under a remote Windows Server Failover cluste The ring (WSFC) cluster the context. Under a remote cluster context, the failing over or creating availability groups the not are.

Next stop the application and remove the listener from the cluster1 to ensure that no outside access is available to use SQL SERVER

Offline operation of AG in Cluster1

ALTER Availability GROUP dbname offline

Switch the cluster context of all SQL Servers in the Cluster2 back

ALTER SERVER CONFIGURATION SET HADR CLUSTER context=local

Re-create AG in Cluster2

Create a new listener in Cluster2

Restart Application

So the downtime involved is 5+6+7+8+9+10.

Eliminates backup, file copy, and restore time compared to previous solutions. The rest of the operation is a sentence operation, to a large extent reduce the downtime.

More information

===

SQL Server in Cluster2 does not allow the creation of any AG prior to migration.

You need to grant the SQL Server startup account in Cluster2 access to the Cluster1 registry before migrating

Change the HADR Cluster context of Server Instance (SQL Server)