June 22, 2009 at 2:49 pm
We have a complicated setup situation that we are trying to figure out:
1) We want to have one server in NY and one server in London.
2) If the network goes down, then each location should use its local server. Once the network connection is established again, the servers should sync themselves.
Questions:
1) if we use mirroring and we lose the network, it means that one of the locations will not see the witness. Assuming that the principal server and witness are located in NY, will the London server automatically become a primary for London? Even if it cannot see the witness?
2) If the answer to #1 is yes, now we have a broken mirror with a primary server in London and a witness + primary server in NY. Once the network connection is re-established what happens? Will one of the servers revert to a mirror? Will it update the principal with its transactions?
3) If the answer to #1 is no, then our only recourse is to use merge replication? Are there any issues in scheduling a merge every 5 minutes? Can we merge stored procedures and views as well?
Thank you
June 22, 2009 at 10:42 pm
The London server would remain a mirror and it will remain in a disconnected state until you do something manually or the connection resumes (at which point it will sync itself, although it may take some time depending on how long the connection was down).
The NY server will remain the principal and will continue to operate as long as it can see the witness.
If it is acceptable for one site to only read information during the outage then mirroring may work; you would have to force a failover, put the database into read-only, and initialise mirroring from scratch when the connection returns .... probably not what you want!! If you allow it to remain read-write then you have the headache of syncing the two databases.
So replication is probably the way to go; you may be able to use transactional replication (with updatable subscriptions) rather than merge. Here are a couple of links to get you started:
http://msdn.microsoft.com/en-us/library/ms151718.aspx
http://msdn.microsoft.com/en-us/library/aa256086(SQL.80).aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply