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 Tail Log
Copy a backup file to a new server
Restore to individual servers
and then re-establish AG
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
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
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
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.
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)