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

Automatic Failover of Replication with Mirroring Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 2:54 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:33 AM
Points: 28, Visits: 164
Morning Everyone

I know this is a topic that has been widely discussed and I have read through a few articles on it but I cant seem to find an answer to my issue I'm hoping some of the great minds here may be able to lend me a helping hand I am working with the following setup listed below:

Server A: Principal - Publisher
Server B: Mirror
Server C: Distributor - Subscriber

Mirroring is setup and works fine with no issues, however I'm trying to make the system automatically failover to the following setup should Server A fail:

Server A: Failed.
Server B: Principal - Publisher.
Server C: Distributor - Subscriber.

I have read up on setting the -PublisherFailoverPartner to be Server B but the information I have gone through has left me fairly confused I cant seem to figure out how the local publication on Server A will transfer to Server B when the -PublisherFailoverPartner comes into effect?

I would really appreciate any help you can give Oh and the specs for the servers are also listed below, thanks again.

Specs:
Windows Server 2008 R2 Standard
SQL Server 2008 R2
Post #1452002
Posted Tuesday, May 14, 2013 7:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 839, Visits: 1,198
To achieve replication with mirroring you should use remote shared distributor and configure Principle & Mirror as Publisher on that distributor.

Also use that Distributor on both Principle & Mirror.

Here is a very useful link :-

http://msdn.microsoft.com/en-us/library/ms151799.aspx




Post #1452580
Posted Tuesday, May 14, 2013 9:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:33 AM
Points: 28, Visits: 164
Hi Neeraj,

Thanks for your reply I have configured a remote shared distributor and I have also setup the Principal and Mirror as publishers. On the distributor I have also done the following:

1: Opened Distributor Properties.
2: Opened Agent Profiles - Profile Defaults.
3: Navigated to the Log Reader Agents.
4: Created a New Default agent profile with the name "Failover Agent Profile".
5: Set the -PublisherFailoverPartner Value to be the Mirror.

I'm going to read through the link you gave me tonight when I get a chance and if I find the silly mistake I'm making I'll post it incase anyone else would find it useful

Thanks again Neeraj! I really appreciate your help.
Post #1452683
Posted Tuesday, May 14, 2013 9:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 839, Visits: 1,198
You are always welcome.
Looking at your steps seems alright to me and as you said everything has been configured correctly. So if you see any error on the error logs of Publisher or Distributor, post them, they might be helpful.
Post #1452692
Posted Tuesday, May 14, 2013 10:20 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:33 AM
Points: 28, Visits: 164
Hi again Neeraj

I just went back into my test environment and the local publications have automatically moved to the mirror I guess I didnt take into account how slow my test environment was running haha Im so happy I didnt rush into anymore changes Thanks again for all your help, and hopefully someone will find this post helpful. I think I got caught up thinking what I had done was so simple that it couldnt be correct

Home Time

Thanks Neeraj!
Post #1452709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse