Use Transactional Replication and Database Mirroring at the same time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Use Transactional Replication and Database...

Use Transactional Replication and Database Mirroring at the same time

Rate Topic

Display Mode

Topic Options

Author

Message

JJS.

Posted Tuesday, October 05, 2010 8:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:39 AM
Points: 47, Visits: 153

Hi,

I have a setup transactional replication, the subscriber (pulled subscription) is used for reporting.
My problem is, I would like to setup Mirroring on the publisher DB to another server for failover.
Though it possible to use the subscriber as failover, but I find it tedious to implement (e.g., reseeding of identity column).

Is a good idea to mirror the publisher DB while it is used in transactional replication? Or is there a better approach?

Your comments and suggestions are highly appreciated.

Thanks.

Post #998381

steveb.

Posted Tuesday, October 05, 2010 8:35 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:56 AM
Points: 2,802, Visits: 7,110

You can mirror the publisher, you just need to esure that you are using a remote-distributor and that your agent is made fail-over aware

also you need to ensure that both the publisher and mirror-publisher can access the same snapshot folder.

Post #998414

gk-411903

Posted Wednesday, October 13, 2010 6:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 6:18 PM
Points: 169, Visits: 418

Please keep in mind that if something is not mirrored, then it will not be replicated ; Because in the case of a failover / switchover the new Principal cannot be ahead of the Replicated database. In other words , if the principal runs exposed then replication will lag behind.

HTH
gk

Post #1004063

JJS.

Posted Thursday, October 14, 2010 8:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:39 AM
Points: 47, Visits: 153

Noted. I'll start exploring this in test environment.
Moreover, any suggested readings/links re best practices in managing databases both in replication and mirroring esp items that I need to watch out.

Again, Thanks in advance.

Post #1004424

gk-411903

Posted Thursday, October 14, 2010 10:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 6:18 PM
Points: 169, Visits: 418

Ok. Here are some links.

How to Perform Required Pre-Replication Setup Tasks
http://technet.microsoft.com/en-us/library/bb693465.aspx
**Hopefully you have not missed on setting the replication text size in the publisher**

Frequently Asked Questions for Replication Administrators
http://msdn.microsoft.com/en-us/library/ms151740.aspx
http://vyaskn.tripod.com/repl_ques.htm

Best Practices for Replication Administration
http://msdn.microsoft.com/en-us/library/ms151818.aspx

How to enable replication agents for logging to output files in SQL Server
http://support.microsoft.com/kb/312292

Strategies for Backing Up and Restoring Snapshot and Transactional Replication
http://msdn.microsoft.com/en-us/library/ms152560.aspx

** Make sure you configure the publishing db for sync_with_backup so that in case of db crash/restore you are 'saved' from the headache of 'break in continuity'; One downside to this is the possible increase in the log size@the publisher.**

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

Monitoring (Replication)
http://msdn.microsoft.com/en-us/library/ms152751.aspx

HTH
gk(mct)

Post #1004610

winash

Posted Thursday, October 14, 2010 11:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883

This document talks about replication and database mirroring (including information on trace flag 1448 which allows the log reader to replicate changes regardless of the mirroring state):

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/ReplicationAndDBM.docx

Post #1004645

« Prev Topic | Next Topic »

Permissions