Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use Transactional Replication and Database Mirroring at the same time Expand / Collapse
Author
Message
Posted Tuesday, October 05, 2010 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, October 05, 2010 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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
Posted Wednesday, October 13, 2010 6:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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
Posted Thursday, October 14, 2010 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 14, 2010 10:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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
Posted Thursday, October 14, 2010 11:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 »

Add to briefcase

Permissions Expand / Collapse