Implementing SQL Server AlwaysOn Availability Groups on ECS Instances

Introduction

SQL Server is an open-source relational database management system (RDBMS). Its primary function is to store and retrieve data when required by other applications. It runs on various operating systems, such as Linux, UNIX, and Windows and across a wide range of applications.

The SQL Server AlwaysOn feature was made available in the SQL Server 2012 release. The AlwaysOn availability group boasts of a high-availability and disaster recovery solution. It replaces enterprise-level database image solutions to maximize the availability of a set of user databases for the enterprise. This feature sparked a revolutionary change in the industry.

The solution implements multiple readable copies as well as convenient read and write separation schemes. It outperforms Database Mirroring + Replication for achieving read and write separation with respect to availability and reliability. Several features may have restrictions in versions earlier than SQL Server 2016. However, post release of the 2016 version, SQL Server became more user-friendly and compliant with the "customer first" principle. For example, you can now achieve no-domain-control deployment and enjoy as many as nine available copies.

Alibaba Cloud ECS Instances

The Alibaba Cloud ECS allows you to load applications with multiple operating systems and manage network access rights and permissions. Within the user console, you can also access the latest storage features, including auto snapshots, which is perfect for testing new tasks or operating systems as it allows you to make a quick copy and restore later. It offers a variety of configurable CPU, memory, data disk and bandwidth variations allowing you to tailor each Instance to your specific needs.

Let us see how to implement AlwaysOn availability groups with no domain control on Alibaba Cloud ECS instances.

1. Prerequisites

Before we begin detailing the architecture solution, let us look at the prerequisites for the deployment process.

1.1 Hardware Conditions:

Container ECS: Recommended: 4-core CPU or above, 8GB memory or above. Mount SSD cloud disks as ECS.

1.2 Software conditions:

You would need the following software with the listed specifications:

● .NET Framework 4.0 or above
● Powershell 5.0 or above
● Windows Server 2016 64-bit Data Center Edition (either Chinese or English edition)
● SQL Server 2016 64-bit Enterprise Edition (It requires Enterprise Edition. Standard Edition can only implement Basic Availability)

Architecture Diagram

The following figure shows the simple architecture of implementation on ECS instances. It also demonstrates a typical software solution for SQL Server AlwaysOn availability groups.

Figure 1.

Overview of Core Components

Let us now discuss the core components of the solution.

1. Architecture Overview

The figure shows a "2 + 3" high-availability and disaster-tolerant solution. The "2" refers to primary and secondary database copies. These primary and secondary databases use sync mode to enable automatic failover between availability groups of the database. The condition for automatic failover is such that the server must utilize the synchronous replication mode. Also, the databases must have automatic failover settings enabled.

The "3" refers to read-only copies that use async, or asynchronous replication mode, which takes into account the performance of multiple copies. For writing data, two copies (primary and secondary) are adequate. You can set all other copies to asynchronous replication. Additionally, there is an access issue to consider for ECS to deploy listeners. Therefore, to meet the solution requirements, the server allows only two synchronous copies. In fact, AlwaysOn availability group can only have up to three synchronous copies, which is enough to prove that Microsoft cares about this performance problem quite a bit.

2. Virtual Private Cloud (VPC)

Being VPC-based is an underlying network environment requirement. If you deploy databases in a classic network, you will run into serious issues due to the absence of Intranet IP addresses. However, if it is a VPC, resolving the problem is possible. AlwaysOn deployment will produce two virtual IP addresses. As a result, the two IP addresses must be private and remain unoccupied. Otherwise, the cluster and the listener may fail to work normally, and HA switch won't function properly.

3. Highly Available (HA)

Users might often ask, why do we need HA? If you deploy AlwaysOn on ECS instances, you may run into listener failures.

The inability to use the listener IP address to access database instances from a non-primary node causes listener failure. The listener failure is an obstacle preventing cloudization. In fact, it is associated with VPC network implementation. While the listener happens to take the Address Resolution Protocol (ARP) protocol, its resolution is not possible. HAVIP (Highly Available Virtual IP) is an HA solution with primary-secondary high-availability architecture under VPC. It provides the capability to access instances using VIP (Virtual IP) between primary and secondary instances without switching the IP address. However, you need to implement HA and heartbeat check on your own. This coincides with AlwaysOn's failover detection, diagnosis and switch mechanisms.

