SQL Server 2012 – Automatic Transactional Replication Failover with AG

replicationsql serversql-server-2012

I am working on to set up high availability group like
SQL01,SQL02,SQL03(DR) and SQLRPT01 as the reporting server which has our reporting DB.
* From SQL01 to RPT01, I am running transnational replication.
*As of now replication works good,if SQL01 goes of AG immediately goes to SQL02 and comes in good health asap.

Question is once I have fail over from sql01 to sql02, do we have the procedure to fail-over the replication agents to SQL02 and replication resume automatically.(without Using fail-over cluster instance, because we don't have any shared storage planning for this.)
I am not sure how this sp_redirect_publisher etc. is going to work.
Do we have a step by step procedure to set up this configuration??

Best Answer

During AG failover from one server(POCSQL01) to second server(POCSQL02) , how replication agents gets re-directed.

With the introduction of AG, there were new SP's added to support replication and replication agents like Snapshot, Log Reader and Merge supports below new SP's :

sp_redirect_publisher

sp_get_redirected_publisher

sp_validate_redirected_publisher

sp_validate_replica_hosts_as_publishers

The interesting one is sp_redirect_publisher

Specifies a redirected publisher for an existing publisher/database pair. If the publisher database belongs to an AlwaysOn Availability Group, the redirected publisher is the availability group listener name associated with the availability group.

If you go into details of how that will work - the log reader agent's default behavior is that it will not process any log records until those records have been hardened across all AG replicas.

sp_redirect_publisher will tell replication that if it is not able to connect to the original publisher, connect to the AG Listener allowing the subscribers to reconnect to the publisher after an AG failover.

Run at the distributor :

Use Distribution
Go
 
Exec sys.sp_redirect_publisher
       @Original_publisher = 'Originap_Pub_Name',
       @Publisher_db = 'Pub_DB_In_AG',
       @Redirected_Publisher = 'ListenerName';
 
-- Confirm that the new meta data table has been created
 
Select *
From MSRedirected_Publishers;
 
Go

Configure Replication for AlwaysOn Availability Groups has all detailed steps on how you will configure replication and alwaysON.

As always test your layout with all possible scenarios !!

Related Question