Expanding AlwaysOn Availability Groups with Replication Publishers

Summary

In this article we are going to be looking at the Replication Support for AlwaysOn Availability Groups that had been provided to ensure that the Replication subscribers continue to receive replicated data after an AG failover.

AlwaysOn Availability Groups provide a more granular approach to help you to meet your High Availability (HA) and Disaster Recovery (DR) requirements in increasingly complex environments.

I will not be discussing the features or installation of SQL Server 2012’s AlwaysOn Availability Groups (AGs) or how to setup an environment. If you need further information into what AGs are, or how to setup your environment to use AGs, then please read my previous article – SQL Server 2012 AlwaysOn.

What is Replication? – “Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency”. This is the definition from MSDN, if you wish to read more you can here.

Replication was first introduced as a built-in feature back in SQL Server 6.0. With the release of SQL Server 2012 some new features and improvements were added:

• Replication Support for AlwaysOn Availability Groups 
• Replication Extended Events
• Support for 15,000 Partitions

Replication Support for AlwaysOn Availability Groups

SQL Server 2012 Replication supports the following new features:

• A publication database can be part of an AG. The publisher instance must share a common distributor. The types of replication that are supported within an AG are Transaction, Merge and Snapshot.
• A database in an AG secondary replica cannot be a publisher. Republishing is not supported.
• Peer-To-Peer (P2P) bi-directional, reciprocal transactional publications, and Oracle Publishing are not supported.
• A database that is enabled for Change Data Capture (CDC) can be part of an AG.
• A database enabled for Change Tracking (CT) can be part of an AG.

In order to support Replication with AGs, four new stored procedures have been provided :

• Sp_redirect_publisher

This new stored procedure specifies a redirected publisher for an existing publisher/database pair. If the publisher database belongs to an AG, the redirected publisher is the Availability Group Listener (AGL) name associated with the AG.

• Sp_get_redirected_publisher

Replication agents use this new stored procedure to query the distributor to determine whether the original publisher has been redirected. This would imply that you have had a failover of the AG on which your publisher is located.

• Sp_validate_redirected_publisher

This new stored procedure verifies that the current host for the publishing database is capable of supporting replication. It must be run from a distribution database.

• Sp_validate_replica_hosts_as_publishers

This new stored procedure is an extension of the sp_validate_redirected_publisher. This stored procedure validates an entire AlwaysOn replication topology. Like the stored procedure above,this one also needs to be run from a distribution database.

In order for AGs to support Replication, three replication agents were modified. The Log Reader, Snapshot and Merge Agents were modified to use the sp_get_redirected_publisher stored procedure to cope with determining where publisher is located.

As we are having a published database that is now supported for failover from a primary to a secondary replica, the default behavior for the Log Reader is that it will not process any log records until those records have been hardened across all AG Replicas. This means that if we have a primary and two secondary replicas with synchronous data transfer between the primary and one secondary, as well as asynchronous data transfer between the primary and the second secondary, the log reader will not process any of the log records until all those records have been hardened on the primary and both secondaries. Because there is asynchronous data transfer between replicas, this could potentially add some latency to the hardening of records due to distance, band width, volume of traffic etc. This leads to latency of the data transfer to the subscribers.

This behavior ensures that, in the event of a failover of the AG, the subscribers avoid getting data that is not in the new primary. There is a Trace Flag 1448 which allows us to change when the log reader reads the hardened transactions. With this flag enabled the data is allowed to be replicated to the subscriber once the transaction is hardened on the synchronous replicas. This allows for the transactions to get to the subscriber more quickly. However, in the event of a failover to an asynchronous replica, there is still a possibility that the subscriber has hardened transactions that do not exist on the publisher.

Scenario

In our environment we need to be able to provide:

• HA & DR for our application databases.
• Reporting-specific capabilities on a subset of our live transactional data.
• Specific indexes on the reporting subset of data in order to provide performance for reporting that does not impact the transactional data.

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 will be configured with Transactional Replication to a subscriber onSERVER2012Node2. As we are going to be adding the database into an AG we need to configure a remote distributor. To provide some HA for our remote distributor, we will place the Distributor database into the Failover Clustered Instance (FCI) WITSQL01Repl01. The AG will be configured with Synchronous data transfer from SERVER2012Node1 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.

In order to set up our environment to use transactional replication in conjunction with AGs, we need to follow these steps

1. Configure all AG Replicas as Publishers
2. Create an AG
3. Configure the publication for redirection

Confirm Transactional Replication is working

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