Furthermore, HAVIP + AlwaysOn can solve problems caused by listener failures. However, the system allows HAVIP to use only two nodes.

We use two synchronous nodes (primary and secondary) to implement write HA. Also, we use three asynchronous read-only copies to implement reads, which fits into this scenario appropriately. The number of read-only copies may vary from one to two or further to seven. However, the number of nodes available for failover must allow votes of greater than or equal to three (nodes with domain control can use the shared folder to act as a VOTE).

This may give rise to the question - How can we access so many read-only copies? Read further for an answer to this question.

4. Server Load Balancer

Server Load Balancer is designed to address read-only nodes. Ideally, this applies if you do not need Server Load Balancer to assign weights or implement load balancing automatically. You can also directly connect to the IP addresses of read-only copies. This is particularly in the case of deployment of web apps in a distributed approach. Therefore, it is possible to connect different web apps to different read-only nodes. Nodes for writes are restricted to do this as write nodes only have one copy.

5. Elastic IP Address (EIP)

An Elastic IP address is a public IPv4 address designed for dynamic cloud computing. Using an EIP, users can mask the failure of an instance or software. However, in this scenario, an EIP is not necessary. If you want to access database instances from outside the VPC, you can bind an EIP to an HAVIP and an ECS IP address. Note that it is impossible to bind Server Load Balancer to EIP. We do not recommend EIP since access in VPC is relatively safe.

6. Elastic Compute Service (ECS)

Elastic Compute Service (ECS) is a core service that enables users to launch new instances immediately to meet with real-time demand. ECS is the most basic instance with no special requirements.

7. Windows Cluster

Windows Cluster is crucial to this solution because operations for creating clusters for nodes with no domain control are often different. Also, DNS servers may even be absent. This is a new feature of Windows Server 2016. It brings with itself the advantage of independence from the complexity of domain control. Additionally, it also adds complexity to AlwaysOn deployment. Domain control not only has to consider deployment and HA problems but also needs to consider O&M issues, which often is a bother. Windows Cluster with no domain control, despite limited functionality, is sufficient to meet conditions for deploying AlwaysOn. You can refer to the deployment plan, discussed further in this article, for more details.

8. AlwaysOn Availability Group

AlwaysOn deployment uses SQL Server 2016 since this version supports non-domain-control deployment. The difference lies in whether domain control exists. While the security authentication modes of corresponding nodes will change - mutual authentication is required using security certificates.

Domain control is fairly straightforward, relying on the security authentication mechanism. The complexity of deployment manifests the same. Another factor that is worth mentioning is the listener, which cannot normally function in a VPC. Therefore, implementation of read and write separation requires HAVIP along with Server Load Balancer. Listener access to database instances is not a very elegant issue. If it is read-only access, there will be a read-only option on the connection string. This might confuse you in considering read and write as two different strings.

Similar to HAVIP + Server Load Balancer, many may mistakenly assume that a string solves the problem. However, it is practically impossible.

Let us now look into the deployment plan.

Deployment Plan

The following section describes the deployment of a stand-alone Windows Server 2016 + SQL Server 2016 environment systematically.

Before you begin the deployment, it is necessary that you have the following as prerequisites.

1. Prerequisites

1.1 Hardware Conditions:

Container ECS: Recommended: 4-core CPU or above, 8GB memory or above. Mount SSD cloud disks as ECS.

1.2 Software conditions:

You would need the following software with the listed specifications:

● .NET Framework 4.0 or above
● Powershell 5.0 or above
● Windows Server 2016 64-bit Data Center Edition (either Chinese or English edition)
● SQL Server 2016 64-bit Enterprise Edition (It requires Enterprise Edition. Standard Edition can only implement Basic Availability)

1.3 Network requirements

Users must remember that AlwaysOn does not need dual NICs. Dual NICs cannot enable separate network connections of Windows Cluster and AlwaysOn. Some customers require dual NICs for the sake of heartbeat, which is unnecessary. Dual NICs can improve redundancy but not for heartbeat.

1.4 Demo Environment

The demo environment should fulfill the following prerequisites:

● .NET Framework 4.0
● Powershell 5.1
● Windows Server 2016 64-bit English Data Center Edition
● SQL Server 2016 64-bit English Enterprise Edition + SP1
● ECS based on VPC
● High-performance SSD cloud disks

Once you are sure of satisfying the prerequisites, you can move forward with preparing an environment for deployment.

2. Prepare Environment for Deployment

