SQL Server AG in primary site with separate WSFC AG in secondary site I would like some feedback from those experienced with SQL Server Availability Groups (AGs) in multiple sites.

We have a scenario where there are five SQL Server 2012 Enterprise Edition servers in a one-site AG under the same WSFC cluster.

We are looking to setup a secondary DR site and would like some guidance on whether the WSFC cluster should 'span' both sites or whether we should use Log Shipping to the remote DR site, targeting a separate AG in the DR site.

We are going to have a 1 Gbps pipe between the two sites with a very low &lt; 15ms response time, but eventually we want to move to an alternate secondary site which will be ~70ms away and so I am uncertain whether we can make use of WSFC for such a latent connection.

This first DR site setup needs to be a 'template' for how we will be setting up the final DR site which is geographically disparate with the higher latency response time.  Therefore, please keep this in context in your response(s).

Some questions:


Can a separate AG be a 'recipient' (restore target) of Log Shipping?  In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?
Ideally, it would be preferable to be able to more easily 'fail back' to the primary site.  If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish.  Network latency will be a concern.


Thanks for your advice.
1.Can a separate AG be a 'recipient' (restore target) of Log Shipping? In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?


No, databases involved in an availability cannot be restored or recovered unless first removed from the availability group. You can log ship to a database NOT in an availability group, however, just fine.


  2.Ideally, it would be preferable to be able to more easily 'fail back' to the primary site. If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish. Network latency will be a concern.


All databases that belong to an AG must exist in side the SAME WSFC thus it would have to be "spanned". If network latency is a concern, work with your networking team to have dedicated bandwidth and QoS rules to make sure your traffic and the cluster traffic is getting through on time. Watch how often large bursts of log are generated, like rebuilding a very large index, and try to mitigate this as much as possible.