-> Option 1

Option 1 utilizes standalone database server JBSERVER1 and JBSERVER2 participating in a basic availability group using synchronous commit with automatic failover for high availability in primary datacentre. Logshipping is configured between Primary Replica in Primary datacentre to JBSERVER3 in secondary datacentre for disaster recovery. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as Listener will be utilized.

It should be noted that in basic availability group each availability group can contain only one database. In an event of some databases failing over to secondary replica and other databases residing on primary replica can result in issues. You can workaround this by failing over all availability group in an event of single availability group failover using this article.

-> Option2

Option 2 utilizes SQL Server installed on a failover cluster with shared storage. JBSERVER1 and JBSERVER2 will be the physical nodes with a virtual SQL Server installed and configured for high availability. Basic availability group using Asynchronous commit with manual failover will be configured between the virtual SQL Server in Primary datacentre to JBSERVER3 in secondary datacentre. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as virtual SQL Server name will be utilized.

-> Option 3

Option 2 utilizes SQL Server installed on a failover cluster with shared storage. JBSERVER1 and JBSERVER2 will be the physical nodes with a virtual SQL Server installed and configured for high availability. Logshipping is configured between the virtual SQL Server in Primary datacentre to JBSERVER3 in secondary datacentre for disaster recovery. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as virtual SQL Server name will be utilized.

It is prudent to test high availability and disaster recovery to make sure how long does it take and if it is acceptable to your business. It is mandatory that the design can achieve the required RTO and RPO.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.