Firstly, apply for an ECS type. You must remember that the recommended memory is 8GB or above, the CPU is 4-core or above and the demo environment is 2-core CPU, 4GB memory. The network type must be VPC.

3. Modify the Host Name

Since images produce ECS instances, some of them may share the same name. While this problem is rare in VPC, to ensure absolute security, modify the host name to shorter than 15 characters and immediately restart the host. You can also manually alter the name by referring to the Powershell command, which is as follows:

Rename-Computer -NewName "ServerName" -restart -force

4. Install SQL Server Instances

During installation, pay attention to setting a reasonable launching account, which can be a network service or a local account. However, if you use a local account, you need to set a uniform password for each ECS instance. We recommend you use a network service. After the installation is complete, add the network service to the SQL Server account, and set the server role to sysadmin. Additionally, maintain full consistency between at least the user database and the log file path of each ECS installation instance. Note that this is necessary for AlwaysOn deployment.
Otherwise, an error may occur later during database creation. We recommend that all installation actions be consistent, as it is a best practice. You can click Next Step or use default installation for installing databases.

5. Install SQL Server Management Studio

Moving to the next step, we suggest you install SQL Server Management Studio (SSMS). SSMS in SQL Server 2016 is available for independent installation. The uniform engine installer does not include the SSMS, and hence you need to download it separately. The reason for installing SSMS is that you may need SQLPS (SQL Powershell).

6. Create a Unified Windows Account

Since this solution is not a stand-alone solution, you need to add an account with the same account name and password to ensure successful deployment of Windows Cluster and add the account to the administrators' group. You can use administrator; however as a best practice, do not use it as the unified password. You can create it manually, or use the following cmd command:

net user Win_Account " xxxxxx" /add
net localgroup administrators Win_Account /add
WMIC.EXE Path Win32_UserAccount Where Name="Win_Account" Set PasswordExpires="FALSE"  

7. Disable UAC Remote Restrictions

You can disable the UAC remote restrictions using the following:

Powershell command:
new-itemproperty -path
HKLM:SOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem -Name
LocalAccountTokenFilterPolicy -Value 1

8. Install Windows Failover Cluster Feature

This is a mandatory and basic structure. AlwaysOn must grow on the Windows Cluster.
Powershell command:

Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools

9. Modify the DNS Suffix of a Windows Host

This solution involves a stand-alone control. To make the solution successfully run under Windows Cluster, you need to add the uniform suffix to the hostname for identification purposes. You can modify it through the UI, or use the Powershell command:

$ParentKeyPath = "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters"
$DnsSuffix="aliyunrds.com"
New-ItemProperty -Path $ParentKeyPath -Name "NV Domain" -Value $DnsSuffix -PropertyType String

10. Change the Host's Static IP Address

Strictly speaking, you can start to use DHCP for configuration starting from Windows Server 2008. However, we suggest you use a static IP address to configure the host's network connection. You can use the UI or the Powershell command for configuration. However, remember to check the DNS configuration and adjust it as necessary. Modify the script on your own or you can adjust it manually. You can do that by using the following:

$IPType = "IPv4"

$Adapter = Get-NetAdapter | Where-Object {$_.Status -eq 'up' -and $_.name -ne 'loopback'}

$IpAddress=((($Adapter | Get-NetIPConfiguration).IPv4Address) | Where InterfaceAlias -ne "loopback").IPAddress
$PrefixLength=((($Adapter | Get-NetIPConfiguration).IPv4Address) | Where InterfaceAlias -ne "loopback").PrefixLength
$Gateway=((Get-NetIPConfiguration).Ipv4DefaultGateway).NextHop

If (($adapter | Get-NetIPConfiguration).IPv4Address.IPAddress) 
{
    $adapter | Remove-NetIPAddress -AddressFamily $IPType -Confirm:$false
}

If (($adapter | Get-NetIPConfiguration).Ipv4DefaultGateway) 
{
    $adapter | Remove-NetRoute -AddressFamily $IPType -Confirm:$false
}

# config static ip address
$Adapter | New-NetIPAddress -AddressFamily $IPType  -PrefixLength $PrefixLength  -IPAddress $IpAddress -DefaultGateway $Gateway

11. Modify the Host's Hosts File

The file is located at hosts under C:WindowsSystem32driversetc. You need to map the name and DNS suffix of every host along with the IP addresses. You can complete this with the cmd command:

