Configure SQL Always On Availability Group in Azure VM manually | Part 3
Create a SQL Server Always On Availability Group on Azure Virtual Machines. The video series creates an Availability Group with a database replica on two SQL Servers.
SQL Always ON
=================
Join the servers to the domain
Add the Lab\Install user as an administrator on each cluster VM
Set the SQL Server service accounts
Create a sign-in on each SQL Server VM for the installation account
Add Failover Clustering features to both SQL Server VMs
Configure the firewall on each SQL Server VM
Configure system account permissions
Create an account for [NT AUTHORITY\SYSTEM] on each SQL Server instance. The following script creates this account:
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
Grant the following permissions to [NT AUTHORITY\SYSTEM] on each SQL Server instance:
ALTER ANY AVAILABILITY GROUP
CONNECT SQL
VIEW SERVER STATE
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
Create the cluster
Add the other SQL Server to cluster
Add a cluster quorum cloud witness
Enable Availability Groups
Create a database on the first SQL Server
Create a backup share
Take a full backup of the database
Create the Availability Group
Check the Availability Group
Create an Azure load balancer
Add backend pool for the availability group listener
Set the probe
Name : SQLAlwaysOnEndPointProbe
Protocol : TCP
Port: 59999
Interval: 5
Unhealthy threshold: 2
Set the load balancing rules
Name SQLAlwaysOnEndPointListener
Protocol TCP
Port 1433
Backend Port 1433
Probe SQLAlwaysOnEndPointProbe
Session Persistence None
Idle Timeout 4
Floating IP Enabled
Add the cluster core IP address for the Windows Server Failover Cluster (WSFC)
Set Probe
Name WSFCEndPointProbe
Protocol TCP
Port 58888
Interval 5
Unhealthy threshold 2
Rule
Name WSFCEndPoint
Protocol TCP
Port 58888
Backend Port 58888
Probe WSFCEndPointProbe
Session Persistence None
Idle Timeout 4
Floating IP Enabled
Configure the listener
Get the name of the cluster network resource.
Add the client access point.
Take the availability group cluster role offline
Configure the IP resource for the availability group.
Make the SQL Server availability group resource dependent on the client access point
Make the client access point resource dependent on the IP address
Set the cluster parameters in PowerShell :
$ClusterNetworkName = "Cluster Network 1"
$IPResourceName = "AGIP"
$ListenerILBIP = "10.10.0.9"
[int]$ListenerProbePort = 59999
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
Bring the availability group cluster role online
Get the IP address name of the WSFC Cluster IP address
Right-click IP Address, and select Properties.
Copy the Name of the IP address. It may be Cluster IP Address.
Set the cluster parameters in PowerShell.
$ClusterNetworkName = "Cluster Network 1"
$IPResourceName = "Cluster IP Address"
$ClusterCoreIP = "10.10.0.8"
[int]$ClusterProbePort = 58888
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ClusterCoreIP";"ProbePort"=$ClusterProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
Set listener port
Test connection to listener