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 "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 $LogBreaker | Out-File "$global:File" -Append
42 " LogFile: $global:File" | Out-File "$global:File" -Append
43 " LogDate: $Date" | Out-File "$global:File" -Append
44 " Server: $ServerInstance" | Out-File "$global:File" -Append
45 " Database: $Database was truncated" | Out-File "$global:File" -Append
46 $LogBreaker | Out-File "$global:File" -Append
47}
48
49Initialize
50
51# MAIN
52# While statement is built to always run
53While ($true) {
54 $Date = (get-date).ToString("yyyy-MM-dd HH:mm:ss.fff")
55 # Generate at least one entry/day to see if I'm alive
56 If ((get-date -Format hhmmss) -eq 000000) {
57 $AddToFile = "$Date" + " It's a new day, it's a new dawn"
58 $AddToFile | Out-File "$global:File" -Append
59 }
60 $Run++
61
62 # Find AlwaysOn Primary
63 $SQLQuery=$("SELECT AGC.name, RCS.replica_server_name, ARS.role_desc, AGL.dns_name " +
64 "FROM sys.availability_groups_cluster AS AGC " +
65 "INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS " +
66 "ON RCS.group_id = AGC.group_id " +
67 "INNER JOIN sys.dm_hadr_availability_replica_states AS ARS " +
68 "ON ARS.replica_id = RCS.replica_id " +
69 "INNER JOIN sys.availability_group_listeners AS AGL " +
70 "ON AGL.group_id = ARS.group_id " +
71 "WHERE ARS.role_desc = 'PRIMARY' ;")
72 $AGResult=invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
73 $PrimaryReplica = $AGResult[1]
74
75 If ($PrimaryReplica -ne $PrimaryReplicaLastRun) {
76 # Filter first run
77 If ($PrimaryReplicaLastRun -eq $null) {
78 $AddToFile = "$Date" + " AG is $PrimaryReplica"
79 $AddToFile | Out-File "$global:File" -Append
80 $LogEntries++
81 }
82 Else {
83 # Write to log
84 $AddToFile = "$Date" + " AG changed from $PrimaryReplicaLastRun to $PrimaryReplica"
85 $AddToFile | Out-File "$global:File" -Append
86 $LogEntries++
87 }
88 }
89
90 # Write to table to see if it is available
91 $SQLQuery=$("INSERT INTO Test_AlwaysOn.dbo.Test_Table (Test_Run,AG_Primary,Test_Time) " +
92 "VALUES ('$Run','$PrimaryReplica','$Date'); " )
93 invoke-sqlcmd -query $SQLquery -serverinstance $serverinstance -database $database
94
95 # Read the last insert via $Run
96 $SQLQuery=$("SELECT Test_Run,AG_Primary,Test_Time FROM Test_AlwaysOn.dbo.Test_Table where Test_run = $Run")
97 $SelectResult=invoke-sqlcmd -query $SQLquery -serverinstance $serverinstance -database $database
98 If ($Debug) {write "SelectResult: "$SelectResult}
99 $AG_Primary = $SelectResult[1]
100 $Test_Time = $SelectResult[2].ToString("yyyy-MM-dd HH:mm:ss.fff")
101
102 # Write to log
103 If ($Test_Time -eq $Date) {
104 If ($Failed -gt 0) {
105 $AddToFile = "$Date Database was not writable between $Test_TimeLastSucceed and $Date, we tried $Failed times."
106 $AddToFile | Out-File "$global:File" -Append
107 $LogEntries++
108 $OfflineHR = $Date.Substring(11,2) - $Test_TimeLastSucceed.Substring(11,2)
109 If ($Debug) {write $OfflineHR}
110 $OfflineMin = $Date.Substring(14,2) - $Test_TimeLastSucceed.Substring(14,2)
111 If ($Debug) {write $OfflineMin}
112 $OfflineSec = $Date.Substring(17,5) - $Test_TimeLastSucceed.Substring(17,5)
113 If ($Debug) {write $OfflineSec}
114 $Offline = $OfflineHR*3600 + $OfflineMin*60 + $OfflineSec
115 If ($Debug) {write $Offline}
116 $AddToFile = "$Date Database was not writable for $Offline seconds."
117 $AddToFile | Out-File "$global:File" -Append
118 $LogEntries++
119 $Failed = 0
120 }
121 $Test_TimeLastSucceed = $Test_Time
122 $Test_Time = $null
123 }
124 Else {
125 $Failed++
126 }
127 Start-Sleep -Milliseconds $Sleep
128
129 # Check if the # entries have reached the maximum entries
130 If ($LogEntries % $MaxLogEntries -eq 0) {
131 $LogBreaker | Out-File "$global:File" -Append
132 $AddToFile = "$Date" + " Log limits have been reached. A new log file will be started."
133 $AddToFile | Out-File "$global:$File" -Append
134 Initialize
135 }
136
137 # Keep the values of the variables of the last run
138 $PrimaryReplicaLastRun = $PrimaryReplica
139}