copy C:\Windows\System32\drivers\etc\hosts C:\Windows\System32\drivers\etc\hosts_2017033141131
echo 172.16.18.247 iZbp1ehi2dopyqC.aliyunrds.com >> C:\Windows\System32\drivers\etc\hosts
echo 172.16.18.246 iZbp1ehi2dopyqZ.aliyunrds.com >> C:\Windows\System32\drivers\etc\hosts
echo 172.16.18.248 iZbp1ehi2dopyqA.aliyunrds.com >> C:\Windows\System32\drivers\etc\hosts

12. Create Windows Cluster

The next step involves creating the windows cluster. The latest version of Windows Server 2016 supports cluster creation through UI, but you can also choose to create the cluster through the Powershell command. You need to specify staticAddress, which is the IP address in the VPC. Be careful not to occupy it:

New-Cluster –Name clus-aliyun0001 -Node
iZbp1ehi2dopyqC.aliyunrds.com,iZbp1ehi2dopyqZ.aliyunrds.com,iZbp1ehi2dopyqA.aliyunrds.com -AdministrativeAccessPoint DNS -StaticAddress 172.16.18.101

13. Set Arbitration Mechanism for Windows Cluster

Non-domain-control AlwaysOn availability groups only support arbitration through majority nodes or based on Microsoft cloud files. Alibaba Cloud only supports the majority nodes mode. As a result, you have to deploy at least three nodes for your Windows Cluster. If you deploy four nodes, make sure to set one of them to zero voting rights. If you only need two database copies, you can use two ECS instances, with one serving as an AlwaysOn node and the other only joining the Windows Cluster.

Set VOTE: 
$node = "Always OnSrv1"  
(Get-ClusterNode $node).NodeWeight = 0 

Set no-witness:  
Set-ClusterQuorum -NoWitness  

Set majority-node arbitration: 
Set-ClusterQuorum –NodeMajority

14. Set Interval of Windows Cluster Failovers

You may observe in your testing process that after several failovers, automatic failover ceases to function after some time. This is because Windows Cluster imposes a limit on the number of automatic failovers for each resource group within a certain period. If you want to increase the limit of automatic failovers to, say 30 failovers, use the following command.

(Get-ClusterGroup "Cluster Group").FailoverThreshold = 30

15. Enable the Database AlwaysOn Feature

You must enable the database AlwaysOn feature. Below is an image for your reference.

Figure 2.

Import-Module SQLPS
Enable-SqlAlwaysOn -Path SQLSERVER:SQLLocalHostDefault -Force

16. Configure AlwaysOn Security Settings

To do this, you need to create all the instance certificates first, then copy them to the directory of each ECS instance and then rerun them. For example, you have three host instances 001, 002 and 003.

● On Instance 001, you need to create the certificates backed up from Instance 002 and Instance 003.
● On Instance 002, you need to create the certificates backed up from Instance 001 and Instance 003.
● Similarly, on Instance 003, you need to re-create the certificates of Instance 001 and Instance 002. Instances must authenticate each other to communicate.

Below are the steps of the SQL statements:

Step 1

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxx'

CREATE CERTIFICATE cer_alwayson_001
WITH SUBJECT='alwayson 001 local certificate',
EXPIRY_DATE='9999-12-31'

EXEC xp_create_subdir 'C:\software\cerficates'

BACKUP CERTIFICATE cer_alwayson_001
TO FILE='C:\software\cerficates\cer_alwayson_001.cer'

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cer_alwayson_001, 
ENCRYPTION = REQUIRED ALGORITHM AES, 
ROLE = ALL)

Step 2

CREATE LOGIN alwayson_user 
WITH PASSWORD='xxxxxx',
CHECK_POLICY=OFF

USE MASTER
GO
CREATE USER alwayson_user FOR LOGIN alwayson_user

Step 3

: create trusted certificate
/*
To do this, you need to create all the instance certificates first, and then copy them to the directories of each ECS instance and then rerun them.
For example, on Instance 001, you need to create the certificates backed up from Instance 002 and Instance 003. On Instance 002, you need to create the certificates backed up from Instance 001 and Instance 003.
Similarly, on Instance 003, you need to re-create the certificates of Instance 001 and Instance 002.
*/

CREATE CERTIFICATE cer_alwayson_002
AUTHORIZATION alwayson_user
FROM FILE='C:softwarecerficatescer_alwayson_002.cer'

