Server Configuration

• 2x Windows Server 2016 virtual servers; Server01, Server02
• Disk Config:
  • Apps Disk on SCSI Controller 0
  • SQL DB Disk on SCSI Controller 1
  • SQL Log Disk on SCSI Controller 1
  • SQL TempDB Disk on SCSI Controller 2
• SQL 2016 with SP2 Installed using DEFAULT instance
• SQL Server service account and computer accounts granted access to backup share
• An empty database called db1 on Server01
• Latest Windows Updates applied
• File Share configured on a third server for hosting the file share witness

Prepare Cluster

Install Failover Clustering

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node Server01, Server02

Configure Failover Clustering

New-Cluster -Name SQLAAG01 -Node Server01, Server02 -StaticAddress 192.168.0.120
Set-ClusterQuorum -NodeAndFileShareMajority \\Server03\AAGFSW$

Configure SQL AlwaysOn

Set Parameters

#Parameters
$Domain = "sqldev.local"
$SQLBackup = "\\Server01\backups"
$DB = "DB01"
$Server01Object = Get-Item "SQLSERVER:\Sql\Server01\DEFAULT"
$Server02Object = Get-Item "SQLSERVER:\Sql\Server02\DEFAULT"
$Server01Nm = $Server01Object.Name
$Server02Nm = $Server02Object.Name
$Server01Instance = "DEFAULT"
$Server02Instance = "DEFAULT"
$ReplicaPort = "5022"
$FailoverMode = "Automatic"
$AvailabilityMode = "SynchronousCommit"
$EndpointURLSrv01 = "$Server01Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$EndpointURLSrv02 = "$Server02Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$AGName = "AG01"

Enable AlwaysOn

Import-Module sqlps
Enable-SqlAlwaysOn -ServerInstance Server01 -Force
Enable-SqlAlwaysOn -ServerInstance Server02 -Force

Create AlwaysOn Group

# Backup the database
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server01Nm"
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server01Nm" -BackupAction Log

# Restore the database and log on node2
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server02Nm" -NoRecovery
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server02Nm" -RestoreAction Log -NoRecovery

# Create an in-memory representation of the primary replica.
$primaryReplica = New-SqlAvailabilityReplica -Name "$Server01Nm" -EndpointURL "TCP://$EndpointURLSrv01" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server01Object.Version -AsTemplate

# Create an in-memory representation of the secondary replica.
$secondaryReplica = New-SqlAvailabilityReplica -Name "$Server02Nm" -EndpointURL "TCP://$EndpointURLSrv02" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server02Object.Version -AsTemplate

# Create the availability group
New-SqlAvailabilityGroup -Name "$AGName" -Path "SQLSERVER:\SQL\$Server01Nm\$Server01Instance" -AvailabilityReplica @($primaryReplica,$secondaryReplica) -Database "$DB"

Verify in SQL whether it’s created properly

Check the ports:

SELECT type_desc, port FROM sys.tcp_endpoints;
GO

Check the endpoints are started:

SELECT state_desc FROM sys.database_mirroring_endpoints;
GO

Start an endpoint:

ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 1433)
FOR database_mirroring (ROLE = ALL);
GO

Check permissions:

SELECT 'Metadata Check';
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
GO

Continue the Availability Group configuration

# Join the secondary replica to the availability group.
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance" -Name "$AGName"

# Join the secondary database to the availability group.
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance\AvailabilityGroups\$AGName" -Database "$DB"

This is an alternate scripting method to create an Availability Group

# Alternative way to create the group
$PrimaryServer = Get-Item "SQLSERVER:\SQL\Server01\DEFAULT"
$SecondaryServer = Get-Item "SQLSERVER:\SQL\Server02\DEFAULT"
$PrimaryReplica = New-SqlAvailabilityReplica -Name "Server01" -EndpointUrl "TCP://Server01.sqldev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "Server02" -EndpointUrl "TCP://Server02.azuredev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
New-SqlAvailabilityGroup -InputObject $PrimaryServer -Name "AG01" -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica) -Database @("DB01")

Configure Listener

New-SqlAvailabilityGroupListener -Name AG01-Listener ` -StaticIp '192.168.0.121/255.255.255.0' ` -Path SQLSERVER:\Sql\$Server01\$Server01Instance\AvailabilityGroups\$AGName