How to migrate alwayson AG

Windows Cluster requires that all Windows versions in the same cluster be the same, so there is a problem. When we want to upgrade windows, (For example, upgrading from Windows 2008 R2 to Windows 2012) has to build a new Windows Cluster. You can choose to build a new hardware, or drop an evict from an existing windows cluster node, reinstall/upgrade the system, and add it to the new Windows Cluster. I will not discuss the specific cluster upgrade Scheme here. Enter the topic immediately:

One requirement of the SQL Server alwayson availability Group (AG) Is that all replica instances belong to the same Windows Cluster.

Therefore, when we upgrade Windows Cluster, Ag cannot be created between the new Windows Cluster and the existing Windows Cluster. In this case, AG will not be able to provide external services for a period of time during the migration process.

 

From the database perspective, we need to do the following:

1. Next, stop the application and delete the listener in cluster1 to ensure that SQL server is not used by the outside world.
2. Backup Database
3. Backup tail log
4. Copy the backup file to the new server.
5. Restore to each server
6. Then re-create AG
7. Create listener
8. Restart an application

We need to back up the database and restore it to the new primary replica and secondary replica. The corresponding downtime is the time required for 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8. You may have thought of creating a memory ing between the new and old clusters, but unfortunately, the database that created AG is no longer allowed to create memory ing.

 

So how should we migrate it? Starting from SQL Server 2012 SP1, Ag can be created between two different windows clusters. The following is an example.

 

There is a three-node Windows Cluster. the Windows version is Windows 2008 R2.

Domain: liweiyin3.lab

Cluster name: cluster1

Server002

Server003

Server004

Listener name: listener1

The three nodes are equipped with standalone instances of SQL Server 2012 SP1. All are default instances.

The AG is created between them. The topology is as follows:

 

 

Create a Windows Cluster with two nodes in Windows 2012

Domain: liweiyin3.lab

Cluster name: cluster2

Server005

Server006

 

Datacenter 1
Server005	Server006
Win 2012	Win2012
Cluster2

 

Create Ag between two clusters:

1. Back up the AG database on cluster1, including full database backup and log backup
2. Restore the file obtained in step 1 on the cluster2 node and specify it as with norecovery.
3. Next, execute the following statement on the three databases of cluster2.

   Alter server configuration set hadr cluster context = 'terter1. liweiyin3.lab'

   After the statement is executed, the cluster context of the database is switched to cluster1. This result can be found in the following dmv

   Select cluster_name from SYS. dm_hadr_cluster

 

1. Next, you can create an ag between cluster1 and cluster2. We can use the ui or T-SQL statement.

   Note that set the synchronization mode of at least one SQL Server in cluster2 to synchronous commit to avoid data loss during migration.

 

In this way, we have created an ag environment that includes both win 2008r2 and win 2012. It can also provide services to the outside world normally, and the whole process does not require downtime.

 

However, in this case, failover is not allowed between two clusters. The 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 underRemoteWindows Server failover clustering (wsfc) Cluster context. Under a remote cluster context, failing over or creating availability groups are not supported.

 

1. Next, stop the application and delete the listener in cluster1 to ensure that SQL server is not used by the outside world.
2. Perform offline operations on Ag in cluster1

   Alter availability group dbname offline

3. Switch back the cluster context of all SQL servers in cluster2

   Alter server configuration set hadr cluster context = Local

4. Re-create AG in cluster2
5. Create a listener in cluster2
6. Restart an application

    

 

The downtime involved is 5 + 6 + 7 + 8 + 9 + 10.

 

Compared with the previous solution, backup, file copy, and restore are saved. The remaining operations are sentence Operations, greatly reducing the downtime.

 

More information

===

Before migration, SQL Server in cluster2 cannot create any AG.

Before migration, you must authorize the SQL server startup account in cluster2 to access the ter1 registry.

Change the hadr cluster context of server instance (SQL Server) http://msdn.microsoft.com/en-us/library/jj573601.aspx

 

How to migrate alwayson AG