CREATE CERTIFICATE cer_alwayson_003
AUTHORIZATION alwayson_user
FROM FILE='C:softwarecerficatescer_alwayson_003.cer'

--step4:
grant connection right
GRANT CONNECT ON ENDPOINT:: Endpoint_Mirroring TO alwayson_user

17. Create a Database on the Instance and Make a Full Backup

The system creates a database that is imperative for creating AG later. The Backup is necessary for copy replication because you will be unable to establish copy replication without a transaction log point.

Use the following command for the same:

CREATE DATABASE rdsystem
BACKUP DATABASE rdsystem TO DISK='C:softwarerdsystem.bak.full.first'

18. Create AG on the Primary Copy

Once the creation of the database commences, you now have to create the AG on the primary copy. We recommend beginners create AG using the UI because the wizard facilitates the creation. Then follow the prompted instructions. Below are some screenshots for your reference.

Figure 3.

You can also use the SQL commands:

CREATE AVAILABILITY GROUP [ag-aliyun0001]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [db1]
REPLICA ON N'IZBP1EHI2DOPYQA' WITH (ENDPOINT_URL = N'TCP://iZbp1ehi2dopyqA.aliyunrds.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'IZBP1EHI2DOPYQC' WITH (ENDPOINT_URL = N'TCP://iZbp1ehi2dopyqC.aliyunrds.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'iZbp1ehi2dopyqZ' WITH (ENDPOINT_URL = N'TCP://iZbp1ehi2dopyqZ.aliyunrds.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

If you need to restore the database to a secondary copy manually, backup the database and logs, and then restore the secondary copy to the "roll forward" state. Alternatively, you can share a folder and allow the wizard to complete the process. Finally, we recommend a method for creating AG. Add the SEEDING_MODE = AUTOMATIC option to each copy of the above script, specifically:

N'iZbp1ehi2dopyqZ' WITH (ENDPOINT_URL = N'TCP://iZbp1ehi2dopyqZ.aliyunrds.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50,SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
Then run the following command on each secondary copy node: 
SECONDARY ALTER AVAILABILITY GROUP [ag-aliyun0001] GRANT CREATE ANY DATABASE

19. Add AG to the Secondary Copy and Add the Database

To add AG to the secondary copy and add the database using the following command:

ALTER AVAILABILITY GROUP [ag-aliyun0001] JOIN;
ALTER DATABASE [db1] SET HADR AVAILABILITY GROUP = [ag-aliyun0001];

20. Create a Listener

The access to A created listener is not possible from external non-primary nodes, but you can use HAVIP to solve this problem. Just use the UI to create it and select Next Step directly. The screenshot below should act as a reference.

Figure 4.

At this point, an AlwaysOn availability group has completed creation. We would request you to have a look at the three figures again. You will notice that they are captured from different copies. Part of their content is different, distinguishing the primary copy and the secondary copy, as well as the synchronous and asynchronous replication relationships. Below are the three screenshots for your reference.

Figure 5.

Figure 6.

Figure 7.

Let us now proceed to create HAVIP

21. Create HAVIP

Consult the VPC/HAVIP product manager to activate the HAVIP whitelist and then associate it with the two synchronized ECS instances in the console. Note that the ECS instances must be the ones for synchronously replicated database copies. HAVIP must be consistent with the listener IP address and the listening port must be consistent with the listener port.

22. Create a Read-Only Server Load Balancer

This is very simple. Just bind the read-only ECS instance to the Server Load Balancer and specify the weight.

This concludes our list of steps involved in the deployment of a stand-alone Windows Server 2016 + SQL Server 2016 environment. We will now move towards understanding the precautions that users must follow during the entire deployment process.

Precautions to be Followed During Deployment

● There is no domain control in this solution; hence there are numerous security requirements on configuration;
● If you need to enable domain control, AlwaysOn security configuration will no longer be required;
● The solution is a beta of a productized solution, with the product scheduled for launch in the near future. The productized solution will have some permission limitations with high automation competency. It will be a PaaS service.

Conclusion:

This article primarily focuses on the deployment of SQL Server 2016 that offers dual master and multiple read replicas. The AlwaysOn group boasts of a high-availability and disaster recovery solution. We discussed the prerequisites that one needs to have in place before embarking on the deployment process. The steps involved in the deployment are a set of 22 steps that users must follow. Users must take note of the difference in distinguishing the primary copy from the secondary copy, as well as the synchronous and asynchronous replication relationships.