· 7 years ago · Oct 08, 2018, 02:00 AM
1<#
2
3 # This script is intended to failover the AGs or get passive nodes of primary DC of AGs
4 # failover time and failover errors will be logged into Monitoring.dbo.tblFailoverLogs
5 # failover errors also logged to "CurrentPath\logs\"
6 # Please provide 2 parameters for script to execute(Param1:getpassive/dryrun/failover Param2:text file of AGNames)
7
8Instructions
9â¦Script requires 2 parameters
10Param 1: getPassive or Failover or Dryrun
11-getPassive will get all passive nodes of primary DC of AG(s)
12-Dryrun won’t do any failover, it just run the script with the AGs given to show the user how the script actually runs.
13-Failover will do the actual failover of the AGs given.
14Param 3: AGList
15â¦Failover time and failover errors will be logged into Monitoring.dbo.tblFailoverLogs. Failover errors are also logged to "CurrentPath\logs\"
16
17#>
18
19param([string]$run,[string]$AGlist)
20
21Set-ExecutionPolicy Unrestricted
22
23$MetadataServer = "ServerName"
24$dtToday = get-date
25$dtThisHour = '{0:HH}' -f $dtToday
26$dtToday = '{0:yyyy-MM-dd}' -f $dtToday
27$dtTodayFilter = "*" + $dttoday +" " + $dtThisHour + "*"
28$fullPathIncFileName = $MyInvocation.MyCommand.Definition
29$currentScriptName = $MyInvocation.MyCommand.Name
30$strBaseDirectory = $fullPathIncFileName.Replace($currentScriptName, "")
31
32If(!$run){ throw "Input Run:" }
33If(!$AGlist){ throw "Input AGlist:" }
34
35If($run -ne "getpassive" -and $run -ne "dryrun" -and $run -ne "failover")
36{
37 throw "Run type is required.Please specify the Runtype (getpassive/dryrun/failover)"
38}
39
40If (-not (Test-Path "$AGlist"))
41{
42 throw "Error: Cannot find AG list file: $AGlist"
43}
44
45#Check if the Logs Directory exists in the base directory if not create it
46if ((Test-Path -path $strBaseDirectory\Logs\) -ne $True)
47{
48 New-Item $strBaseDirectory\Logs\ -type directory
49}
50
51$strRunlog = $strBaseDirectory + "\Logs\" + $dtToday + "-"+ $dtThisHour + "-2Node-PatchingFailoverRun.log"
52$strErrlog = $strBaseDirectory + "\Logs\" + $dtToday + "-"+ $dtThisHour + "-2Node-PatchingFailoverError.log"
53
54Function Addto-log($strRuntxt)
55{
56 $strRunDate = get-date #-format 'yyyy-MM-dd hh:mm:ss'
57 $strRundate = '{0:yyyy-MM-dd HH:mm:ss}' -f $strRundate
58
59 write-host $strRunDate ":" $strRuntxt -ForegroundColor Green
60 $strFileRuntxt = "$strRundate : $strRuntxt"
61 Add-content $strRunlog "$strFileRuntxt"
62}
63
64Function Addto-Warning($strWarningtxt)
65{
66 $strWarningDate = get-date #-format 'yyyy-MM-dd hh:mm:ss'
67 $strWarningdate = '{0:yyyy-MM-dd HH:mm:ss}' -f $strWarningdate
68
69 write-host $strWarningDate ":" $strWarningtxt -ForegroundColor Yellow
70 $strFileWarningtxt = "$strWarningdate : $strWarningtxt"
71 Add-content $strRunlog "$strFileWarningtxt"
72}
73
74Function Addto-ErrLog($strErrtxt)
75{
76 $strErrDate = get-date #-format 'yyyy-MM-dd hh:mm:ss'
77 $strErrDate = '{0:yyyy-MM-dd HH:mm:ss}' -f $strErrDate
78
79 write-host $strErrDate ":" $strErrtxt -ForegroundColor Red
80 $strFileErrtxt += "$strErrdate : $strErrtxt"
81 Add-content $strErrlog "$strFileErrtxt"
82 Add-content $strRunlog "$strFileErrtxt"
83}
84
85TRY
86{
87
88$qryFailoverID = " select max(FailoverID) + 1 as ID from [Monitoring].[dbo].[tblFailoverLogs] (nolock) "
89$getFailoverID = Invoke-Sqlcmd -Query $qryFailoverID -ServerInstance $MetadataServer
90$fID = $getFailoverID.ID
91
92###################################################################################
93# Failover Script
94###################################################################################
95
96$FailoverScript =
97{
98 param([Int]$fID,[String]$AG,[String]$FromServer,[String]$ToServer,[string]$run,[string]$MetadataServer)
99
100 TRY
101 {
102 $ErrorActionPreference = "Stop";
103 $dtStartEnd = Get-Date
104
105 If($run -eq "dryrun"){$FailoverStatus = "dryrun"}
106 If($run -eq "failover"){$FailoverStatus = "FailoverStarted"}
107
108 $qryFailoverStart =
109 "
110 SET IDENTITY_INSERT [Monitoring].[dbo].[tblFailoverLogs] ON
111 INSERT INTO [Monitoring].[dbo].[tblFailoverLogs] (FailoverID,AG,FromServer,ToServer,FailoverStartedTime,FailoverEndedTime,FailoverStatus,Comments) VALUES ($fID,'$AG','$FromServer','$ToServer','$dtStartEnd','$dtStartEnd','$FailoverStatus','NA')
112 SET IDENTITY_INSERT [Monitoring].[dbo].[tblFailoverLogs] OFF
113 "
114 $rsFailoverID = Invoke-Sqlcmd -Query $qryFailoverStart -ServerInstance $MetadataServer
115
116 If($run -eq "failover")
117 {
118 $Failoverquery = "ALTER AVAILABILITY GROUP $AG FAILOVER"
119 Invoke-Sqlcmd -Query $Failoverquery -ServerInstance $ToServer
120 }
121
122 If($run -eq "dryrun"){$FailoverStatus = "dryrun"}
123 If($run -eq "failover"){$FailoverStatus = "FailoverCompleted"}
124
125 $dtStartEnd = Get-Date
126 $qryFailoverEnd = "UPDATE [Monitoring].[dbo].[tblFailoverLogs] SET FailoverEndedTime = '$dtStartEnd', FailoverStatus = '$FailoverStatus' WHERE AG = '$AG' AND FailoverID = $fID"
127 Invoke-Sqlcmd -Query $qryFailoverEnd -ServerInstance $MetadataServer
128 }
129 CATCH
130 {
131 $dtStartEnd = Get-Date
132
133 If($run -eq "dryrun"){$FailoverStatus = "dryrun"}
134 If($run -eq "failover"){$FailoverStatus = "FailoverFailed"}
135
136 $qryFailoverFailed = "UPDATE [Monitoring].[dbo].[tblFailoverLogs] SET FailoverEndedTime = '$dtStartEnd', FailoverStatus = '$FailoverStatus', Comments = '$_.Exception.Message' WHERE AG = '$AG' AND FailoverID = $fID"
137 Invoke-Sqlcmd -Query $qryFailoverFailed -ServerInstance $MetadataServer
138
139 Addto-ErrLog "Error while failover $AG from $FromServer to $ToServer -- $AG $($_.Exception.Message)"
140 }
141 FINALLY
142 {
143 $ErrorActionPreference = "CONTINUE";
144 }
145}
146
147###################################################################################
148# getPassiveNode Script
149###################################################################################
150
151Function getPassiveNodes()
152{
153try
154{
155 $ErrorActionPreference = "Stop";
156
157 $AGs = Get-Content "$AGlist"
158
159 $l = 0
160
161 Addto-Warning "Getting Passive node for Primary DC..."
162
163 $out =@()
164 ForEach ($AGlistener in $AGs)
165 {
166 $FromToServerquery = "select FromServer,TOServer from [Monitoring].[dbo].[tblFailoverServers](nolock) where AG = '$AGlistener' "
167 $getFromToServer = Invoke-Sqlcmd -Query $FromToServerquery -ServerInstance $MetadataServer
168 $FromServer = $getFromToServer.FromServer
169 $ToServer = $getFromToServer.TOServer
170 $DC = $FromServer.Substring(0,3)
171
172 $con = new-object system.data.sqlclient.sqlconnection
173 $cmd1 = new-object system.data.sqlclient.sqlcommand
174 $dt1 = new-object system.data.datatable
175
176 $con.connectionstring = "Data Source=$AGlistener;Integrated Security=sspi;Initial Catalog=master;Connect Timeout=60;MultiSubnetFailover=true"
177 $con.open()
178
179 $AGNameandSecondaryServerquery =
180 "
181 SELECT DISTINCT AGC.name, RCS.replica_server_name
182 FROM sys.availability_groups_cluster AS AGC
183 INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
184 ON RCS.group_id = AGC.group_id
185 INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
186 ON ARS.replica_id = RCS.replica_id
187 INNER JOIN sys.availability_group_listeners AS AGL
188 ON AGL.group_id = ARS.group_id
189 WHERE ARS.role_desc = 'SECONDARY'
190 AND AGL.dns_name = '$AGlistener'
191 AND RCS.replica_server_name like '$DC%'
192 "
193
194 $cmd1.commandtext = $AGNameandSecondaryServerquery
195 $cmd1.connection = $con
196 $dr1 = $cmd1.ExecuteReader()
197 $dt1.Load($dr1)
198
199 Foreach($item in $dt1)
200 {
201 $AG = $item.name
202 $PassiveServer = $item.replica_server_name
203 $obj = New-Object PSObject
204 $obj | Add-Member -Membertype NoteProperty -Name AG -Value $AG
205 $obj | Add-Member -Membertype NoteProperty -Name PassiveServer -Value $PassiveServer
206 $out += $obj
207 }
208 }
209 #$out | Format-Table
210 write-host "AGListener`t`tPassiveServer"
211 write-host "----------`t`t-------------"
212 Foreach($R in $out)
213 {
214 $RAG = $R.AG
215 $RPassiveServer = $R.PassiveServer
216 write-host $RAG"`t`t"$RPassiveServer -ForegroundColor Green
217 }
218
219 $l++
220 Write-Progress -activity "Getting Passive Nodes is in progress..." -status "Scanned: $l of $($AGs.Count) AGs" -percentComplete (($l / $AGs.Count) * 100)
221}
222catch
223{
224 $ErrorMessage = $_.Exception.Message
225 $line = $_.InvocationInfo.ScriptLineNumber
226 Addto-ErrLog "getPassiveScript: AGListener--$AGlistener : $ErrorMessage Error Occurred on line $line"
227}
228FINALLY
229{
230 $ErrorActionPreference = "CONTINUE";
231}
232
233}
234
235###################################################################################
236# Post Failover Verification Script
237###################################################################################
238
239Function PostFailover($AGs)
240{
241 Addto-Warning "Post failover verification is in progress..."
242
243 $MAGs = 0
244 $FOIP = 0
245 $l = 0
246
247 $out3 =@()
248 ForEach ($AGlistener in $AGs)
249 {
250 $e = 0
251 $Health = $null
252
253 $FromToServerquery = "select FromServer,TOServer from [Monitoring].[dbo].[tblFailoverServers](nolock) where AG = '$AGlistener' "
254 $getFromToServer = Invoke-Sqlcmd -Query $FromToServerquery -ServerInstance $MetadataServer
255 $FromServer = $getFromToServer.FromServer
256 $ToServer = $getFromToServer.TOServer
257 $DC = $FromServer.Substring(0,3)
258
259 Try
260 {
261 $ErrorActionPreference = "Stop";
262
263 $columbexistsqry =
264 "
265 IF EXISTS (select * from [Monitoring].[dbo].[tblFailoverLogs] where AG = '$AGlistener' and FailoverID = $fID)
266 select 1 as R
267 ELSE
268 select 0 as R
269 "
270
271 $getcolumbexists = Invoke-Sqlcmd -Query $columbexistsqry -ServerInstance $MetadataServer
272 $columnexists = $getcolumbexists.R
273
274 If($columnexists -eq 1)
275 {
276 $verificationstartqry = "UPDATE [Monitoring].[dbo].[tblFailoverLogs] SET FailoverStatus = 'PostFailoverVerification' WHERE AG = '$AGlistener' AND FailoverID = $fID"
277 Invoke-Sqlcmd -Query $verificationstartqry -ServerInstance $MetadataServer
278
279 $con1 = new-object system.data.sqlclient.sqlconnection
280 $con2 = new-object system.data.sqlclient.sqlconnection
281 $cmd1 = new-object system.data.sqlclient.sqlcommand
282 $dt1 = new-object system.data.datatable
283 $cmd2 = new-object system.data.sqlclient.sqlcommand
284 $dt2 = new-object system.data.datatable
285
286 $con1.connectionstring = "Data Source=$AGlistener;Integrated Security=sspi;Initial Catalog=master;Connect Timeout=100;MultiSubnetFailover=true"
287 $con1.open()
288
289 $PrimaryServerquery =
290 "
291 SELECT DISTINCT RCS.replica_server_name
292 FROM sys.availability_groups_cluster AS AGC
293 INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
294 ON RCS.group_id = AGC.group_id
295 INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
296 ON ARS.replica_id = RCS.replica_id
297 INNER JOIN sys.availability_group_listeners AS AGL
298 ON AGL.group_id = ARS.group_id
299 WHERE ARS.role_desc = 'PRIMARY'
300 AND AGL.dns_name = '$AGlistener'
301 AND RCS.replica_server_name like '$DC%'
302 "
303
304 $cmd1.commandtext = $PrimaryServerquery
305 $cmd1.connection = $con1
306 $dr1 = $cmd1.ExecuteReader()
307 $dt1.Load($dr1)
308 $CurrentPrimary = $dt1.replica_server_name
309
310 $con1.close()
311
312 $con2.connectionstring = "Data Source=$AGlistener;Integrated Security=sspi;Initial Catalog=master;Connect Timeout=100;MultiSubnetFailover=true"
313 $con2.open()
314
315 $AGHealthquery =
316 "
317 SELECT DISTINCT
318 AGC.name,ADB.database_name,RCS.replica_server_name,ARS.role_desc,AR.failover_mode_desc,ARS.connected_state_desc,
319 ISNULL(HDRS.database_state_desc, 0) AS [database_state_desc],
320 ISNULL(HDRS.is_suspended, 0) AS [is_suspended],
321 ISNULL(HDRS.suspend_reason_desc, 0) AS [suspend_reason_desc],
322 ISNULL(HDBRCS.is_database_joined, 0) AS [is_database_joined]
323 FROM sys.availability_groups_cluster AS AGC
324 INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
325 ON RCS.group_id = AGC.group_id
326 INNER JOIN sys.availability_databases_cluster AS ADB
327 ON ADB.group_id = RCS.group_id
328 INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
329 ON ARS.replica_id = RCS.replica_id
330 INNER JOIN sys.availability_group_listeners AS AGL
331 ON AGL.group_id = ARS.group_id
332 INNER JOIN sys.availability_replicas AS AR
333 ON RCS.replica_id = AR.replica_id
334 INNER JOIN sys.dm_hadr_database_replica_states AS HDRS
335 ON ADB.group_database_id = HDRS.group_database_id
336 INNER JOIN sys.dm_hadr_database_replica_cluster_states AS HDBRCS
337 ON ADB.group_database_id = HDBRCS.group_database_id
338 WHERE AGL.dns_name = '$AGlistener'
339 AND HDRS.database_state_desc IS NOT NULL
340 order by ARS.role_desc, ADB.database_name,AR.failover_mode_desc
341 "
342 $cmd2.commandtext = $AGHealthquery
343 $cmd2.connection = $con2
344 $dr2 = $cmd2.ExecuteReader()
345 $dt2.Load($dr2)
346
347 ForEach ($item in $dt2)
348 {
349 $iname = $item.name
350 $idatabase_name = $item.database_name
351 $ireplica_server_name = $item.replica_server_name
352 $irole_desc = $item.role_desc
353 $ifailover_mode_desc = $item.failover_mode_desc
354 $iconnected_state_desc = $item.connected_state_desc
355 $idatabase_state_desc = $item.database_state_desc
356 $iis_suspended = $item.is_suspended
357 $isuspend_reason_desc = $item.suspend_reason_desc
358 $iis_database_joined = $item.is_database_joined
359
360 If($irole_desc -eq 'SECONDARY' -AND $idatabase_state_desc -eq 0) { $idatabase_state_desc = 'SECONDARY' }
361 If($isuspend_reason_desc -eq 0) { $isuspend_reason_desc = 'NA' }
362 If($iconnected_state_desc -ne 'CONNECTED') { $e++ }
363 If($idatabase_state_desc -ne 'ONLINE') { If($idatabase_state_desc -ne 'SECONDARY') { $e++ } }
364 If($iis_suspended -ne 0) { $e++ }
365 If($iis_database_joined -ne 1) { $e++ }
366 }
367 If($e -gt 0) { $Health = 'BAD' }
368 Else { $Health = 'GOOD' }
369
370 If($CurrentPrimary -eq $ToServer)
371 {
372 If ($Health -eq 'GOOD') { $FailoverStatus = 'FailoverSucceeded' }
373 If ($Health -eq 'BAD')
374 {
375 $FailoverStatus = 'Failover is still in progress'
376 $FOIP++
377 }
378 }
379 Else
380 {
381 If ($Health -eq 'GOOD') { $FailoverStatus = 'Failedback to original node' }
382 If ($Health -eq 'BAD') { $FailoverStatus = 'Failback is in progress' }
383 }
384 $verificationendqry = "UPDATE [Monitoring].[dbo].[tblFailoverLogs] SET FailoverStatus = '$FailoverStatus', AGHealth = '$Health' WHERE AG = '$AGlistener' AND FailoverID = $fID"
385 Invoke-Sqlcmd -Query $verificationendqry -ServerInstance $MetadataServer
386 }
387 Else
388 {
389 $FailoverStatus = 'Failover was not initiated.'
390 $Health = 'NA'
391 $qryMissedAGs =
392 "
393 SET IDENTITY_INSERT [Monitoring].[dbo].[tblFailoverLogs] ON
394 INSERT INTO [Monitoring].[dbo].[tblFailoverLogs] (FailoverID,AG,FromServer,ToServer,FailoverStartedTime,FailoverEndedTime,FailoverStatus,Comments) VALUES ($fID,'$AGlistener','$FromServer','$ToServer','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','$FailoverStatus','NA')
395 SET IDENTITY_INSERT [Monitoring].[dbo].[tblFailoverLogs] OFF
396 "
397 Invoke-Sqlcmd -Query $qryMissedAGs -ServerInstance $MetadataServer
398 $MAGs++
399 }
400
401 }
402 Catch
403 {
404 $FailoverStatus = 'AG connectivity failed, failover might be still in progress or AG down. Please check..'
405 $Health = 'NA'
406 $verificationendqry = "UPDATE [Monitoring].[dbo].[tblFailoverLogs] SET FailoverStatus = '$FailoverStatus', AGHealth = '$Health' WHERE AG = '$AGlistener' AND FailoverID = $fID"
407 Invoke-Sqlcmd -Query $verificationendqry -ServerInstance $MetadataServer
408 }
409 FINALLY
410 {
411 $ErrorActionPreference = "CONTINUE";
412 }
413
414 $obj3 = New-Object PSObject
415 $obj3 | Add-Member -Membertype NoteProperty -Name AG -Value $AGlistener
416 $obj3 | Add-Member -Membertype NoteProperty -Name FailoverStatus -Value $FailoverStatus
417 $out3 += $obj3
418
419 $l++
420 Write-Progress -activity "Post failover verification is in progress..." -status "Scanned: $l of $($AGs.Count) AGs" -percentComplete (($l / $AGs.Count) * 100)
421 }
422
423 write-host "AGListener`t`tFailoverStatus"
424 write-host "----------`t`t--------------"
425 Foreach($R in $out3)
426 {
427 $RAG = $R.AG
428 $RFailoverStatus = $R.FailoverStatus
429 If($RFailoverStatus -eq 'FailoverSucceeded') { write-host $RAG"`t`t"$RFailoverStatus -ForegroundColor Green }
430 Else { write-host $RAG"`t`t"$RFailoverStatus -ForegroundColor Red }
431 }
432 #Addto-log "Failover completed."
433 return $MAGs, $FOIP
434}
435
436###################################################################################
437# Main Script
438###################################################################################
439
440Try
441{
442 $ErrorActionPreference = "Stop";
443
444 $truncatequery1 = "truncate table [Monitoring].[dbo].[tblFailoverServers]"
445 Invoke-Sqlcmd -Query $truncatequery1 -ServerInstance $MetadataServer
446
447 $AGs = Get-Content "$AGlist"
448
449 If($run -eq "dryrun"){ Addto-Warning "Generating Metadata for Dryrun..." }
450 If($run -eq "failover"){ Addto-Warning "Generating Metadata for Failover..." }
451 If($run -eq "getpassive"){ Addto-Warning "Generating Metadata to get passive nodes..." }
452
453 $j = 0
454 ForEach ($AGlistener in $AGs)
455 {
456 $con = new-object system.data.sqlclient.sqlconnection
457 $cmd1 = new-object system.data.sqlclient.sqlcommand
458 $dt1 = new-object system.data.datatable
459
460 $con.connectionstring = "Data Source=$AGlistener;Integrated Security=sspi;Initial Catalog=master;Connect Timeout=100;MultiSubnetFailover=true"
461 $con.open()
462 $PrimaryServerquery =
463 "
464 SELECT DISTINCT RCS.replica_server_name
465 FROM sys.availability_groups_cluster AS AGC
466 INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
467 ON RCS.group_id = AGC.group_id
468 INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
469 ON ARS.replica_id = RCS.replica_id
470 INNER JOIN sys.availability_group_listeners AS AGL
471 ON AGL.group_id = ARS.group_id
472 WHERE ARS.role_desc = 'PRIMARY'
473 AND AGL.dns_name = '$AGlistener'
474 "
475 $cmd1.commandtext = $PrimaryServerquery
476 $cmd1.connection = $con
477 $dr1 = $cmd1.ExecuteReader()
478 $dt1.Load($dr1)
479 $FromServer = $dt1.replica_server_name
480 $DC = $FromServer.Substring(0,3)
481 $AGNameandSecondaryServerquery =
482 "
483 SELECT DISTINCT AGC.name, RCS.replica_server_name, AR.availability_mode_desc
484 FROM sys.availability_groups_cluster AS AGC
485 INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
486 ON RCS.group_id = AGC.group_id
487 INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
488 ON ARS.replica_id = RCS.replica_id
489 INNER JOIN sys.availability_replicas AS AR
490 ON AR.replica_id = ARS.replica_id
491 INNER JOIN sys.availability_group_listeners AS AGL
492 ON AGL.group_id = ARS.group_id
493 WHERE ARS.role_desc = 'SECONDARY'
494 AND AGL.dns_name = '$AGlistener'
495 AND RCS.replica_server_name like '$DC%'
496 "
497 $AGNameandSecondaryServerdata = Invoke-Sqlcmd -Query $AGNameandSecondaryServerquery -ServerInstance $FromServer
498
499 $AGLatencyquery =
500 "
501 SELECT ListenerName = ag.name
502 , ReplicaName = ar.replica_server_name
503 , ReplicaRole = ar_state.role_desc
504 , ReplicaLocal = CASE WHEN ar_state.is_local = 1 THEN N'Local' ELSE 'Remote' END
505 , AvailabilityMode = AR.availability_mode_desc
506 , FailoverMode = AR.failover_mode_desc
507 , ConnectedState = ar_state.connected_state_desc
508 , DatabaseName = db_name(dr_state.database_id)
509 , DatabaseState = dr_state.database_state_desc
510 , SyncHealth = dr_state.synchronization_health_desc
511 , LogSendQueueSize = isnull(dr_state.log_send_queue_size,0)
512 , LogSendRate = isnull(dr_state.log_send_rate, 0)
513 , RedoQueueSize = isnull(dr_state.redo_queue_size,0)
514 , RedoRate = isnull(dr_state.redo_rate, 0)
515 FROM sys.availability_groups AS ag
516 JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id
517 JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id
518 JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id
519 WHERE ag.name = '$AGlistener'
520 "
521 $AGLatencyData = Invoke-Sqlcmd -Query $AGLatencyquery -ServerInstance $FromServer
522 Foreach($row in $AGLatencyData)
523 {
524 $ListenerName = $row.ListenerName
525 $ReplicaName = $row.ReplicaName
526 $ReplicaRole = $row.ReplicaRole
527 $ReplicaLocal = $row.ReplicaLocal
528 $AvailabilityMode = $row.AvailabilityMode
529 $FailoverMode = $row.FailoverMode
530 $ConnectedState = $row.ConnectedState
531 $DatabaseName = $row.DatabaseName
532 $DatabaseState = $row.DatabaseState
533 $SyncHealth = $row.SyncHealth
534 $LogSendQueueSize = $row.LogSendQueueSize
535 $LogSendRate = $row.LogSendRate
536 $RedoQueueSize = $row.RedoQueueSize
537 $RedoRate = $row.RedoRate
538
539 $insaglatency = "INSERT INTO [Monitoring].[dbo].[tblFailoverAGLatency] (FailoverID,AGName,ReplicaName,ReplicaRole,ReplicaLocal,AvailabilityMode,FailoverMode,ConnectedState,DatabaseName,DatabaseState,SyncHealth,LogSendQueueSize,LogSendRate,RedoQueueSize,RedoRate) VALUES ($fID,'$ListenerName','$ReplicaName','$ReplicaRole','$ReplicaLocal','$AvailabilityMode','$FailoverMode','$ConnectedState','$DatabaseName','$DatabaseState','$SyncHealth',$LogSendQueueSize,$LogSendRate,$RedoQueueSize,$RedoRate)"
540 Invoke-Sqlcmd -Query $insaglatency -ServerInstance $MetadataServer
541 }
542
543 If( $(($AGNameandSecondaryServerdata.replica_server_name).Count) -eq 1 )
544 {
545 $AGName = $AGNameandSecondaryServerdata.name
546 $ToServer = $AGNameandSecondaryServerdata.replica_server_name
547 $ToServerFailoverMode = $AGNameandSecondaryServerdata.availability_mode_desc
548 $insphase1servers = "INSERT INTO [Monitoring].[dbo].[tblFailoverServers] (AG,FromServer,TOServer,TOServerFailoverMode) VALUES ('$AGlistener','$FromServer','$ToServer','$ToServerFailoverMode')"
549 Invoke-Sqlcmd -Query $insphase1servers -ServerInstance $MetadataServer
550 }
551 ElseIf( $(($AGNameandSecondaryServerdata.replica_server_name).Count) -eq 0 )
552 { Addto-Warning "$AGlistener : is not possible to failover because it has no secondary server in primary dc." }
553 ElseIf( $(($AGNameandSecondaryServerdata.replica_server_name).Count) -gt 1 )
554 { Addto-Warning "$AGlistener : is not possible to failover because it has $(($AGNameandSecondaryServerdata.replica_server_name).Count) secondary servers in primary dc." }
555 $con.close()
556 $j++
557 Write-Progress -activity "Generating Metadata..." -status "Scanned: $j of $($AGs.Count) AGs" -percentComplete (($j / $AGs.Count) * 100)
558 }
559
560 If($run -eq "getpassive"){ getPassiveNodes }
561
562 If($run -eq "dryrun" -or $run -eq "failover")
563 {
564
565 $skipserverscountquery = "select count(*) AS SSC from [Monitoring].[dbo].[tblFailoverServers](nolock) where AG != '' and TOServerFailoverMode = 'ASYNCHRONOUS_COMMIT'"
566 $skipserverscount = Invoke-Sqlcmd -Query $skipserverscountquery -ServerInstance $MetadataServer
567 $skipserverc = $skipserverscount.SSC
568
569 If($skipserverc -gt 0)
570 {
571 $skipserversquery = "select * from [Monitoring].[dbo].[tblFailoverServers](nolock) where AG != '' and TOServerFailoverMode = 'ASYNCHRONOUS_COMMIT'"
572 $skipservers = Invoke-Sqlcmd -Query $skipserversquery -ServerInstance $MetadataServer
573
574 $out1 =@()
575 Foreach($Row in $skipservers)
576 {
577 $RAGname = $Row.AG
578 $RFromServer = $Row.FromServer
579 $RToServer = $Row.TOServer
580 $RTOServerFailoverMode = $Row.TOServerFailoverMode
581 $obj1 = New-Object PSObject
582 $obj1 | Add-Member -Membertype NoteProperty -Name AG -Value $RAGname
583 $obj1 | Add-Member -Membertype NoteProperty -Name From -Value $RFromServer
584 $obj1 | Add-Member -Membertype NoteProperty -Name To -Value $RToServer
585 $obj1 | Add-Member -Membertype NoteProperty -Name FailoverMode -Value $RTOServerFailoverMode
586 $out1 += $obj1
587 }
588 $out1 | Format-Table
589 Addto-Warning "Above AGs will not be failedover as failovermode is ASYNCHRONOUS_COMMIT"
590 }
591
592 $failoverserversquery = "select * from [Monitoring].[dbo].[tblFailoverServers](nolock) where AG != '' and TOServerFailoverMode = 'SYNCHRONOUS_COMMIT'"
593 $failoverservers = Invoke-Sqlcmd -Query $failoverserversquery -ServerInstance $MetadataServer
594 If ( $($failoverservers.AG) -gt 0 )
595 {
596 $out2 =@()
597 Foreach($Row in $failoverservers)
598 {
599 $RAGname = $Row.AG
600 $RFromServer = $Row.FromServer
601 $RToServer = $Row.TOServer
602 $RTOServerFailoverMode = $Row.TOServerFailoverMode
603 $obj2 = New-Object PSObject
604 $obj2 | Add-Member -Membertype NoteProperty -Name AG -Value $RAGname
605 $obj2 | Add-Member -Membertype NoteProperty -Name From -Value $RFromServer
606 $obj2 | Add-Member -Membertype NoteProperty -Name To -Value $RToServer
607 $obj2 | Add-Member -Membertype NoteProperty -Name FailoverMode -Value $RTOServerFailoverMode
608 $out2 += $obj2
609 }
610 $out2 | Format-Table
611
612 If($run -eq "failover"){ $ip = Read-Host "Please enter (y/n) to failover above AGs.." }
613 If($run -eq "dryrun"){ $ip = "y" }
614
615 If(($ip -eq 'y') -or ($ip -eq 'Y'))
616 {
617 If($run -eq "dryrun"){ Addto-log "Dryrun is in progress.." }
618 If($run -eq "failover"){ Addto-log "Failover is in progress.." }
619 Foreach($row in $failoverservers)
620 {
621 $AGname = $row.AG
622 $FromServer = $row.FromServer
623 $ToServer = $row.TOServer
624 If($run -eq "dryrun"){ Addto-log "Generating Dryrun Job for $AGName" }
625 If($run -eq "failover"){ Addto-log "Generating Failover Job for $AGName" }
626
627 $j = Start-Job -scriptblock $FailoverScript -ArgumentList $fID, $AGname, $FromServer, $ToServer, $run, $MetadataServer -Name $AGName
628 $j | Format-Table Id,Name,State,PSBeginTime,Error
629 }
630 If($run -eq "dryrun"){ Addto-log "Dryrun completed." }
631 If($run -eq "failover")
632 {
633 Addto-Warning "Failover is in progress..Please wait for failover to complete..."
634
635 $time = 120 # seconds, use you actual time in here
636 Foreach($i in (1..$time))
637 {
638 $percentage = $i / $time
639 $message = "{0:p0} complete " -f $percentage
640 Write-Progress -Activity $message -PercentComplete ($percentage * 100)
641 Start-Sleep 1
642 }
643
644 $VerificationAgsquery = "select distinct AG from [Monitoring].[dbo].[tblFailoverServers](nolock)"
645 $getVerificationAgs = Invoke-Sqlcmd -Query $VerificationAgsquery -ServerInstance $MetadataServer
646 $VerificationAgs = $getVerificationAgs.AG
647 $PFOOP = PostFailover $VerificationAgs
648 $MAGs = $PFOOP[0]
649 $FOIP = $PFOOP[1]
650 If($MAGs -gt 0)
651 {
652 Addto-Warning "Retrying Failover for missed AGs..Please wait for failover to complete..."
653 $MAGsquery = " select AG from [Monitoring].[dbo].[tblFailoverLogs] where FailoverID = $fID and FailoverStatus = 'Failover was not initiated.' "
654 $AllMAGs = Invoke-Sqlcmd -Query $MAGsquery -ServerInstance $MetadataServer
655 ForEach($item in $AllMAGs)
656 {
657 $MAG = $item.AG
658 $MAGDetailsqry = " select * from [Monitoring].[dbo].[tblFailoverServers] where AG = '$MAG' "
659 $MAGDetails = Invoke-Sqlcmd -Query $MAGDetailsqry -ServerInstance $MetadataServer
660 $MAGFromServer = $MAGDetails.FromServer
661 $MAGTOServer = $MAGDetails.TOServer
662 $MAGTOServerFailoverMode = $MAGDetails.TOServerFailoverMode
663
664 If($MAGTOServerFailoverMode -eq 'SYNCHRONOUS_COMMIT')
665 {
666 Addto-log "Generating Failover Job for $MAG"
667 $k = Start-Job -scriptblock $FailoverScript -ArgumentList $fID, $MAG, $MAGFromServer, $MAGTOServer, $run, $MetadataServer -Name $MAG
668 $k | Format-Table Id,Name,State,PSBeginTime,Error
669 }
670 }
671 $time = 120 # seconds, use you actual time in here
672 Foreach($i in (1..$time))
673 {
674 $percentage = $i / $time
675 $message = "{0:p0} complete " -f $percentage
676 Write-Progress -Activity $message -PercentComplete ($percentage * 100)
677 Start-Sleep 1
678 }
679 Addto-Warning "Redoing post failover verification.."
680 $PFOOP2 = PostFailover $VerificationAgs
681
682 }
683 ElseIf($FOIP -gt 0)
684 {
685 Addto-Warning "Redoing post failover verification.."
686 $PFOOP3 = PostFailover $VerificationAgs
687 }
688 }
689 }
690 Else
691 {
692 $truncatequery2 = "truncate table [Monitoring].[dbo].[tblFailoverServers]"
693 Invoke-Sqlcmd -Query $truncatequery2 -ServerInstance $MetadataServer
694 Exit
695 }
696 $truncatequery2 = "truncate table [Monitoring].[dbo].[tblFailoverServers]"
697 Invoke-Sqlcmd -Query $truncatequery2 -ServerInstance $MetadataServer
698
699 }
700
701 #getPassiveNodes
702 }
703}
704Catch
705{
706 $ErrorMessage = $_.Exception.Message
707 $line = $_.InvocationInfo.ScriptLineNumber
708 Addto-ErrLog "Mainprogram: AGlistener--$AGlistener : $ErrorMessage Error Occurred on line $line"
709}
710FINALLY
711{
712 $ErrorActionPreference = "CONTINUE";
713}
714
715}
716catch
717{
718 $ErrorMessage = $_.Exception.Message
719 $line = $_.InvocationInfo.ScriptLineNumber
720 Addto-ErrLog " $ErrorMessage Error Occurred on line $line"
721}