Expanding AlwaysOn Availability Groups with Replication Subscribers

Summary

Carrying on from my previous article – Expanding AlwaysOn Availability Groups with Replication Publishers, This article will take you through the steps required to provide High Availability (HA) / Disaster Recovery (DR) for your subscription databases. In my previous article, I described the new features that are supported in the release of SQL Server 2012 for replication. I am not going to restate these here.

When you add a publisher database into an Availability Group (AG) and then follow the publisher redirection steps, you are able to have automatic reconnection of your publishers and subscribers after an AG failover. Replication Subscribers, on the other hand, are not able to achieve an automatic reconnection between the publisher, distributor and subscribers on AG failover.

When a subscription is created, this creates a SQL Agent Job that runs the distribution agent. This agent iterates through the published transactions and replicates them to the appropriate subscriber. If you have multiple subscriptions then you will have multiple distributor agent jobs. There is a distinct one-to-one relationship between the distribution agent job and the server that the subscriber database resides on. By adding a subscriber database to an AG, we are creating a one-to-many relationship. Because of this, you need to do a manual process to set up a subscription in an AG. This needs to be repeated after a failover of the AG to allow replication to continue working.

Scenario

In our environment, we need the ability to provide HA & DR for our subscriber databases.

To meet these requirements and for the purpose of this article we have the following environment:

• 3 Node Windows Server Failover Cluster (WSFC)
  • Server2012Node1
  • Server2012Node2
  • Server2012Node3
• 3 Standalone default instances of SQL Server 2012
• 1 Clustered Instance of SQL Server 2012

Image 1 – Environment

On Server2012Node1 we have a database AGTransRepl which has been configured with Transactional Replication to a subscriber on SERVER2012Node2. (This was setup in my previous article –Expanding AlwaysOn Availability Groups with Replication Publishers) To provide some HA for our subscriber database we will create a new AG that will be configured with Synchronous data transfer fromSERVER2012Node2 to SERVER2012Node3.

This is my development test environment and I am limited in the number of VMs that I am able to configure and run. In a production environment, this would potentially be split into two WSFCs using a combination of traditional Failover Cluster Instances (FCI) and AlwaysOn Availability Groups (AGs). The concepts are relevant all the same.

To set up our environment to use transactional replication in conjunction with AGs, we will follow these steps:

1. 1. TestConfirm that Transactional Replication is working
   2. Create an AG
   3. Failover the AG
   4. Configure Subscription after a failover
   5. Failback steps

Confirm Transactional Replication is working

Right-click on the Replication Tree in SSMS | Select Launch Replication Monitor. Confirm the status of your newly-created transactional publication.

Image 2 – Replication Monitor

Create an AG

The steps on how to create an availability group are described in my previous article SQL Server 2012 AlwaysOn. In my previous article, we set up an Availability Group Listener (AGL) for the AG that our published database was added to. We will need to know that AGL when we are configuring our subscription after it has been ‘failed over’. As with all AGs, you have the choice or not to create our Subscription AG with an AGL. If you did not configure your AGL when initially creating your Subscriber AG, it can be added afterwards.

Expand AlwaysOn High Availability | Availability Groups | Expand your AG | Right-click on Availability Group Listeners | Add listener ….

Image 3 – New Availability Group Listener

Enter your Port Number

Click Add.

Enter your IP Address that will be associated with your Subscriber AGL.

Image 4 – AGL IP Address

Click Ok

Now that we have created our AG with our published database in it, we need to finish off some configurations to complete our setup.

Configure the subscription after and AG Failover

As we are configuring a subscription to be part of an AG, I would recommend doing the failover to a secondary replica during an agreed window so that you are able to perform these configuration steps in a controlled manner. If you instead leave it until after an unexpected failure, then the pressure is on to bring the subscription back up and online.

Our first step is to perform a Manual Failover of our Subscriber AG. This can be achieved with the following script code:

    1: — Use SQLCMD mode to run these script parts

   2:  :Connect Server2012Node3

   3:  Alter Availability Group SQL2012_Subs Failover;

 You will need to connect to your distribution server and open the SQL Agent Job Activity Monitor. You will now see that the distribution agent job for subscription that we have just failed over has a status of “Between retries”.

Image 5 – Distribution Agent retries

