none
SQL Server 2014 AlwaysON in multi datacenter environment

    Question

  • We are planning for a migration from SQL 2008 one to many transactional replication model to SQL 2014 AlwaysON in a multi datacenter environment. We have Four datacenters and planning for six SQL Server Instances. First datacenter has three servers – One primary, one synchronous secondary and one asynchronous secondary. Remaining three datacenters will have one asynchronous secondary.

    Our requirement is to sync all the changes in primary replica to the remaining five secondary replicas, Connect SQL Client to the local SQL Server(No cross datacenter calls unless there is a failure) and efficient failover management.

    I have these questions. Appreciate your help and any pointers for designing AlwaysON in a multi datacenter environment.

    1. Is it a good idea to replace one to many transactional replication with AlwaysON for a high traffic multi datacenter application? Can I get same or better performance compared to Replication?
    2. In our current model, Index maintenance jobs are running at publisher as well as subscribers. We have to run these on primary because AlwaysON secondary’s are read only copies. Our total indexes size is 33 MB. Do you see any concerns if it is applied to live secondary replica?
    3. I assume one Windows Failover Cluster,  one Availability Group and one AG Listener should be good enough for all 6 instances across four datacenters. Correct me if this is wrong.
    4. I would like to use AG Listener. At the same time how do I guaranty that SQL Client always connect with local SQL Server Instance(No cross datacenter calls unless there is a failure)? I don’t want to use direct SQL connection because roles are going change after the automatic failover.

    Please let me know your comments even you came across these in SQL 2012.

    Thanks,

    Sudhakar

    Sudhakar

    Friday, January 17, 2014 10:57 PM

All replies

  • 1) Is it a good idea to replace one to many transactional replication with AlwaysON for a high traffic multi datacenter application? Can I get same or better performance compared to Replication?

    It depends. With replication, you have the option to select only the objects you want replicated. With Availability Group, you don't have that option - everything gets replicated. This being said, the amount of transaction log records replicated will be exactly the same as the amount of log records generated when dealing with Availability Groups. In replication, the only transaction log records that are replicated are the ones from the publications. This is one of the factors when you talk about performance between the two. Another factor is, with synchronous Availability Group, a log record will be considered as committed if it is persisted on the log file on the secondary replica. This affects application performance as well.

    2) In our current model, Index maintenance jobs are running at publisher as well as subscribers. We have to run these on primary because AlwaysON secondary’s are read only copies. Our total indexes size is 33 MB. Do you see any concerns if it is applied to live secondary replica?

    Secondary replicas are at most read-only. You are correct in that if you want indexes maintained, they have to run on the primary replica and the changes will be replicated to the secondary replicas. I don't understand what you mean by live secondary replica? Do you mean readable secondaries? If so, you still can't do any change on the readable secondaries because they are an exact copy of the primary. Which means you cannot run index maintenance on scondaries.

    3) I assume one Windows Failover Cluster,  one Availability Group and one AG Listener should be good enough for all 6 instances across four datacenters. Correct me if this is wrong.

    If you are accessing all of the databases in an instance as a group, then you only need one Availability Group. An example of this is SharePoint where the farm requires all of the SharePoint databases in order to be online. One AG listener will be enough and applications will connect to the database via the listener. However, you will need 4 virtual IP addresses for the Windows Failover Cluster and 4 virtual IP addresses for the AG listener name. This is on the assumption that each data center will be on a different network subnet.  

    4) I would like to use AG Listener. At the same time how do I guaranty that SQL Client always connect with local SQL Server Instance(No cross datacenter calls unless there is a failure)? I don’t want to use direct SQL connection because roles are going change after the automatic failover.

    Since you are only using a pair of synchronous replicas within the same data center, you are guaranteed that the SQL Client will always connect to the SQL Server instance on the primary data center. That is because you cannot do automatic failover with asynchronous replicas. However, if you need to failover to the other data centers, you either have to manually flush the DNS cache on the client machine or use the MultiSubnetFailover parameter in your client application connection string.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course



    Saturday, January 18, 2014 3:04 AM
    Moderator
  • Hi Edwin,

    Thank you so much for reply. This is a great help for me. I have few clarifications/follow-up questions below.

    2) In our current model, Index maintenance jobs are running at publisher as well
    as subscribers. We have to run these on primary because AlwaysON secondaries
    are read only copies. Our total indexes size is 33 MB. Do you see any concerns
    if it is applied to live secondary replica?

    Secondary replicas are at most read-only. You are correct in that if you want indexes
    maintained, they have to run on the primary replica and the changes will be
    replicated to the secondary replicas. I don't understand what you mean by live
    secondary replica? Do you mean readable secondaries? If so, you still can't do
    any change on the readable secondaries because they are an exact copy of the
    primary. Which means you cannot run index maintenance on secondaries.

    [Sudhakar]I mean to say readable secondaries are busy with high volume live traffic from
    the clients while this 33 MB Indexes are being applied from primary replica.
    There might be max 200 ms ping delay between two datacenters. I just wanted to
    make sure that Application performance is not affected while the index is applied.

    4) I would like to use AG Listener. At the same time how do I guaranty that SQL
    Client always connect with local SQL Server Instance (No cross datacenter calls
    unless there is a failure)? I don’t want to use direct SQL connection because
    roles are going change after the automatic failover.

    Since you are only using a pair of synchronous replicas within the same data center, you
    are guaranteed that the SQL Client will always connect to the SQL Server
    instance on the primary data center. That is because you cannot do automatic
    failover with asynchronous replicas. However, if you need to failover to the
    other data centers, you either have to manually flush the DNS cache on the
    client machine or use the MultiSubnetFailover parameter in your client
    application connection string.

    [Sudhakar] You are correct. We are not going to
    setup synchronous replicas between two datacenters because they are more than
    60 miles away and for performance reasons. I like MultiSubnetFailover flag.
    Even it is true client application will be guaranteed to be connected to the
    Local SQL Server under normal circumstances (No failures)? I understood how it
    is going to work when a failover happens (That is what I need). Also Client
    will be connected back to local SQL instance once the failed local SQL server
    is back online?


    Sudhakar



    Monday, January 20, 2014 9:50 PM