SQL Server Performance Forum – Threads Archive

Co-location and Replication

I have 4 servers. (2 Win2k (IIS), 2 Win2k (SQL 200). 2 servers at main location and 2 servers at colocation facility. At the main location the IIS and SQL2000 both replicate data to the location 2 for redundancy. If location 1 goes down, people will use location 2 (read/write also) Problem:
What is the best way to assure that if location 1 goes down and people write data to location 2 that the data will be replicated back to location 1 when it comes back on line and vice versa. Mike

Hi Mike, The IIS boxes tend to not be too hard to implement, as they are normally stateless. If you have content replication from the primary to the secondary, then you will either have to copy any changes on the secondary back to the primary IIS server when it comes back up, or not make any content changes while your in DR mode For the database merge replication could be a good choice. There are complications though… merge replication requires a GUID column in every table, and has restrictions in terms of text/image columns. Also the secondary site can become the primary for as long as its identity columns (assuming you have any) still have unused values, since in merge replication these are allocated to the different servers in blocks of reservations another option is log shipping, but others here will have way more experience with this. a third option would be to go to Windows 2003 and use geographically a dispersed cluster, this would give you automatic failover and failback, but is way more expensive in terms of hardware Should the solution be automatic failover to the second site, or manual? Cheers
Twan

Are you looking for a failover support or hot standby setup of SQL Server?
Maintenance of replication on cluster setup is bit hard and as suggested by Twan I would choose third option of taking WIn2kÂ£ advantage. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If I need to upgrade 2 Win2K3 then I can do that. I just need the second colocation site to be able to update records when site 1 goes down and then replicate back to site 1 when it comes back online and then they will both synchronize to each other when everything is running correctly. Mike

http://www.sql-server-performance.com/q&a108.asp fyi. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>