Microsoft SQL AlwaysOn Unabridged Notes

# Adding Prerequisite Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
    Install-WindowsFeature Failover-Clustering | out-null;
    Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
    Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
    Import-Module FailoverClusters | out-null;
    }

# Find Listener Name

$listener=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].Value

$listenerAlias=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].ClusterObject.Name

# Set Listeners to register DNS with only its active IP and with shorter TTL

Get-ClusterResource $listenerAlias | Set-ClusterParameter RegisterAllProvidersIP 0  

Get-ClusterResource $listenerAlias | Set-ClusterParameter HostRecordTTL 300 

Stop-ClusterResource $listenerAlias

Start-ClusterResource $listenerAlias

# Verify changes

Get-ClusterResource $listenerAlias | Get-ClusterParameter HostRecordTTL, RegisterAllProvidersIP

# Failover: set current SQL server as the Primary Replica after suspending all databases. Resume replication afterward.

Import-Module -Name SqlServer
Import-Module "sqlps" -DisableNameChecking

$cluster=(get-clusterresource).Name[3]

#$databases=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases\).Name    #This method only obtain the names

$databases= Get-ChildItem  "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases"    #This method obtains the database metadata objects

#$databases | foreach {Suspend-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases\$_}

$databases |  Suspend-SqlAvailabilityDatabase

Switch-SqlAvailabilityGroup -Path SQLSERVER:\sql\$env:computername\default\availabilitygroups\$cluster -AllowDataLoss

#$databases | foreach {Resume-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases\$_}

$databases |  Resume-SqlAvailabilityDatabase

# SQL Method

ALTER AVAILABILITY GROUP [AvailabilityGroupName] FORCE_FAILOVER_ALLOW_DATA_LOSS;

# Update DNS with the current Live IP of AlwaysOn Listener

Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource

# Check DNS Record to verify the correct Active IP

(Resolve-DnsName $listener).IPAddress

# Force DNS Replication (SysAdmin)

repadmin /syncall /Aed

# Discover Endpoints

SELECT name,protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING'

# Show AlwaysOn Endpoints and Statuses

select r.replica_server_name, r.endpoint_url,

rs.connected_state_desc, rs.last_connect_error_description,

rs.last_connect_error_number, rs.last_connect_error_timestamp

from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r

on rs.replica_id=r.replica_id

where rs.is_local=1

Error

The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process. (Microsoft SQL Server, Error: 9692)

# Check listening ports within MSSQL

SELECT * FROM sys.dm_tcp_listener_states WHERE port='XXXX'

# Troubleshooting use only: Manually Create Endpoint

CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = XXXX, LISTENER_IP = (x.x.x.x)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

# Troubleshooting Only: Manually restore database

RESTORE DATABASE TEST
   FROM DISK = 'H:\Backup\TEST.bak'
   WITH REPLACE,RECOVERY

# Windows CMD method to find the process listening on port XXXX
netstat -aon | findstr :XXXX

# Discover process name of found pid
tasklist /fi "pid eq PID_NUMBER" #result: cvd.exe => CommVault

# Kill the process
taskkill /pid PID_NUMBER /F

# PowerShell Method to kill process (one liner)
Stop-Process -Id (Get-NetTCPConnection -LocalPort XXXX).OwningProcess -Force

# Restart MSSQL
net stop MSSQLSERVER
net start MSSQLSERVER

# Error:
The WSFC cluster could not bring the Network Name resource with DNS name ' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible.

IP: 192.166.16.22
IP: 192.166.9.24
EDMS_STG_AG_192.166.9.25
192.166.16.23
192.166.9.25
File Share Witness \\FILESERVER01\CLUSTER02
HQ Network 1040 192.166.16.0/24
ZONE2 network 9: 192.166.9.0/24

# Connection to each node to clear cluster settings
Clear-Clusternode -Force

# Build Cluster
$clusterName="CLUSTER02"
$nodes="SQL01","SQL02"
$clusterIPs="192.166.16.22","192.166.9.24"

New-Cluster -Name $clusterName -Node $nodes -NoStorage -StaticAddress $clusterIPs

Error: An attempt to use the specified cluster name failed because an enabled computer object with the given name already exists in the domain

Searched AD to find no record of old clustername - proceeded to cleanup traces of DNS entries for CLUSTER02 in association with 192.166.16.22,192.166.9.24

 Move-ClusterGroup “Cluster Group” -node SQL01

EDMS_STG_AG
Listener: CLUSTER02, port 1433, IPs: 192.166.16.23, 192.166.9.26
Added KIMCONNECT\svc_cluster02 into NTFS Permissions of \\FILESERVER01\CLUSTER02\backup
File Share Witness \\FILESERVER01\CLUSTER02
HQ Network: 192.166.16.0/24
ZONE2 network : 192.166.9.0/24

netstat -a | find "8225"
netstat -a -b
[macmnsvc.exe] TCP    0.0.0.0:8225         SQL02:0           LISTENING
[svchost.exe] TCP    192.35.16.21:8225       SQL01:58308       ESTABLISHED
[sqlservr.exe] TCP    192.35.16.21:8225       sql01:58329       ESTABLISHED
[sqlservr.exe] TCP    192.35.16.21:8225       sql01:58381       ESTABLISHED
[sqlservr.exe] TCP    192.35.16.21:8225       sql01:58393       ESTABLISHED
[macmnsvc.exe] TCP    [::]:8225              SQL02:0           LISTENING

netstat -p tcp -aon
netstat -aon | find /I "5022"
    TCP    0.0.0.0:5022           0.0.0.0:0              LISTENING       1292

TITLE: Microsoft SQL Server Management Studio
------------------------------
Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
------------------------------
ADDITIONAL INFORMATION:
Failed to join the database 'TEST' to the availability group 'STG_AG' on the availability replica 'SQL01, 8225'. (Microsoft.SqlServer.Smo)
For help, click: https://www.microsoft.com/?ProdName=Microsoft+SQL+Server&ProdVer=11.0.7001.0+((SQL11_PCU_Main).170815-1011+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)
For help, click: https://www.microsoft.com/?ProdName=Microsoft+SQL+Server&ProdVer=11.00.7462&EvtSrc=MSSQLServer&EvtID=35250

SQL01  TCP://SQL01.kimconnect.com:5022   DISCONNECTED                The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process.            0              2019-02-27 00:59:36.917