High availability - SAN Replication coupled with SQL Server Transactional Replication


  • All SQL Servers versions are SQL Server 2008R2

    We are looking at various HA options and have decided upon SAN replication between two Data centres in two separate domains. We are SAN replicating all production databases but not system databases.

    At the same time we need to replicate production data using Transactional Replication from the primary data centre through a remote distributor to the subscriber in a third data centre. I would like to configure our transactional replication so that the log readers are able to handle a fail over from the primary data centre to the secondary data centre without having to drop and recreate replication.

    Because SQL Server replication is so tightly knit with SQL Server Servername\Instance names I've created the same SQL Server named instances at both data centres. 

    In theory is all this possible? If not is there anything else which I can try to achieve the same results?


    • Edited by Pixilated_1 Wednesday, September 18, 2013 10:58 AM
    Wednesday, September 18, 2013 9:20 AM


All replies

  • The way this is normally done is to use mirroring between the primary data center and the secondary data center and mirror the published databases. Then you use a common remote distributor. Consult this link for more info on how to set this up.

    Now your problem is going to be the bandwidth between your primary data center and your secondary data center.

    This will not really be a good fit unless you have a low latency high bandwidth link between the two.

    You might be able to log ship between two instances, where you use the sync with backup option and log ship your publication and distribution databases.

    Another option is to do a no-sync on failover. This would likely be the approach I would use.

    What it really comes down to is what you are trying to achieve. Can you describe your goal?

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Wednesday, September 18, 2013 1:18 PM
  • Many thanks for the reply. 

    Our goal is, in the case of a disaster at our primary DC

    1. Take the front end web site down
    2. Fail over from the primary data centre to the secondary data centre
    3. Wait for the last sync (if available) to load from the primary to the secondary
    4. Bring the front end back up
    5. Continue transactional replication from the secondary datacentre to our reporting servers

    I was also looking to enable sync_with_backup at the publisher to attempt to negate the risk of not being able to apply the last sync from the primary DC to the secondary DC. 

    I was wondering if in theory that it might be possible

    1. Create two SQL Server publisher instances, one in each data centre with the same ServerName (DC1 : PRIMARYP & DC2 : PRIMARYP)
    2. Register the publisher Servername at the distributor with sp_adddistpublisher @publisher = N'PRIMARYP'
    3. This creates a linked server record with the publisher server name at the distributor 
    4. Register the remote SQL Server distributor at both SQL Server publisher instances
    5. The publisher servername will route to the publisher SQL Server instance at the primary data centre
    6. Create a publication plus articles at the primary publisher
    7. Create a subscription to the publication
    8. At the secondary publisher SQL Server instance repeat steps 6 and 7
    10. SAN Replication syncs the secondary DC with the primary DC
    11. Our network team updates the IP behind the publisher server name (PRIMARYP) to now point to the publisher SQL Server instances at our secondary data centre
    12. The log reader follows the linked server (PRIMARYP) to the publisher instance in the secondary data centre and replication continues unaffected

    How ever I'm beginning to think that I'm missing a step...i.e. Do I have to use a SQL Server technology to keep the two SQL Server publisher instances in sync rather than relying on SAN Replication?  

    Wednesday, September 18, 2013 2:06 PM
  • The problem is that you are committing yourself to a solution which is not really scalable and adds a layer of complexity and another point of failure to your system - namely the remote distributor.

    The performance problem is when you are running off your dr site with the remote distributor on your primary site. Then you will need to cluster your remote distributor.

    I think you are far better to use a no-sync subscription when you do your failover.

    SAN replication will replicate luns. If you are using something which does snapshots (SnapManager, or Replistore) it will allow you to mount a database on the DR site which will be somewhat in sync with the primary site. You can mirror an entire instance to the DR site, which will mean that SQL Server will spin up on the DR site with the same name.

    I don't know a lot about your SAN implementation, so I would advise you to try it out to see how it works. The linked server will likely want to do some Kerberos authentication where it relies on the SPN to authenticate. I don't think this will work with the type of topology you are using, but again there are subtleties here which I might be missing.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Wednesday, September 18, 2013 2:23 PM
  • Again thanks for the great advice.

    To clarify "I think you are far better to use a no-sync subscription when you do your failover." I assume that this would negate the need to mirror or log ship between the two publisher data centres?

    And to just confirm that if I were to go with that option I Would 

    1. create publication and articles at the primary 
    2. subscribe with no-sync subscription type
    4. Using SAN replication snapshot primary and restore to secondary
    5. DNS for publisher Server name at Distributor is updated to point to DR site
    6. Replication hopefully continues unaffected 

    If it was decided that snapshots were a nonrunner would the other best option be to mirror / log ship between the two data centres? The remote distributor is actually in the same domain  and data center as the subscriber.

    I appreciate that this has moved past a simple Q&A. 

    Wednesday, September 18, 2013 4:23 PM
  • No, you would need to sync the publication database to the DR site on a continual basis using SAN replication or database mirroring. Then on failover you would need to recreate the publications on the DR site and the subscriptions as a no-sync subscription. Use local distributors.

    It will pick up with minimal data loss to the subscribers. Then do your DNS thing.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    • Marked as answer by Pixilated_1 Thursday, September 19, 2013 7:56 AM
    Wednesday, September 18, 2013 6:10 PM