Replicate Data between 2 SQL Databases in Different Physical Locations

[mRgEE]

Hi,

We have a web farm with SQL Servers running our backend databases on them. I must migrate the web farm from one country to another.
The backend databases integrate tightly with the web sites we run in that the databases constantly change by data input from end users in
forums, user registrations, users editing their profiles etc, users entering competitions etc.

During the migration I have been asked that the web sites must stay up and available on the Internet.
This should not be too much of an issue in that I will mirror all of the sites at both locations during the DNS propagation duration.

However, I would like to be able to keep the sites dynamic with their use of the SQL databases.
Is their a way to achieve this with SQL server.

For example, if I take a copy of all the databases and mirror them onto SQL servers at the new location is their a way to configure any data changes
to databases in either location to be replicated to the other mirrored database?
That way, once DNS propogation has finished, it will not matter which site a user has hit and modified the SQL databases behind those sites as they will
both replicate any changes between them. Similar in effect to Sites and Services replication?

Hope you understand what I am trying to get at.
Any input greatly appreciated.

 

[Catadmin]

What version of SQL are you using?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mRgEE]

SQL Server 2000 (if neccessary we can upgrade).
Let me know your thoughts.

 

[Catadmin]

You say you're moving the web farm "between countries". Does this mean that the servers are on different sites & domains (or at least domains) as well?

If everything is on the same domain AND you have a persistant, reliable connection, you should be able to implement Transactional Replication.

If everything is on the same domain but you do NOT have a persistant, reliable connection, then you have to rely on Merge or Snapshot replication which will NOT be consistent. There will be an amount of latency, to be determined by which type of replication you implement and by the connection you do have, between updates.

If everything is on different domains, then you may be up a creek. I have not been successful at implementing inter-domain replication, though I believe I heard once it was possible. My issue was Security. First, the domains MUST have a two-way trust (since this was two different companies, we couldn't give them a trust and they wouldn't give us a trust), then there's the pass-through security issue of having SQL use the same account names with the same exact permissions. Once you get through all of that, replication is possible, but the type of rep again depends on your connection.

The way I had to implement to a different domain was Replicate ServerA to ServerB, Log ship from ServerB to OutsideDomain\ServerC and restore the logs.

The reason I asked about your version is that SQL 2k5 supports Data Mirroring (once it gets out of beta testing) which is supposed to support a transactional consistency like Transactional Replication but replaces log shipping. However, I don't know if it works between domains or if everything has to be on the same domain.

Does this help you out at all?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"