Implementing SQL Server AlwaysOn Availability Groups on ECS Instances

Image for post
Image for post

Introduction

Alibaba Cloud ECS Instances

1. Prerequisites

1.1 Hardware Conditions:

1.2 Software conditions:

Architecture Diagram

Image for post
Image for post

Overview of Core Components

1. Architecture Overview

2. Virtual Private Cloud (VPC)

3. Highly Available (HA)

4. Server Load Balancer

5. Elastic IP Address (EIP)

6. Elastic Compute Service (ECS)

7. Windows Cluster

8. AlwaysOn Availability Group

Deployment Plan

1. Prerequisites

1.1 Hardware Conditions:

1.2 Software conditions:

1.3 Network requirements

1.4 Demo Environment

2. Prepare Environment for Deployment

3. Modify the Host Name

4. Install SQL Server Instances

5. Install SQL Server Management Studio

6. Create a Unified Windows Account

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

8. Install Windows Failover Cluster Feature

9. Modify the DNS Suffix of a Windows Host

$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

$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

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

13. Set Arbitration Mechanism for 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

15. Enable the Database AlwaysOn Feature

Image for post
Image for post

16. Configure AlwaysOn Security Settings

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

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

18. Create AG on the Primary Copy

Image for post
Image for post
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));
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

20. Create a Listener

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

21. Create HAVIP

22. Create a Read-Only Server Load Balancer

Precautions to be Followed During Deployment

Conclusion:

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

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