How to Install and Configure SQL Server AlwaysOn on an ECS Instance

In this tutorial, we will set up the SQL Server AlwaysOn environment and create a SQL Server AlwaysOn availability group on an Alibaba Cloud Elastic Compute Service (ECS) instance.

In this tutorial, we will show you how to prepare the SQL Server AlwaysOn environment and create a SQL Server AlwaysOn availability group on an Alibaba Cloud Elastic Compute Service (ECS) instance.

We recommend you use Windows Server Failover Clustering (WSFC) and SQL Server AlwaysOn Availability Groups as your SQL Server high availability solution on Alibaba Cloud's 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.

The SQL Server AlwaysOn feature was made available in the SQL Server 2012 release. The AlwaysOn availability group boasts a high-availability 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, following the 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.

An Alibaba Cloud ECS Instance provides fast memory and the latest Intel CPUs to help you to power your cloud applications and achieve faster results with low latency. All ECS instances come with Anti-DDoS protection to safeguard your data and applications from DDoS and Trojan attacks.

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, that 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.

We recommend the following hardware to deploy this solution:

ECS: 4-core CPU or above with 16GB of memory, or above.
Mount SSD cloud disks as ECS.

We also recommend 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
SQL Server 2016 64-bit Enterprise Edition
ECS based on VPC
High-performance SSD cloud disks

For the purposes of this tutorial, we will assume a basic understanding of Alibaba Cloud's suite of products and services, the Alibaba Cloud Console, failover clustering, the Active Directory (AD), and the administration of Windows Server.

1.The Architecture

We will assume you have set up Windows Server Failover Clustering (WSFC) on Alibaba Cloud using the following configuration, which contains three servers and runs across the Alibaba Cloud Virtual Private Cloud (VPC) to provide an isolated cloud network to operate your resource in a secure environment:

WSFC-1: A primary ECS instance running Windows Server 2016.
WSFC-2: A secondary ECS instance, configured to match the primary instance, running in another Availability Zone.
AD-1: An Active Directory (AD) / domain name server (DNS) instance. This server will serve several roles:
1. Providing a Windows domain.
2. Resolving hostnames to IP addresses.
3. Hosting the file share witness that acts as a third "vote" to achieve the required quorum for the cluster.
HAVIP-1 and HAVIP-2: two HAVIPs, one in each availability zone, binded to the corresponding instance to that subnet behind the HAVIP.

Note: the quorum is sometimes referred to as the Disk or File Witness. It is simply a small clustered disk which is in the available cluster storage group.

2.Prepare Your SQL Server AlwaysOn Environment

The SQL Server AlwaysOn cluster is equivalent to DataGuard in Oracle. The master copy is readable and writable. The backup copy is read-only, and the data is stored in local disk. The data can be synchronized in real time.

2.1 Install SQL Server Management Studio

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).

2.2 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"

2.3 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

2.4 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

2.5 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.

Remember to check the DNS configuration and adjust it as necessary. Modify the script on your own or adjust it manually. You can do that 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

2.6 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

2.7 Create Windows Cluster

The next step involves creating the Windows Cluster. The latest version of Windows Server 2016 supports cluster creation through the 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

2.8 Set Arbitration Mechanism for the 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

2.9 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

2.10 Enable the Database AlwaysOn Feature

From SQL Server, enable the database AlwaysOn feature by ensuring the following checkbox is ticked:

2.11 Configure the 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.

Instances must authenticate each other to communicate. For example, if 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.
On Instance 003: you need to re-create the certificates of Instance 001 and Instance 002.

Below are three steps of SQL statements to enable these security settings:

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 a 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. Here are some example commands:

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'
GRANT CONNECT ON ENDPOINT:: Endpoint_Mirroring TO alwayson_user

2.12 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'

3.Create a SQL Server AlwaysOn Availability Group

3.1 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.

Specify the AG name:

Choose the database:

Next, specify the Replicas by clicking on the Replicas tab:

On the Endpoints tab, check the following information is presented:

For the Backup Preferences, Listener and Read-Only Routing tabs, keep the default values.

Select Initial Data Synchronization.

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));

3.2 Add AG to the Secondary Copy and Add the Database

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];

If you have not done in the Secondary database restore, then select Full Database and log backup.

Then, complete the AG creation:

3.3 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. The screenshot below should act as a reference.

At this point, an AlwaysOn availability group has completed creation. We would advise that you to have a look at the previous 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.

3.4 Check the ECS and HAVIP Configurations

Check the ECS instances are ones for synchronously replicated database copies.

Also, check that your HAVIPs are consistent with the listener IP address and the listening port is consistent with the listener port.

Conclusion

This concludes the steps involved in the deployment of a stand-alone Windows Server 2016 + SQL Server 2016 environment.

You should now have installed and configured the SQL Server AlwaysOn service on an ECS Instance.

For more information and help, please see the Microsoft SQL Server on Alibaba Cloud Best Practices, which are available here: https://www.alibabacloud.com/help/doc-detail/68959.htm

To read other tutorials covering Windows Server Failover Clusters, SQL Servers and Windows Server Failover Clustering, visit: https://www.alibabacloud.com/getting-started/projects

Community