Replication does detect Mirrored Source Fail Over

I'm trying setup a proof of concept for a client but I’m having issues with mirroring and replication.

With the help of http://technet.microsoft.com/en-us/library/ms151799.aspx I’ve done the following..

The setup is as follows, I have several instances of SQL Server

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

C is the distributor

E is subscribes to A, with parameters for PublicationFailoverPartner set to B

The mirroring between A+B works fine, I can manually fail each and the databases keep in step.

Without testing the mirroring failover, the replication between A+C+E also works.

However when I test the mirror to failover between A+B no transactions are replicated though to E. The publication moves from A to B in the Server Management Studio.

The logs state that the log reader agent can not gain access to A, however nothing is recorded regarding any attempts to contact B. In the replication monitor the error message

"The concurrent snapshot for publication ABCXYZ is not available because it has not been fully generated of the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Spapshot Agent for the publica..." (cut off).

"the process could not execute 'sp_replcmds' on 'A'

I have checked that the SQL Server Agents are running on all agents,

I have checked that that server B is set at the PublisherFailoverPartner on server C agent configuration, I'm now at a lost as to what is wrong,

Any help or suggestions are greatly appreciated.

Pls do not duplicate

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1958649&SiteID=1

|||

Sorry about this Deepak, I had duplicated this as I though I had lost my original post, it was just in anothor forum,

appologies for being a Topic Hog

Replicating mirrored dbs

Hello,
We're looking to replicate Database A to Database B. (transactional DB to a
reporting server).
Both database A and database B are mirrored. Can we setup replication from
database A to database B and have failover not interupt replication? I've
been playing around with a test setup and replication works fine until I
failover database A or database B. I've performed the steps outlined here as
best as I understand them:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
However once I failover either database, replication complains that it can't
connect to the publishing db or subscription db. (which makes sense since
they've failed over). Any direction or resources on setting this up would be
greatly appreciated.
Thanks in advance.
I forgot to mention this is one way transactional replication with no
updating subscribers.
"sqlboy2000" wrote:

> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would be
> greatly appreciated.
> Thanks in advance.
|||No, but if you have a remote distributor you can use the alternate log
reader switch to have the standby come up and replicate to the remote
distributor and its subscribers. Have a look at the PublisherFailoverPartner
setting.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
> a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
> as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it
> can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
> be
> greatly appreciated.
> Thanks in advance.
|||Thanks. That parameter setting did the trick for the publisher failover, it's
pretty seamless. (We're using a remote distributor).
As to the subscriber failing over, I've created a script that drops the push
subscription to the former primary and recreates it for the failed over
subscriber. Not totally automatic, but I'll take it.
Thanks.
"Hilary Cotter" wrote:

> No, but if you have a remote distributor you can use the alternate log
> reader switch to have the standby come up and replicate to the remote
> distributor and its subscribers. Have a look at the PublisherFailoverPartner
> setting.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
>
>

Replicating mirrored dbs

Hello,
We're looking to replicate Database A to Database B. (transactional DB to a
reporting server).
Both database A and database B are mirrored. Can we setup replication from
database A to database B and have failover not interupt replication? I've
been playing around with a test setup and replication works fine until I
failover database A or database B. I've performed the steps outlined here as
best as I understand them:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-
218bae11a2d6.htm
However once I failover either database, replication complains that it can't
connect to the publishing db or subscription db. (which makes sense since
they've failed over). Any direction or resources on setting this up would be
greatly appreciated.
Thanks in advance.I forgot to mention this is one way transactional replication with no
updating subscribers.
"sqlboy2000" wrote:

> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab
5-218bae11a2d6.htm
> However once I failover either database, replication complains that it can
't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
be
> greatly appreciated.
> Thanks in advance.|||No, but if you have a remote distributor you can use the alternate log
reader switch to have the standby come up and replicate to the remote
distributor and its subscribers. Have a look at the PublisherFailoverPartner
setting.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
> a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
> as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab
5-218bae11a2d6.htm
> However once I failover either database, replication complains that it
> can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
> be
> greatly appreciated.
> Thanks in advance.|||Thanks. That parameter setting did the trick for the publisher failover, it'
s
pretty seamless. (We're using a remote distributor).
As to the subscriber failing over, I've created a script that drops the push
subscription to the former primary and recreates it for the failed over
subscriber. Not totally automatic, but I'll take it.
Thanks.
"Hilary Cotter" wrote:

> No, but if you have a remote distributor you can use the alternate log
> reader switch to have the standby come up and replicate to the remote
> distributor and its subscribers. Have a look at the PublisherFailoverPartn
er
> setting.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
>
>

Replicating mirrored dbs

Hello,
We're looking to replicate Database A to Database B. (transactional DB to a
reporting server).
Both database A and database B are mirrored. Can we setup replication from
database A to database B and have failover not interupt replication? I've
been playing around with a test setup and replication works fine until I
failover database A or database B. I've performed the steps outlined here as
best as I understand them:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
However once I failover either database, replication complains that it can't
connect to the publishing db or subscription db. (which makes sense since
they've failed over). Any direction or resources on setting this up would be
greatly appreciated.
Thanks in advance.I forgot to mention this is one way transactional replication with no
updating subscribers.
"sqlboy2000" wrote:
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would be
> greatly appreciated.
> Thanks in advance.|||No, but if you have a remote distributor you can use the alternate log
reader switch to have the standby come up and replicate to the remote
distributor and its subscribers. Have a look at the PublisherFailoverPartner
setting.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
> a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
> as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it
> can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
> be
> greatly appreciated.
> Thanks in advance.