SQL Server Performance

Co-location and Replication

Discussion in 'SQL Server Clustering' started by mikel, Mar 20, 2004.

  1. mikel New Member

    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
  2. Twan New Member

    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
  3. satya Moderator

    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.
  4. mikel New Member

    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
  5. satya Moderator

Share This Page