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 behaviour 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 behaviour 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 on SERVER2012Node2. 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) WITSQL01\Repl01. 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
- Configure all AG Replicas as Publishers
- Create an AG
- 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Confirm that all possible publisher nodes have a linked server to the subscriber -- If you have more than 1 possible publisher you will need to change the server -- name in the :Connect statement below. -- The below :Connect statement is using SQLCMD :Connect SERVER2012Node3 Use Master Go Exec sp_Addlinkedserver @Server = 'SERVER2012Node2', @srvproduct = N'SQL Server'; Exec sp_serveroption @Server = 'SERVER2012Node2', @optname = N'Sub', @optvalue = N'True'; Go |
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- On the Distribution Instance of SQL Server connect and run the new system -- stored procedure sp_redirect_publisher to associate the original publisher and -- published database with the Availability Group Listener. -- This tells replication that if is not able to connect to the original publisher -- it is to connect to the Virtual Network Name (AG Listener) -- The below :Connect statement is using SQLCMD :Connect WITSQL01\Repl01 Use Distribution Go Exec sys.sp_redirect_publisher @Original_publisher = 'SERVER2012Node1', @Publisher_db = 'AGTransRepl', @Redirected_Publisher = 'Repl_AGL'; -- Confirm that the new meta data table has been created Select * From MSRedirected_Publishers; Go -- Validate the replication setup to confirm that all replica's in the -- Availability Group can serve as publishers for the published db. Declare @Redirected_publisher sysname; Exec sp_Validate_Replica_Hosts_As_Publishers @Original_publisher = 'SERVER2012Node1', @Publisher_DB = 'AGTransRepl', @Redirected_Publisher = @Redirected_Publisher output; Select @Redirected_Publisher; |
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
- Supported Features with AlwaysOn Availability Groups
- SQL Server replication
- What’s New (Replication)
- SP_Redirect_Publisher
- SP_Get_Redirected_Publisher
- SP_Validate_Redirected_Publisher
- SP_Validate_Replica_Hosts_As_Publishers
- Trace Flag 1448
- Replication and SQL Server Versions
Further Reading
- Replication Administration
- Maintaining an AlwaysOn Publication Database
- AlwaysOn Availability Group Interoperability
- Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups
Load comments