locked
Replication and Database Mirroring on SQL server 2005 failed after mirroring failover RRS feed

  • Question

  • I'm trying setup a proof of concept for high avaialbility solution with combination of mirroring and replication. All my sql servers are SQL Server 2005 Enterprise.

     I have followed all steps list in http://technet.microsoft.com/en-us/library/ms151799(SQL.90).aspx. The setup is as follows:

     A+B are mirrored with each other, with A being the principle and B the mirror

    C is the distributor

    As I have only three sql server instance to play with, I have created the replicated database on A and B as well. That is, A is also a subscriber of itself and B is a subscriber of A. The replication is transactional.

    The mirroring between A+B works fine, I can manually fail over. Before mirror failover, the replication A+C to A+B  also works fine.

     

    However when I test the mirror to failover between A+B, The publication moves from A to B in the Server Management Studio, but the Error Log of log reader agent keep shows that the log reader agent still trying to exec  'sp_replcmds' on A and failed due to it can't log in to the database, as now A is the mirror and this database is not accessible (restoring mode).

    I have create new profile for log reader agent and set the the switch -PublisherFailoverPartner to mirror server, but the switch does not seem to take effect. I have also tried to set the switch on log reader agent itself, no difference. I have restarted the log reader agent many times, and also after failover, with no luck.

    As we always initialize the replication with a backup, we do not actually use snapshot agent, so my snapshot agent do not start, I do not know whether this is relevant.

    As the error message clearly say the the log reader agent still tries run on the old primary server (which is now mirror after failover), it seems to me that it is the log reader agent does not detect the failover, or the -PublisherFailoverPartner  simply have no effect. I wonder what could be the underly reason to cause this.

     Any help or suggestions are greatly appreciated.

    Friday, June 5, 2009 9:45 AM

All replies

  • What release are you running on?
    Monday, June 15, 2009 9:51 AM
  • I don’t think that idea is likely to work out with any sort of reliability.  When you fail over all pending data has to get cleared and then the command gets set before the database statuses are switched. It’s never going to move fast enough on average to take the replication setting change 100% of the time.  I would suggest using log shipping instead. Inside the job for log shipping you can add a check in the MSDB for Primarily and then copy/move the log if it is.   As long as the jobs are synced or the servers linked to the same log shipping job, that should work out to do what you need without the problems you get with replication.

     

    Friday, June 26, 2009 7:53 PM