Architecting a Hybrid Database Strategy with Microsoft SQL Server : Designing the Solution
   
Designing the Solution
Configuring AlwaysOn Availability Groups requires:
• A minimum of two virtual servers (one primary and one secondary/replica) running, in the sample solution, Microsoft Windows Server 2012 R2 Enterprise Edition
• Microsoft SQL Server 2016 Enterprise Edition
• WSFC configured between the server nodes
Additional Microsoft prerequisites and requirements for this configuration are listed in the following table. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) at https://msdn.microsoft.com/en-us/library/ff878487.aspx.
Table 2. Solution Prerequisites and Requirements
Component
Prerequisites or Requirements
Windows Server 2012 R2
• Availability groups are not supported on Active Directory domain controllers.
• Each SQL compute node must be part of a Windows Server Failover Cluster.
• Each server that hosts an availability replica must be provisioned with sufficient disk space to host all the databases in the availability group.
• A WSFC node can only host one availability replica for a given availability group. On a given WSFC node, one or more instances of SQL Server can host availability replicas for many availability groups.
SQL Server 2016
• The instances of SQL Server that host availability replicas for a given availability group must reside on separate nodes of a single WSFC cluster.
• Each server instance must be configured with the Enterprise Edition of SQL Server 2016.
• The AlwaysOn Availability Groups feature must be enabled on each server node that will host an availability replica.
Availability groups
• The number of databases and availability groups you can put on a physical host or individual virtual machine depends on the virtual hardware provisioned and workload of the environment.
• Each AlwaysOn Availability replica must be hosted by SQL server instances running on different nodes in the same WSFC cluster.
• Each AlwaysOn Availability Group can support one primary replica and up to four secondary replicas.
• Be sure to operationally test the environment, including failover scenarios, with a production-like peak-time workload, to validate the environment’s ability to handle the capacity required, and to meet or exceed the application’s SLAs.
Availability database
• Must be a multi-user database.
• Must be a read-write database. A read-only database cannot be added to AlwaysOn Availability Groups.
• Cannot be configured for database mirroring.
• Cannot belong to any existing availability group.
 
The sample solution architecture shown in the following figure is built on the Windows Server 2012 R2 Enterprise Edition operating system platform, with a four-node WSFC, consisting of a two-node cluster in the primary on-premises data center and a two-node cluster at the secondary VMware Cloud Provider Program provider facility using a stretched, active-active failover cluster configuration.
Figure 5. Sample Architecture Database Functions
 
The four SQL Server 2016 instances are configured with a single AlwaysOn Availability Group. This availability group was created with one primary replica and three secondary replicas. For more information about creating Availability Groups, see Creation and Configuration of Availability Groups (SQL Server) at http://msdn.microsoft.com/en-us/library/ff878265.aspx.
The following table describes the virtual machine functions in this sample solution architecture.
Table 3. Architecture Virtual Machine Function
Data Center Site
Virtual Machine
Function
On-Premises
Windows Server 2012 R2 (ms-dc1)
Provides DNS and Active Directory, services
On-Premises
SQL Server 2016 (ms-sql-db1)
Provides database services
On-Premises
SQL Server 2016 (ms-sql-db2)
Provides database services
On-Premises
SQL Server 2016 (ms-sql-wns)
 
Provides Cluster File Share Witness services
VMware Cloud Provider Program facility
Windows Server 2012 R2 (ms-dc2)
Provides DNS and Active Directory, services
VMware Cloud Provider Program facility
SQL Server 2016 (ms-sql-db3)
Provides database services
VMware Cloud Provider Program facility
SQL Server 2012 (ms-sql-db4)
Provides database services
VMware Cloud Provider Program facility
nsx-edge-02
VMware NSX Edge™ device