AOAG – Run SQL Server BLOG

25th April 202225th April 2022

Replace a MS SQL Database in AlwaysOn AG with DBATools

There is a database in AG and you need to drop it and restore from backup (which is sometimes a transition from developer to prod )
You don’t need any prerequisite for cluster nodes and AG names.
The script find the instance in local windows server and find the db AG nodes and listener.
So finally you need the database name and the location of the backup folder which can be an Ola backup solution folder.

$db="AdventureWorks"
$BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks"

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }
#find AG:
$AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db}
$SQLListener = Get-DbaAgListener -SqlInstance  $SqlServersOnline[0]  | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup}

# connect to listener to get primary and secondary
$availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup
$PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name
$SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name
$AvailabilityGroup = $AGGroup.AvailabilityGroup
# remove db from AG-s

Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db
Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false

# check restore script with -OutputScriptOnly 
# Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace   -MaintenanceSolution -OutputScriptOnly   
# usefull paratmeters if needed:  -RestoreTime '2022-01-26 07:15:00' -WithReplace  -OutputScriptOnly -MaintenanceSolution
Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace  -MaintenanceSolution # -OutputScriptOnly 


# check and set recovery model if needed
if ( (Get-DbaDbRecoveryModel  -SqlInstance $PrimaryReplica  -Database $db).recoverymodel -eq "Simple")   `
    {  Set-DbaDbRecoveryModel  -SqlInstance $PrimaryReplica  -Database $db  -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL } 

# add database to primary AG 
Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic

$db="AdventureWorks"

$BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks"

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }

#find AG:

$AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db}

$SQLListener = Get-DbaAgListener -SqlInstance $SqlServersOnline[0] | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup}

# connect to listener to get primary and secondary

$availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup

$PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name

$SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name

$AvailabilityGroup = $AGGroup.AvailabilityGroup

# remove db from AG-s

Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db

Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false

# check restore script with -OutputScriptOnly

# Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution -OutputScriptOnly

# usefull paratmeters if needed: -RestoreTime '2022-01-26 07:15:00' -WithReplace -OutputScriptOnly -MaintenanceSolution

Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution # -OutputScriptOnly

# check and set recovery model if needed

if ( (Get-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db).recoverymodel -eq "Simple") `

{ Set-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL }

# add database to primary AG

Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic

27th January 202210th May 2022

SQL AO AG failover in cluster group and check the synchronization status

Get info from Cluster:

Get-Cluster
Get-ClusterNode
Get-ClusterGroup
Get-ClusterResource
Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView
Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics  | Sort-Object cluster,ownernode,name | Out-GridView

Get-Cluster

Get-ClusterNode

Get-ClusterGroup

Get-ClusterResource

Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView

Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics | Sort-Object cluster,ownernode,name | Out-GridView

Failover general example, you can use Invoke-DbaAgFailover for SQL server:

# Invoke-DbaAgFailover dbatools powershell command to failover

#  Switch the all cluster group to the another node in verbose:
# do NOT do in production environment:
Get-ClusterGroup | Move-ClusterGroup -verbose


#  Switch the cluster to the particular Node:
# do NOT do in production environment:
#  Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve


# move one group example:
Move-ClusterGroup "Available Storage"
Move-ClusterGroup "Cluster Group"

# Invoke-DbaAgFailover dbatools powershell command to failover

# Switch the all cluster group to the another node in verbose:

# do NOT do in production environment:

Get-ClusterGroup | Move-ClusterGroup -verbose

# Switch the cluster to the particular Node:

# do NOT do in production environment:

# Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve

# move one group example:

Move-ClusterGroup "Available Storage"

Move-ClusterGroup "Cluster Group"

DBATools makes your life happy, you can find the SQL server instances and you can failover them.

To find all of the SQL instances in a cluster server . To Check AG before and after the failover, and failover the SQL Server AO AG :

# cluster::::::
# (Get-ClusterNode).name | Find-DbaInstance
# find SQL server instances in the cluster nodes
$SqlServers = (Get-ClusterNode).name | Find-DbaInstance
# remote cluster server example:
# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance
$SqlServers
$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }
$SqlServersOnline
if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red  -BackgroundColor Yellow}
# failover GUI
Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif
# instance ellenorzes
Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

# cluster::::::

# (Get-ClusterNode).name | Find-DbaInstance

# find SQL server instances in the cluster nodes

$SqlServers = (Get-ClusterNode).name | Find-DbaInstance

# remote cluster server example:

# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance

$SqlServers

$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }

$SqlServersOnline

if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red -BackgroundColor Yellow}

# failover GUI

Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif

# instance ellenorzes

Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

if you have to wait a lot for synchronization you can check how many servers are synchronized.

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }
do { Write-Host; Write-Host "*********************************************************************************************";`
    $Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline;     Start-Sleep -Seconds 1.0 ; `
    Start-Sleep -Seconds 0.5 ; `
    Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. "  ; `
    Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString();  `
    Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `
} `
while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }

do { Write-Host; Write-Host "*********************************************************************************************";`

$Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline; Start-Sleep -Seconds 1.0 ; `

Start-Sleep -Seconds 0.5 ; `

Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. " ; `

Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString(); `

Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `

} `

while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

9th July 2021

Find and list read and writeable user databases which are only in local instance or in primary AG group

List database names, which are in AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' 
OR ars.role_desc='SECONDARY'
)

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

OR ars.role_desc='SECONDARY'

)

Local databases , which are outside of AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

Writeable databases : primary db or only in local instance

    SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

    UNION

    SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' 
    --OR ars.role_desc='SECONDARY'

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

UNION

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

--OR ars.role_desc='SECONDARY'

17th April 201817th April 2018

AOAG Synchron Commit time

Átlagos tranzakció végrehajtási ideje:

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait'
from sys.dm_os_wait_stats where waiting_tasks_count >0
 and wait_type = 'HADR_SYNC_COMMIT'

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait'

from sys.dm_os_wait_stats where waiting_tasks_count >0

and wait_type = 'HADR_SYNC_COMMIT'

SQL szerver válasz ideje ms-ban:

wait_type	waiting_tasks_count	wait_time_ms	time_per_wait
HADR_SYNC_COMMIT	10809645	135666120	12