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

  1. ECS: 4-core CPU or above with 16GB of memory, or above.
  2. Mount SSD cloud disks as ECS.
  1. .NET Framework 4.0 or above
  2. Powershell 5.0 or above
  3. Windows Server 2016 64-bit Data Center Edition
  4. SQL Server 2016 64-bit Enterprise Edition
  5. ECS based on VPC
  6. High-performance SSD cloud disks

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:

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

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.

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.

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

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.

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.

  1. On Instance 001: you need to create the certificates backed up from Instance 002 and Instance 003.
  2. On Instance 002: you need to create the certificates backed up from Instance 001 and Instance 003.
  3. On Instance 003: you need to re-create the certificates of Instance 001 and Instance 002.
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)
CREATE LOGIN alwayson_user 
WITH PASSWORD='xxxxxx',
CHECK_POLICY=OFF
USE MASTER
GO
CREATE USER alwayson_user FOR LOGIN alwayson_user
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.

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.

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

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.

3.4 Check the ECS and HAVIP Configurations

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

Conclusion

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com