| Author |
Topic |
|
oblongman
Starting Member
3 Posts |
 Posted - 2007-06-08 : 09:08:09
|
| Hi,We are about to implement a mirroring architecture but are unsure how best to deal with the replication that will need to publish and subscribe to our mirrored serversBelow is a diagram of our initial set upDB1 -> Mirrors to -> DB2^|VDB3- DB1 and DB2 are mirrored- DB1 is a subscriber to DB3- DB3 is a subscriber to DB1My question is, if DB1 fails and DB2 is brought in then the replication will need to run between DB2 and DB3 instead.What is the best the way to implement this?Thanks in advance,Matt |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-08 : 12:47:55
|
| When you failover to DB2, then you will need to drop and recreate replication.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
oblongman
Starting Member
3 Posts |
Posted - 2007-06-11 : 04:14:40
|
| Hi,I am aware that i will need to do this but do not know the best way to implement it.We have replication going up, coming down and merging between DB1 and DB3.What is the recommended way of restarting all this replication with DB2 instead of DB1?1 - We need to ensure that no data is lost2 - The solution must be fastThanks in advanceMatt |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-06-11 : 12:11:41
|
| I have not tried this particular scenario, but I am with tara i thinking that you will need to drop/recreate replication. this means a new snapshot.this seems like a fairly simple scenario for you to test. let us know what you find out :)-ec |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-11 : 12:16:42
|
quote:
Originally posted by eyechart
I have not tried this particular scenario, but I am with tara i thinking that you will need to drop/recreate replication. this means a new snapshot.this seems like a fairly simple scenario for you to test. let us know what you find out :)-ec
If the DB at the publisher is in sync with DB2 then at least you dont need to create a new snapshot. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
oblongman
Starting Member
3 Posts |
Posted - 2007-06-11 : 12:25:39
|
| again thanks for your replies.I am not in a position to test this as yet as we are going to decide what servers to buy based on this discussion. Exciting eh ;-)1 -How do you suggest recreating replication in this case?It would take forever in the front end so i was thinking about using a script?2 - however i am also worried that when i run the scripts the data won't get out of sync. 3 - how do i get SQL to tell me that the failover has happened and it is time for me to run my script?Sorry if i seem a bit vague here i just assumed that SQL Server would manage all this for me somehow.Thanks again in advanceMatt |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-06-11 : 12:32:25
|
quote:
Originally posted by dinakarIf the DB at the publisher is in sync with DB2 then at least you dont need to create a new snapshot.
replication is pretty screwy sometimes, i would not be at all surprised if in this situation starting replication again on the mirrored system didn't work without a new snapshot. Only testing will tell.-ec |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-11 : 12:43:55
|
quote:
Originally posted by oblongman
again thanks for your replies.I am not in a position to test this as yet as we are going to decide what servers to buy based on this discussion. Exciting eh ;-)
Well theres no other way to find out than to test it. Surely you have a few development machines lying around that you can test. Setting up a high-availability solution is not as simple as reading some articles and understanding the technology. Its very very important you test out the scenario.quote:
1 -How do you suggest recreating replication in this case?
As Tara said, drop the replication from DB1, re-set up from scratch to DB2. Its a manual process. There is no automated script for it, yet.quote:
It would take forever in the front end so i was thinking about using a script?2 - however i am also worried that when i run the scripts the data won't get out of sync. 3 - how do i get SQL to tell me that the failover has happened and it is time for me to run my script?Sorry if i seem a bit vague here i just assumed that SQL Server would manage all this for me somehow.Thanks again in advanceMatt
As I mentioned earlier, this is an entirely backend process you need to be on the server and follow through a series of steps to remove the replication and re-set it to the other server. The only way is to test it. You dont need to procure high-end servers. all you need is a few development machines that can talk to each other. Restore a copy of the DB on each machine and test various scenarios.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-11 : 12:46:20
|
quote:
Originally posted by eyechart
quote:
Originally posted by dinakarIf the DB at the publisher is in sync with DB2 then at least you dont need to create a new snapshot.
replication is pretty screwy sometimes, i would not be at all surprised if in this situation starting replication again on the mirrored system didn't work without a new snapshot. Only testing will tell.-ec
"sometimes"?? I would say MOST of the times. Once its set up its generally pretty stable for a long time. Once in a while the data gets out of sync and some manual updates/deletes takes care of it. But setting it up from scratch can be as smooth as eating a cake or so painful you will remember your last 7 ancestors  Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-06-11 : 12:49:25
|
quote:
Originally posted by oblongman
again thanks for your replies.1 -How do you suggest recreating replication in this case?It would take forever in the front end so i was thinking about using a script?2 - however i am also worried that when i run the scripts the data won't get out of sync. 3 - how do i get SQL to tell me that the failover has happened and it is time for me to run my script?
1. yes, you would want to script this2. how will the data get out of sync? if the application feeding data to db1 successfully switches to db2 how do you get out of sync?3. I don't think SQL will tell you a failover has occured. You will need to code something to monitor the state of mirroring. here is a good resource to get you started http://technet.microsoft.com/en-us/library/ms365781(SQL.90).aspx-ec |
|
|
|
|
Mirroring and replication