SQL Server Performance

SQL Clustering + Database Mirroring?

Discussion in 'SQL Server 2005 Clustering' started by gohsiauken, May 22, 2008.

  1. gohsiauken New Member

    Hi all,

    I am pretty new to sql clustering and will appreciate if anyone can help me.

    Currently i am working on a web portal project that will use SQL to host the databases. We plan to use either windows server 2003 or 2008 entreprise edition as operating systems and SQL 2005 or 2008 enterprise to host the databases.

    There are 3 SQL servers and they will be located at three separate locations that are few kilo meters away. Leased line connections will be built to interconnect these 3 zones. These 3 zone will be in the same domain network but in different subnet.

    The requirements of this project is to use NAS for SQL server at each zone. The SQL servers are identical at these 3 zones.

    Zone 1: SQL1+NAS1 (Active)
    Zone 2: SQL2+NAS2 (Hot standby)
    Zone 3: SQL3+NAS3 (Inactive)

    The SQL databases are stored in NAS in these 3 zones, and the data must be synchronised to each other so each will have up to date data.

    In the event of wSQL1 fail, SQL2 should become active and SQL3 should become Hot standby.

    In the event of SQL2 fail, SQL3 should become Hot standby to SQL1.

    If both SQL1 and SQL2 failed at the same time, SQL3 will become the active server.

    I think of to have ActivePassive Cluster SQL1 and SQL2. But how to add in SQL3 so that it can meet the requirement? Possible to have ActivePassivePassive scenario? Can SQL Clustering support NAS (Not shared)? Should i use database Mirroring?
    Thanks again,
    Ken

  2. MohammedU New Member

    Regular clustering will not work unless you use multi site clustering which new technology or disk replication/clustering...
    http://209.85.173.104/search?q=cach...ulti site clustering&hl=en&ct=clnk&cd=1&gl=us
    Disk replication is very expensive...
    OR you can one way transactional replication too...which very cheap...
    OR custom logshipping...
    OR Database mirroring but you will have latency and data loss issue unless you use synchronous...
  3. SQL2000DBA New Member

    Hi,
    You can try to setup Active/Passive clustering between SQL1 and SQL2 and use transactional replication to replicate data between cluster server to SQL 3 as you have lease line connectivity each zone.This type of setup will provide you automatic recovery in case primary node fails and SQL3 act as hot standby for disaster recovery. I can see only one disadvantage in this setup that is whenever both the nodes in clustering goes down, you have to do manual switch to Zone 3 and once clustering comes up,manual fail back to cluster server and possibility of such scenario may be very very less.

Share This Page