Image 2 – Replication Monitor

Configure all AG Replicas as Publishers

Each of those replicas that are participating in an AG and will have a published database needs to be configured as a publisher. Connect to the FCI that is currently configured as a distributor. Right Click Replication and then click on Select Distributor Properties.

Image 3 – Distributor Properties

Click Add and then select Add SQL Server Publisher. Perform this for each replica that will be participating in the AG.

Click Ok.

Image 4 – Distributor Replicas

Create an AG

If you need to know the steps that are required to create an availability group, you will find instructions in my previous article SQL Server 2012 AlwaysOn. When creating your AG, you have an option to create an Availability Group Listener (AGL). You will need to configure your AG with an AGL to allow you to undertake the next step in configuring your publication for redirection. If you did not configure your AGL when initially creating your AG, it can be added after the fact.

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

Image 5 – New Availability Group Listener

Enter your Port Number.

Click Add.

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

Image 6 – 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 publication for redirection

The last steps we need to perform, now that our published database is residing in an AG, is to configure our replication setup for redirection. As we have already added each replica as a possible publisher, we firstly need to ensure we have set up a linked server on each replica that is pointing to the Subscription server.

   1: — Confirm that all possible publisher nodes have a linked server to the subscriber

   2:  

   3:  -- If you have more than 1 possible publisher you will need to change the server

   4:  

   5:  -- name in the :Connect statement below.

   6:  

   7:  -- The below :Connect statement is using SQLCMD

   8:  

   9:  :Connect SERVER2012Node3

  10:  

  11:  Use Master

  12:  

  13:  Go

  14:  

  15:  Exec sp_Addlinkedserver @Server = 'SERVER2012Node2', @srvproduct = N'SQL Server';

  16:  

  17:  Exec sp_serveroption @Server = 'SERVER2012Node2', @optname = N'Sub', @optvalue = N'True';

  18:  

  19:  Go

  20:  

 We now need to connect to our Distribution server to configure it for redirection. This redirection allows the subscribers to reconnect to the publisher after a failover of the AG.

   1: — On the Distribution Instance of SQL Server connect and run the new system

   2:  

   3:  -- stored procedure sp_redirect_publisher to associate the original publisher and

   4:  

   5:  -- published database with the Availability Group Listener.

   6:  

   7:  -- This tells replication that if is not able to connect to the original publisher

   8:  

   9:  -- it is to connect to the Virtual Network Name (AG Listener)

  10:  

  11:  -- The below :Connect statement is using SQLCMD

  12:  

  13:  :Connect WITSQL01Repl01

  14:  

  15:  Use Distribution

  16:  

  17:  Go

  18:  

  19:  Exec sys.sp_redirect_publisher

  20:  

  21:  @Original_publisher = 'SERVER2012Node1',

  22:  

  23:  @Publisher_db = 'AGTransRepl',

  24:  

  25:  @Redirected_Publisher = 'Repl_AGL';

  26:  

  27:  -- Confirm that the new meta data table has been created

  28:  

  29:  Select *

  30:  

  31:  From MSRedirected_Publishers;

  32:  

  33:  Go

  34:  

  35:  -- Validate the replication setup to confirm that all replica's in the 

  36:  

  37:  -- Availability Group can serve as publishers for the published db.

  38:  

  39:  Declare @Redirected_publisher sysname;

  40:  

  41:  Exec sp_Validate_Replica_Hosts_As_Publishers

  42:  

  43:  @Original_publisher = 'SERVER2012Node1',

  44:  

  45:  @Publisher_DB = 'AGTransRepl',

  46:  

  47:  @Redirected_Publisher = @Redirected_Publisher output;

  48:  

  49:  Select @Redirected_Publisher;

  50:  

We have now given our publisher database some high availability via SQL Server 2012s AlwaysOn Availability Groups. This means that your subscribers will continue data replication after an AG failover.

References

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

SP_Redirect_Publisher – http://msdn.microsoft.com/en-us/library/hh759120.aspx

SP_Get_Redirected_Publisher – http://msdn.microsoft.com/en-us/library/hh759142.aspx

SP_Validate_Redirected_Publisher – http://msdn.microsoft.com/en-us/library/hh759079.aspx

SP_Validate_Replica_Hosts_As_Publishers –http://msdn.microsoft.com/en-us/library/hh759080.aspx

Trace Flag 1448 – http://msdn.microsoft.com/en-us/library/ms188396.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

Maintaining an AlwaysOn Publication Database –http://msdn.microsoft.com/en-us/library/hh710048.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