<#
	.Synopsis
	Monitors an SQL AlwaysOn cluster and tries to write to a database on this AlwaysOn cluster.
 
 	.Description
	Monitors an SQL AlwaysOn cluster and tries to write to a database on this AlwaysOn cluster.
 
	.Example
	# No Examples available
#>

# Import SQL module
Import-Module sqlps

# Connection parameters
$ServerInstance="database_server" # if you are running SQL on a custom port it should be "database_server,port"
$Database="TEST_AlwaysOn"

# Logging
$FileName="PrimaryLOG"
$LogDir="E:SQL_script"
$LogBreaker="##########################"
$global:File=""
$MaxLogEntries = 1000
$LogEntries = 1
$Failed = 0
$Sleep = 900

Function Initialize {
	# Logging parameters
	$LogFileDate = Get-Date -Format FileDateTime
	$global:File = $LogDir + "" + $FileName + "_" + $LogFileDate + ".log"
	If ($Debug) {write "The filename is: $global:File"}
 
	# Initialization (empty table)
	$SQLQuery=$("TRUNCATE TABLE Test_AlwaysOn.dbo.Test_Table")
	invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
	#Start-Sleep -Seconds 3
 
	# Initialize log
	$LogBreaker | Out-File "$global:File" -Append
	" LogFile: $global:File" | Out-File "$global:File" -Append
	" LogDate: $Date" | Out-File "$global:File" -Append
	" Server: $ServerInstance" | Out-File "$global:File" -Append
	" Database: $Database was truncated" | Out-File "$global:File" -Append
	$LogBreaker | Out-File "$global:File" -Append
}

Initialize

# MAIN
# While statement is built to always run
While ($true) {
	$Date = (get-date).ToString("yyyy-MM-dd HH:mm:ss.fff")
	# Generate at least one entry/day to see if I'm alive
	If ((get-date -Format hhmmss) -eq 000000) {
		$AddToFile = "$Date" + " It's a new day, it's a new dawn"
		$AddToFile | Out-File "$global:File" -Append
	}
	$Run++
 
	# Find AlwaysOn Primary
	$SQLQuery=$("SELECT AGC.name, RCS.replica_server_name, ARS.role_desc, AGL.dns_name " +
	"FROM sys.availability_groups_cluster AS AGC " +
	"INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS " +
	"ON RCS.group_id = AGC.group_id " +
	"INNER JOIN sys.dm_hadr_availability_replica_states AS ARS " +
	"ON ARS.replica_id = RCS.replica_id " +
	"INNER JOIN sys.availability_group_listeners AS AGL " +
	"ON AGL.group_id = ARS.group_id " +
	"WHERE ARS.role_desc = 'PRIMARY' ;")
	$AGResult=invoke-sqlcmd -query $SQLquery -serverinstance $ServerInstance -database $Database
	$PrimaryReplica = $AGResult[1]
 
	If ($PrimaryReplica -ne $PrimaryReplicaLastRun) {
		# Filter first run
		If ($PrimaryReplicaLastRun -eq $null) {
			$AddToFile = "$Date" + " AG is $PrimaryReplica"
			$AddToFile | Out-File "$global:File" -Append
			$LogEntries++
		}
		Else {
			# Write to log
			$AddToFile = "$Date" + " AG changed from $PrimaryReplicaLastRun to $PrimaryReplica"
			$AddToFile | Out-File "$global:File" -Append
			$LogEntries++
		}
	}

	# Write to table to see if it is available
	$SQLQuery=$("INSERT INTO Test_AlwaysOn.dbo.Test_Table (Test_Run,AG_Primary,Test_Time) " +
	"VALUES ('$Run','$PrimaryReplica','$Date'); " )
	invoke-sqlcmd -query $SQLquery -serverinstance $serverinstance -database $database

	# Read the last insert via $Run
	$SQLQuery=$("SELECT Test_Run,AG_Primary,Test_Time FROM Test_AlwaysOn.dbo.Test_Table where Test_run = $Run")
	$SelectResult=invoke-sqlcmd -query $SQLquery -serverinstance $serverinstance -database $database
	If ($Debug) {write "SelectResult: "$SelectResult}
	$AG_Primary = $SelectResult[1]
	$Test_Time = $SelectResult[2].ToString("yyyy-MM-dd HH:mm:ss.fff")

	# Write to log
	If ($Test_Time -eq $Date) {
		If ($Failed -gt 0) {
			$AddToFile = "$Date Database was not writable between $Test_TimeLastSucceed and $Date, we tried $Failed times."
			$AddToFile | Out-File "$global:File" -Append
			$LogEntries++
			$OfflineHR = $Date.Substring(11,2) - $Test_TimeLastSucceed.Substring(11,2)
			If ($Debug) {write $OfflineHR}
			$OfflineMin = $Date.Substring(14,2) - $Test_TimeLastSucceed.Substring(14,2)
			If ($Debug) {write $OfflineMin}
			$OfflineSec = $Date.Substring(17,5) - $Test_TimeLastSucceed.Substring(17,5)
			If ($Debug) {write $OfflineSec}
			$Offline = $OfflineHR*3600 + $OfflineMin*60 + $OfflineSec
			If ($Debug) {write $Offline}
			$AddToFile = "$Date Database was not writable for $Offline seconds."
			$AddToFile | Out-File "$global:File" -Append
			$LogEntries++
			$Failed = 0
		}
		$Test_TimeLastSucceed = $Test_Time
		$Test_Time = $null
	}
	Else {
		$Failed++
	}
	Start-Sleep -Milliseconds $Sleep

	# Check if the # entries have reached the maximum entries
	If ($LogEntries % $MaxLogEntries -eq 0) {
		$LogBreaker | Out-File "$global:File" -Append
		$AddToFile = "$Date" + " Log limits have been reached. A new log file will be started."
		$AddToFile | Out-File "$global:$File" -Append
		Initialize
	}

	# Keep the values of the variables of the last run
	$PrimaryReplicaLastRun = $PrimaryReplica
}