Right-click on this job and select ‘Stop Job’ to ensure the job is not trying to connect to the old subscriber server (SERVER2012Node2) as this is now a readable secondary replica database.

If we start our Replication Monitor we can also see that our status is “Retrying failed command”.

Image 6 – Replication Monitor retry failures

The replication transactions will now be banking up and so we need to create a new subscription on our new primary replica server. Fortunately we are able to create a new subscription initialising from LSN which has the potential of saving us a lot of time. We do not need to take a new snapshot.

Our first step is to connect to our new primary replica and retrieve the Transaction_TimeStamp from theMSreplication_subscriptions table in our replicated database.

    1: — Use SQLCMD mode to run these script parts

   2:  :Connect Server2012Node3 

   3:  Select Transaction_TimeStamp

   4:  From AGTransRepl_Sub..MSreplication_subscriptions;

 Take a copy of the Transaction_Timestamp as this will be need shortly. It will look something like this: — Transaction_TimeStamp : 0x00000045000001700003000000000000

As we have failed over the AG there is some remanent Metadata about the old subscription now on the new primary replica. To cleanup this old subscription metadata run the sp_subscription_cleanup stored procedure.

   1: — Use SQLCMD mode to run these script parts

   2:  :Connect Server2012Node3 

   3:  Exec sp_subscription_cleanup    

   4:              @Publisher = 'SERVER2012Node1',    

   5:              @Publisher_DB = 'AGTransRepl';

 We now connect to our Publisher AGL to add the new subscription onto our new Subscription AG primary replica. This is where you will need the Transaction_Timestamp that we mentioned above.

    1: — Use SQLCMD mode to run these script parts

   2:  :Connect Repl_AGL 

   3:   

   4:  Use AGTransRepl; 

   5:   

   6:  Exec sp_AddSubscription    @Publication = 'AGTransRepl',    

   7:                    @Subscriber = 'SERVER2012Node3',    

   8:                    @Destination_DB = 'AGTransRepl_Sub',    

   9:                    @Subscription_Type = 'Push',    

  10:                    @Sync_Type = 'Initialize from LSN',    

  11:                    @Subscriptionlsn = 0x00000045000001700003000000000000,    

  12:                    @Article = 'All',    

  13:                    @Update_Mode = 'Read Only',    

  14:                    @Subscriber_Type =0;

Now if you open up your Replication Monitor you will notice the new subscription has been created and is catching up with the transactions that have been collecting on the publisher.

Image 7 – Replication Monitor new subscription

This has created a new SQL Agent job on your distribution instance of SQL Server 2012. Checking the SQL Agent Job Activity Monitor will show the new job.

Image 8 – SQL Agent Job Activity Monitor

At this point we now have a subscriber database participating in an AG. For us to failback to our original replica we can perform the following steps:

    1: — Use SQLCMD mode to run these script parts

   2:  :Connect Server2012Node2 

   3:   

   4:  Alter Availability Group SQL2012_Subs Failover;

The following 2 steps can be undertaken after any failover of the Subscription AG now that we have configured our subscriptions.

1. 1. Stop our distribution agent job on our distribution server that is showing a status of “Between retries”.
   2. Start the distribution agent job for the subscription on our now primary replica.

To confirm your replication is working, go back to your Replication Monitor and you should now see the agent running on your current primary replica.

 References

Replication Subscribers and AlwaysOn Availability Groups –http://msdn.microsoft.com/en-au/library/hh882436.aspx

Configuring Replication for AlwaysOn Availability Groups –http://msdn.microsoft.com/en-us/library/hh710046.aspx

Supported Features with AlwaysOn Availability Groups –http://msdn.microsoft.com/en-us/library/hh403414.aspx#PublisherRedirect

SQL Server replication – http://msdn.microsoft.com/en-us/library/ms151198.aspx

What’s New (Replication) – http://msdn.microsoft.com/en-us/library/bb500342.aspx

Replication and SQL Server Versions –http://support.microsoft.com/kb/164517

Further Reading

Replication Administration – http://msdn.microsoft.com/en-us/library/ms152548.aspx

AlwaysOn Availability Group Interoperability –http://msdn.microsoft.com/en-us/library/hh710077.aspx

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups – http://msdn.microsoft.com/en-us/library/hh403414.aspx

Subscription Cleanup – http://msdn.microsoft.com/en-us/library/ms188393.aspx