Windows server 2008 R2 upgrade to windows 2012 migration Alwayson AG method

Windows cluster requires all windows versions in the same cluster are the same, so there is a problem, when we want to upgrade windows, ( For example, upgrading from windows 2008 R2 to windows 2012) had to build a new windows cluster. You can choose to build with new hardware, or evict one node in the existing windows cluster, reinstall/upgrade the system and add it to the new windows cluster. I will not discuss the specific cluster upgrade plan here. Go to the topic immediately:

sql server AlwaysOn Availability Group (hereafter referred to as AG) One requirement is that all replicas are required to belong to the same windows cluster.

So when we upgraded the windows cluster, we could not establish an AG between the new windows cluster and the existing windows cluster. Then, during the migration process, AG will not be able to provide external services for a period of time.

From the perspective of the database, we need to do the following things

Next stop the application and delete the Listener in cluster1, to ensure that there is no outside world to use SQL SERVER.

Backup database

Backup tail log

Copy the backup file to the new server

Restore to each server

and re-establish AG

Create a Listener

Restart the application

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

How should it be migrated? Starting with SQL Server 2012 SP1, it is allowed to create an AG between two different sets of windows clusters. Here is an example to illustrate

There is a three-node windows cluster, Windows version is Windows 2008 R2
Copy code
code is as follows: Domain:liweiyin3.lab</p> <p>Cluster name: cluster1</p><p>Server002</p><p>Server003</p><p>Server004</p><p>Listener name: Listener1 three nodes A standalone instance with SQL Server 2012 SP1 installed. Both are default instances.

The AG. topology map is established as follows:

There is now a set of two nodes Windows 2012 windows cluster

Domain:liweiyin3.lab

Cluster name: cluster2

Server005

Server006

For the AG database on cluster1 Make a backup, including full database backup and log backup. Create the AG in the middle of the cluster:

Restore the file obtained in the first step on the node of cluster2, and specify it with norecovery.

Run the following statement on the three databases of cluster2
Copy the code
The code is as follows: ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT='cluster1.liweiyin3.lab' After this statement is executed, this one The database's cluster context will switch to cluster1. This result can be checked from the following DMV to
copy the code
code as follows: select cluster_name from sys.dm_hadr_cluster

The next step is in cluster1 and cluster2 Establish an AG between. We can use UI or T-SQL statements.

It should be noted that the synchronization mode of at least one SQL Server in cluster2 should be set to Synchronous commit to ensure that there is no data loss in the migration.

This way, we have built an AG environment that includes both win 2008R2 and win 2012. And it can also provide services to the outside world normally. The whole process does not require downtime.

This way, we have established an AG environment that includes both win 2008R2 and win 2012. And it can also provide services to the outside world normally. The whole process does not require downtime.

In this way, we have established an AG environment that includes both win 2008R2 and win 2012. And it can also provide services to the outside world normally. The whole process does not need downtime.

But it should be noted that in this case, it is not allowed to failover between two clusters. . The corresponding prompt information is as follows

An attempt to fail over or create an availability group failed. This operation is not supported when AlwaysOn Availability Groups is running under a remote Windows Server Failover Clustering (WSFC) Cluster context. Under a remote cluster context, failing over or creating availability groups are not supported.

Next stop the application and delete the Listener in cluster1 to ensure that there is no outside world to use SQL SERVER

Offline operation of AG in Cluster1

ALTER AVAILABILITY GROUP dbName offline

Switch CLUSTER CONTEXT of all sql servers in cluster2

ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT=local

Re-create AG in cluster2

Create a new listener in cluster2

Restart application

This is the downtime involved That is 5+6+7+8+9+10

Compared with the previous solution, the backup, file copy and restore time is omitted. The rest of the operations are sentence operations, which greatly reduces the downtime.

More Information

===

The sql server in Cluster2 does not allow the creation of any AGs before migration.

The sql server startup account in cluster2 needs to be granted permission to access the cluster1 registry before migration

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