SQLServerWiki

“The Only Thing That Is Constant Is Change”

Transactional Replication breaks on failing over to Mirrored Server.

Posted by database-wiki on April 11, 2016

Symptoms

Problem Statement: Transactional Replication breaks after failing over to the Mirror.

– Mirroring Replicated (transactional) databases on Cluster.

– Principal: SQL Server 2005 64- bit 2176 Clustered

– Mirrored: SQL Server 2005 64 – bit 3042 Clustered

  1. Customer has 11 databases configured for mirroring on Principal; with transactional replication (remote distributor)
  2. Upon failover (manual) to Mirrored Server they were not able to access any publication and basically the replication breaks.
  3. They failed back on the Principal and dropped the pubs/subs
  4. Failed over to Mirrored Server again and tried creating the publication here but get an error “could find stored procedure”
  5. They tried using GUI first on step 4. and then were using a script (to create publication) but it fails again and they then realize it could be that the database is still pointing to the earlier Principal Server
  1. They check on one of the tables called sys_repl_servers, look up the old principal name there as they suspected and manually updated the record.
  1. They run the script to create pubs again and this time it succeeds.

Cause

Transactional Replication Not Configured as per documentation.

Resolution

– In the article http://msdn2.microsoft.com/en-us/library/ms151799.aspx “Replication and Database Mirroring” the requirements says that “The principal and mirror must share a Distributor”

– In this configuration the Principal and Mirror do not share the same distributor so that explained why the replication broke upon failover.

Replication failover to a mirror is supported for publication databases only; it is not supported for the distribution database or subscription databases. For information about recovering a distribution database or subscription database without any need to reconfigure replication, see Backing Up and Restoring Replicated Databases.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: