Powershell Test AlwaysOn
This script checks which node is the primary in the SQL AlwaysOn cluster. A test database has been added to see if that database is writable. All actions are logged to a log file.
:warning: This script is not to be run in a production environment.
The purpose of this script is to see if the SQL AlwaysOn Availability Group has done a failover. It also checks if a database within this AG is writable and if not how long it has been offline.
Info
The part to check which node is the primary has been found on a blog, which I would have given the credits if I remembered where I found it.
1<#
2 .Synopsis
3 Monitors an SQL AlwaysOn cluster and tries to write to a database on this AlwaysOn cluster.
4
5 .Description
6 Monitors an SQL AlwaysOn cluster and tries to write to a database on this AlwaysOn cluster.
7
8 .Example
9 # No Examples available
10
11 # Import SQL module
12 Import-Module sqlps
13#>
14
15# Connection parameters
16$ServerInstance="database_server" # if you are running SQL on a custom port it should be "database_server,port"
17$Database="TEST_AlwaysOn"
18
19# Logging
20$FileName="PrimaryLOG"
21$LogDir="E:\SQL_script"
22$LogBreaker="##########################"
23$global:File=""
24$MaxLogEntries = 1000
25$LogEntries = 1
26$Failed = 0
27$Sleep = 900
28
29Function Initialize {
30 # Logging parameters
31 $LogFileDate = Get-Date -Format FileDateTime
32 $global:File = $LogDir + "\" + $FileName + "_" + $LogFileDate + ".log"
33 If ($Debug) {Write-Host "The filename is: $global:File"}
34
35 # Initialization (empty table)
36 $SQLQuery=$("TRUNCATE TABLE Test_AlwaysOn.dbo.Test_Table")
37 invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
38 #Start-Sleep -Seconds 3
39
40 # Initialize log
41 $Date = (get-date).ToString("yyyy-MM-dd HH:mm:ss.fff")
42 $LogBreaker | Out-File "$global:File" -Append
43 " LogFile: $global:File" | Out-File "$global:File" -Append
44 " LogDate: $Date" | Out-File "$global:File" -Append
45 " Server: $ServerInstance" | Out-File "$global:File" -Append
46 " Database: $Database was truncated" | Out-File "$global:File" -Append
47 $LogBreaker | Out-File "$global:File" -Append
48}
49
50Initialize
51
52# MAIN
53# While statement is built to always run
54While ($true) {
55 $Date = (get-date).ToString("yyyy-MM-dd HH:mm:ss.fff")
56 # Generate at least one entry/day to see if I'm alive
57 If ((get-date -Format HHmmss) -eq "000000") {
58 $AddToFile = "$Date" + " It's a new day, it's a new dawn"
59 $AddToFile | Out-File "$global:File" -Append
60 }
61 $Run++
62
63 # Find AlwaysOn Primary
64 $SQLQuery=$("SELECT AGC.name, RCS.replica_server_name, ARS.role_desc, AGL.dns_name " +
65 "FROM sys.availability_groups_cluster AS AGC " +
66 "INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS " +
67 "ON RCS.group_id = AGC.group_id " +
68 "INNER JOIN sys.dm_hadr_availability_replica_states AS ARS " +
69 "ON ARS.replica_id = RCS.replica_id " +
70 "INNER JOIN sys.availability_group_listeners AS AGL " +
71 "ON AGL.group_id = ARS.group_id " +
72 "WHERE ARS.role_desc = 'PRIMARY' ;")
73 $AGResult=invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
74 $PrimaryReplica = $AGResult[1]
75
76 If ($PrimaryReplica -ne $PrimaryReplicaLastRun) {
77 # Filter first run
78 If ($PrimaryReplicaLastRun -eq $null) {
79 $AddToFile = "$Date" + " AG is $PrimaryReplica"
80 $AddToFile | Out-File "$global:File" -Append
81 $LogEntries++
82 }
83 Else {
84 # Write to log
85 $AddToFile = "$Date" + " AG changed from $PrimaryReplicaLastRun to $PrimaryReplica"
86 $AddToFile | Out-File "$global:File" -Append
87 $LogEntries++
88 }
89 }
90
91 # Write to table to see if it is available
92 $SQLQuery=$("INSERT INTO Test_AlwaysOn.dbo.Test_Table (Test_Run,AG_Primary,Test_Time) " +
93 "VALUES ('$Run','$PrimaryReplica','$Date'); " )
94 invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
95
96 # Read the last insert via $Run
97 $SQLQuery=$("SELECT Test_Run,AG_Primary,Test_Time FROM Test_AlwaysOn.dbo.Test_Table where Test_run = $Run")
98 $SelectResult=invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
99 If ($Debug) {Write-Host "SelectResult: $SelectResult"}
100 $AG_Primary = $SelectResult[1]
101 $Test_Time = $SelectResult[2].ToString("yyyy-MM-dd HH:mm:ss.fff")
102
103 # Write to log
104 If ($Test_Time -eq $Date) {
105 If ($Failed -gt 0) {
106 $AddToFile = "$Date Database was not writable between $Test_TimeLastSucceed and $Date, we tried $Failed times."
107 $AddToFile | Out-File "$global:File" -Append
108 $LogEntries++
109 $Offline = ([DateTime]::Parse($Date) - [DateTime]::Parse($Test_TimeLastSucceed)).TotalSeconds
110 $AddToFile = "$Date Database was not writable for $Offline seconds."
111 $AddToFile | Out-File "$global:File" -Append
112 $LogEntries++
113 $Failed = 0
114 }
115 $Test_TimeLastSucceed = $Test_Time
116 $Test_Time = $null
117 }
118 Else {
119 $Failed++
120 }
121 Start-Sleep -Milliseconds $Sleep
122
123 # Check if the # entries have reached the maximum entries
124 If ($LogEntries % $MaxLogEntries -eq 0) {
125 $LogBreaker | Out-File "$global:File" -Append
126 $AddToFile = "$Date" + " Log limits have been reached. A new log file will be started."
127 $AddToFile | Out-File "$global:File" -Append
128 Initialize
129 }
130
131 # Keep the values of the variables of the last run
132 $PrimaryReplicaLastRun = $PrimaryReplica
133}