This post is part of a series which walks through the steps required to build a ‘High Availability’ and ‘Disaster Recovery’ (HADR) solution using both AlwaysON technologies. The AlwaysON technologies were introduced in SQL Server 2012 and include Failover Cluster Instance (FCI) and Availability Group (AG). In this series, the focus is on a particular solution as described in the ‘solution details’ section, it does not fully explain all of the concepts within AlwaysON FCI and AG.

NOTE: This is going to be a very detailed series with lots of screenshots.

Okay, let’s get started. The requirement we will focus on is captured in the diagram below.

SQL Server architecture design_FCI_AG

As we will be using abbreviations throughout the series, let’s get some definitions out of the way.

Definitions:
WSFC: Windows Server Failover Cluster
FCI: AlwaysON Failover Cluster instance
AG: AlwaysON Availability Group
VNN: Virtual Network Name
DC: Data Centre
CNO: Cluster Name Object
Node: A Windows Server. In our case, VMs running Windows Server 2012 R2.
Availability database: A SQL Server database that belongs to an AG
Availability Replica: An instance of the AG hosted on a SQL Server instance that hosts availability databases.
AD: Active Directory

Solution details:

  • Two data centres (Primary and DR)
  • One Windows Server Failover Cluster (WSFC) deployment across both DC. Three Windows Server 2012 R2 VMs will be built and become nodes in one and the same WSFC, two nodes in the Primary DC and one in the DR DC. The three nodes will be part of the same Active Directory domain.
  • The two primary nodes will host an ‘AlwaysON FCI’ which will use shared disks. Only the two primary DC nodes will use these shared disks. This ‘AlwaysON Failover Cluster instance’ will have ‘AlwaysON Availability Group’ feature enabled on it.
  • The one DR node will host a standalone installation of SQL Server 2012. This standalone installation of SQL Server will have ‘AlwaysON Availability Group’ feature enabled on it.
  • The ‘AlwaysON Failover Cluster Instance’ in the Primary data centre and the standalone installation of SQL Server will be configured to belong to the same ‘AlwaysON Availability Group’ entity.
  • An ‘Availability Group Listener’ will be created for the Availability Group. This will be the entry point (Client Access Point) for all client connections for the entire solution. The listener can be seen as the main SQL Server instance name.
  • The FCI will become the Primary ‘Availability Replica’ to host a set of ‘Availability Databases’ for the above ‘AlwaysON Availability Group’ entity.
  • The standalone SQL Server will become the Secondary Availability Replica to host the same set of ‘Availability Databases’ for the above ‘AlwaysON Availability Group’ entity.
  • There will always be a controlled failover between the Primary and the Secondary.
  • There is no synchronisation between the storage at the Primary and Secondary data centres. Data movement is achieved by sending transaction log records over the network.

Software requirements
Operating System: Windows Server 2012 R2
SQL Server version: 2012

Virtual Machine requirement (the Lab environment used for this solution)

  • Physical Servers running Microsoft Hyper-V Server 2012
  • Two virtual NICs, one for each subnet so we can simulate two geographically distinct DCs.
  • Domain controller VM – AD domain, DNS
  • Three VMs. Two will map to one subnet (Primary DC) while the third will map to the DR subnet
  • A software router is used to route traffic between the two subnets. VM running Routing and Remote Access Service (RRAS). It uses the two virtual NICs so it knows about both subnets.
  • SQL Server 2012 Developer Edition
  • Windows Server 2012 R2 Data centre evaluation edition

Prerequisites

  1. We need to setup a WSFC as described in the ‘Solution details’ section. As the series is about AlwaysON, we will not go into the details of how to build the WSFC. However, high level steps are available below.
  2. We use a file share witness. Be aware that ‘Dynamic Quorum’ & ‘Dynamic Quorum witness’ will be used.
  3. Create a SQL Server Service account in AD
  4. Create a SQL Server Agent Service account in AD
  5. FCI IP address
  6. Listener IP addresses (one for each subnet)
  7. FCI VNN
  8. AG name
  9. Listener VNN
  10. Check that .Net 3.5 SP1 has been enabled on each node. SQL Server 2012 setup will not continue if after performing the ‘Setup support Rules’ check it finds that the .Net requirement has not been met.
  11. Ensure correct permissions are available to the Cluster Name Object (CNO), also known as the Cluster Identity. It is usually the cluster name with a ‘$’ appended to it when checking from AD. If it does not have the correct permissions before you begin, your cluster resource installation (FCI VNN, Listener VNN) might not be installed correctly. Permissions required are:
    a. ‘Create Computer Object’ for the Cluster Identity
    b. ‘Read All Properties’
    c. The Cluster ID needs admin privileges on each node in the cluster

High-level steps for creating the WSFC

  • Create three VMs. You can use other virtualization platforms (e.g. VMWare), I just happen to have Hyper-V.
  • Configure storage. For the FCI’s shared storage, I configured iSCSI targets will connect to the targets using initiators from the two nodes in the Primary DC. For the DR node, I created virtual disks in Hyper-V and attached those to the DR VM from Hyper-v settings. Note: This setup is for a Lab environment.
  • Setup the OS on the nodes. We’re using Windows Server 2012 R2 for this solution.
  • Enable the Failover Clustering feature on each node
  • From a node in the Primary DC, open ‘Failover Cluster Manager’ console and run through the ‘Validate Configuration’ wizard. Include all three nodes and run all tests. Resolve all issues that come up.
  • After validating the configuration, create the cluster.

Ultimately, you can create the WSFC the way you want and there are tons of information out there to help you out.

Finally, we list out all the elements of the solution:

NOTE: This is for a Lab environment. Make sure your production environment is properly resourced.

IP Subnets:
10.10.80.0/24 (Primary DC)
10.10.90.0/24 (DR DC)

Server VM specification:

Drive letter Description Size (GB) vCPU Count
C OS 30 2
E SQL Data 20
F SQL Logs 20 Memory
G TempDB 10 4 GB

Server details:

Name IP Address Description
DC 192.168.0.200 Primary Domain Controller
Flexograf.com Not Applicable Active Directory Domain
FlexRouter 192.168.0.150    10.10.80.150   10.10.90.150 Lan Router
Flex1 10.10.80.70 Node A  (Primary DC)
Flex2 10.10.80.71 Node B  (Primary DC)
Flex3 10.10.90.70 Node C  (DR DC)
svcSQL2012 Not Applicable SQL Server Service Account
svcSQL2012Agent Not Applicable SQL Server Agent Service Account
FlexClus1 10.10.80.75   10.10.90.75 Cluster virtual network name
SQL2012FCI 10.10.80.72 AlwaysON Failover Cluster Instance
FlexAGListener 10.10.80.100   10.10.90.100 Availability Group Listener (two IP addresses required)
SQL2012AG Not Applicable AlwaysON Availability Group name

That’s it for now. In Part 2, we will perform the setup of the SQL Server FCI.

Leave a Reply

Your email address will not be published. Required fields are marked *