Automatic Failover of Replication with Mirroring

Question

Automatic Failover of Replication with Mirroring

craig.dixon

Say Hey Kid

Points: 688
More actions
May 13, 2013 at 2:54 am

#275226

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 :unsure: 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 :ermm: 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

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

craig.dixon Say Hey Kid Points: 688 More actions · Answer 2

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.

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 3

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.

craig.dixon Say Hey Kid Points: 688 More actions · Answer 4

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 :blink: haha Im so happy I didnt rush into anymore changes :Whistling: 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 :laugh:

Home Time 😎

Thanks Neeraj!