SQL 2005 Mirroring and Replication  
Author Message
Gatordog





PostPosted: Thu Mar 02 13:00:18 CST 2006 Top

SQL Server >> SQL 2005 Mirroring and Replication

I make a lot of use of replication (currently SQL2000 SP3) , but nowhere
can I find anything that says what compatibility there is with SQL2005
Mirroring.

Hypothetical...In a SQL2005 Mirrored scenario......

Primary setup with both Transactional Replication. Does the 2nd box take
over replication when failover occurs? Or does Replication need setting up
on it again?

Simple questions, but I cannot find anything to give me the answers.

Regards
Paul

SQL Server62  
 
 
Michael





PostPosted: Thu Mar 02 13:00:18 CST 2006 Top

SQL Server >> SQL 2005 Mirroring and Replication There is some limited compatibility between replication and mirroring.
Mirroring the publisher or subscriber is supported. Mirroring the
distributor is not. I really have no idea why it isn't.

Understanding what is going on really isn't that complicated. The first
thing you need to realize is that replication isn't anything magical. It is
plain and simply an executable. 5 executables in fact: logread.exe,
distrib.exe snapshot.exe, replmerg.exe, and quesrvc.exe (or something like
that). So, in otherwords, replication is nothing more than an application.
The means it looks and behaves just like any other application. It has to
use a connection library to connect to the publisher, distributor, and
subscriber. It is at that connection level where the interaction with
mirroring occurs. If the replication exes are using the new access
libraries, then they have the ability to take advanatge of the transparent
client redirect capability and since the replication exes are already coded
to retry as well as reconnect, there isn't much you need to do.

At the moment, this is all theory. I haven't had the time to start playing
heavily with replication interacting with a Database Mirror session.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.




>I make a lot of use of replication (currently SQL2000 SP3) , but nowhere
> can I find anything that says what compatibility there is with SQL2005
> Mirroring.
>
> Hypothetical...In a SQL2005 Mirrored scenario......
>
> Primary setup with both Transactional Replication. Does the 2nd box take
> over replication when failover occurs? Or does Replication need setting
> up
> on it again?
>
> Simple questions, but I cannot find anything to give me the answers.
>
> Regards
> Paul


 
 
Paul





PostPosted: Thu Mar 02 18:05:28 CST 2006 Top

SQL Server >> SQL 2005 Mirroring and Replication

> There is some limited compatibility between replication and mirroring.
> Mirroring the publisher or subscriber is supported. Mirroring the
> distributor is not. I really have no idea why it isn't.
>
> Understanding what is going on really isn't that complicated. The first
> thing you need to realize is that replication isn't anything magical. It is
> plain and simply an executable. 5 executables in fact: logread.exe,
> distrib.exe snapshot.exe, replmerg.exe, and quesrvc.exe (or something like
> that). So, in otherwords, replication is nothing more than an application.
> The means it looks and behaves just like any other application. It has to
> use a connection library to connect to the publisher, distributor, and
> subscriber. It is at that connection level where the interaction with
> mirroring occurs. If the replication exes are using the new access
> libraries, then they have the ability to take advanatge of the transparent
> client redirect capability and since the replication exes are already coded
> to retry as well as reconnect, there isn't much you need to do.
>
> At the moment, this is all theory. I haven't had the time to start playing
> heavily with replication interacting with a Database Mirror session.

Cheers Mike,

I hadn't thought about it being 5 apps like that. In my case though, the
Primary would be the distributor as well. I do have other possible ways of
doing this so it wouldnt be a showstopper if its not possible. We also
make use of Red-Gates' Sync toolkit which of course essentially does the
same thing in terms of results (and is arguably easier to set up!)