SQL 2012 Enterprise Edition’s AlwaysOn Availability Groups brings high availability and disaster recovery to an entirely new level for the product- gone are the days of relying on log shipping and physical clustering! With CenturyLink Cloud’s Global Data Center footprint, it is easy to deploy a globally scaled and highly available database architecture in record time.
SQL AlwaysOn Database Availability Groups require an Active Directory environment, as well as SQL 2012 Enterprise Edition- both are can easily be automated through the CenturyLink Cloud “Create Server” wizard or use of our Blueprint Library. This article assumes an existing AD Forest, and as such we simply add the “Join Domain” and “Install SQL” tasks on the final page of the “Create Server” walk-through:
Create the desired amount of servers, in the appropriate groups and desired data centers- in this example, we are using four servers across CenturyLink Cloud’s two Canadian data centers- Toronto and Vancouver. Our servers will need to communicate with each other, while all CenturyLink Cloud data centers are equipped with 10G interconnects, we will still need to allow communication between networks and vlans.
In the Control Portal, navigate to the “Firewall” section uunder the “Network” tab. Click “Add Policy” and choose the appropriate vlan by clicking “Set Local Address”
In this example, we will enable communication across the entire subnet range (CIDR /24). Cross data center firewall policies allow communication across all ports, so if your security policies require restricting access to a certain group of hosts, choose your network segment carefully. Likewise, if you require only certain ports to be open, they must be secured on the host OS layer (though intra-data center policies allow for the specification of ports or a port range).
We repeat the same process for the remote address segment, and save the firewall policy.
Once the servers have been created, we will need to install the Failover Clustering Role. This is done via the Add Roles function of Server Manager:
This action can also be batch-applied across an entire group via the “Execute Script” groups command. Open up the Failover Cluster Manager, select “Validate Cluster” and add all of your SQL server hostnames:
Click “Run All Tests”. It is expected for some tests to fail or pass with warnings. View the report and troubleshoot any issues that may prevent the cluster formation. After validation, proceed to create the cluster and virtual IP addresses. Once the cluster is created, you should see all nodes present and online:
We will then configure the quorum settings. Right click the cluster name, go to “More Actions” and select “Configure Cluster Quorum Settings”.
Select “Node and File Share Majority” as we will be utilizing a file share rather than shared disk. Click “Next” and enter the path to the share to be used for the witness server. This share must reside on a different server than the cluster nodes- in this instance, a share on the Domain Controller was previously created.
Click Next twice and then Finish to complete the cluster quorum configuration. Finally, we ready to build our SQL AlwaysOn Availability Group!
First, enable the AlwaysOn Group feature in SQL- this step will need to be done on all participating servers. Open SQL Configuration Manager (Start Menu->SQL 2012->Configuration Tools) and with SQL Server Services highlighted, double click on the SQLServer (MSSQLSERVER)
Select the “AlwaysOn High Availability” tab and check the Enable box, and hit OK. The SQL Service will need to be restarted, which you can do by right clicking and selecting restart in the Configuration Manager.
Next, open SQL Server Management Studio. Connect to the local SQL Server instance. In Object Explorer, expand the AlwaysOn High Availability folder. Right-click on the Availability Groups folder and select the New Availability Group Wizard… option. This will launch the New Availability Group Wizard.
Click through the introduction page and enter the desired name for the Availability Group in the Availability Group Name field. Click Next. You will then be presented with a list of local databases and their eligibility for Availability Group Membership. The database will need to be consistent, in Full Recovery Mode- so take a backup before starting this process, if the database is not already backed up nightly.
In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster. It is suggested to leave Automatic Failover, Synchronous Commit checked. You can choose whether or not you need a Readable Secondary database- A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. If you get a network or named pipes error- make sure that named pipes/IP connections are enabled in SQL and that Windows Firewall is configured to allow traffic through, or turned off entirely. Named Pipes/IP are configured under SQL Configuration Manager in the appropriate instance name (default MSSQLSERVER)
It is recommended that all local servers are synchronous commits, and all offsite replicas are asynchronous commits- automatic failover is recommended locally but may not be appropriate given the applications relying on the SQL Cluster. The fourth node is a readable secondary, and will be used for lagged backups and read-only operations, it cannot accept writes or be used in production.
Select Full for the initial data synchronization and specify a share accessible to all cluster nodes. After you pass the validation, click Finish.
You have just created a SQL AlwaysOn Cluster!