Implementing SQL Server AlwaysOn Availability Groups on ECS Instances


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.

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.

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:

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.

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.

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.

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.

Deployment Plan

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

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:

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:

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:

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:

8. Install Windows Failover Cluster Feature

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

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"
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 $ -ne 'loopback'}$IpAddress=((($Adapter | Get-NetIPConfiguration).IPv4Address) | Where InterfaceAlias -ne "loopback").IPAddress
$PrefixLength=((($Adapter | Get-NetIPConfiguration).IPv4Address) | Where InterfaceAlias -ne "loopback").PrefixLength
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 >> C:\Windows\System32\drivers\etc\hosts
echo >> C:\Windows\System32\drivers\etc\hosts
echo >> 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:

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.

15. Enable the Database AlwaysOn Feature

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

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.

WITH SUBJECT='alwayson 001 local certificate',
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
CREATE LOGIN alwayson_user 
CREATE USER alwayson_user FOR LOGIN 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.

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.

Then run the following command on each secondary copy node:

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:

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.

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.

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.


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.

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.