Reconfigure diagnostic partition using Get-EsxCli -V2

by Harold Preyers | Nov 2, 2018

The following powershell snippet is going to unconfigure the diagnostic coredump partition using the esxcli version 2 cmdlet. The second part will reconfigure the diagnostic partition with the ‘smart’ option so that an accessible partition is chosen.

If you want to configure a new diagnostic partition the you will find the necessary information in the following VMware knowledge base article: Configuring a diagnostic coredump partition on an ESXi 5.x/6.x host (2004299). There will be additional steps to supply the partition id.

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

$srv = Get-VMHost ESXiHost

$esxcli = Get-EsxCli -VMHost $srv -V2

$arg = $esxcli.system.coredump.partition.set.CreateArgs()

$arg.unconfigure = "true"

$esxcli.system.coredump.partition.set.Invoke($arg)

$arg = $esxcli.system.coredump.partition.set.CreateArgs()

$arg.unconfigure = "false"

$arg.enable = "true"

$arg.smart = "true"

$esxcli.system.coredump.partition.set.Invoke($arg)

First we connect to the esxi host directly and insert the connection details in the variable $srv:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

1	$srv = Get-VMHost ESXiHost

Then we create a esxcli object $esxcli using the variable $srv we created previously:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

2	$esxcli = Get-EsxCli -VMHost $srv -V2

Now we create a variable $arg to store the arguments we will provide later:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

3	$arg = $esxcli.system.coredump.partition.set.CreateArgs()

Setting the $arg property ‘unconfigure’ to true will deactivate the diagnostic partition:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

4	$arg.unconfigure = "true"

The invoke command will invoke the command remotely on the esxi host. After execution the diagnostic partition is deactivated:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

5	$esxcli.system.coredump.partition.set.Invoke($arg)

The second part starts with creating a new set of arguments:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

6	$arg = $esxcli.system.coredump.partition.set.CreateArgs()

Reactivate the coredump, because we deactivated it before:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

7	$arg.unconfigure = "false"

Enable the coredump partition:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

8	$arg.enable = "true"

The ‘smart’ property will try to use an accessible partition:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

9	$arg.smart = "true"

The last argument will configure the diagnostic partition using the supplied parameters:

$srv = Get-VMHost ESXiHost
$esxcli = Get-EsxCli -VMHost $srv -V2
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)
$arg = $esxcli.system.coredump.partition.set.CreateArgs()
$arg.unconfigure = "false"
$arg.enable = "true"
$arg.smart = "true"
$esxcli.system.coredump.partition.set.Invoke($arg)

10	$esxcli.system.coredump.partition.set.Invoke($arg)

PowerShell goodies

by Harold Preyers | May 23, 2016

(more…)

List registered SPNs in Active Directory: pimped

by Harold Preyers | May 13, 2016

List all registered SPNs in Active Directory: pimped

This will go and poll all your registered SPNs in Active Directory and write them to a file. It accepts Debug, Log_Dir and Log_FileName as parameters.

Source: http://social.technet.microsoft.com/wiki/contents/articles/18996.list-all-spns-used-in-your-active-directory.aspx

<#
	.Synopsis
	Go and poll all your SPNs registered in Active Directory and write them to a file

	.Description
	Go and poll all your SPNs registered in Active Directory and write them to a file.

	.Author
	Harold Preyers

	.Parameter Debug
	The Debug parameter shows output to the screen.

	.Parameter Log_Dir
	Supply your own LogFile location.

	.Parameter Log_FileName
	Supply your own LogFile location. Don't add a file extension, the script will construct a filename based on the time of launch and add a .log extension

	.Example
	# Start SPN_List.ps1 without debug output based on the default log file location
	./SPN_List.ps1

	.Example
	# Start storage vmotions with debug output based on the default log file location
	./SPN_List.ps1 -Debug $true 

	.Example
	# Start storage vmotions with debug output based on custom log file location
	./SPN_List.ps1 -Debug $true -Log_Dir "C:\Scripts\SPN_List\Logs" -Log_FileName SPN_list 

#>

# Accept parameters
[cmdletBinding()]
	Param (
		[Bool]$Debug = $false				# Write output to console
		[string]$Log_Dir,					# Log Directory location
		[string]$Log_FileName,				# Log Filename
	)

#Variables
$Break = "-------------------------"

# Logging
$Log_Dir="C:\Scripts\Storage vMotion\Logs"
$Log_FileName="svMotion_log"
$Log_Breaker="##########################"
$global:File=""

# Create the necessary directories if necessary
If(!(Test-Path -Path $Log_Dir )){
	New-Item -ItemType directory -Path $Log_Dir
}

Function Initialize {
	# Logging parameters
	$Date = (get-date).tostring("yyyyMMdd_HHmmss")
	$global:File = $LogDir + "\" + $Log_FileName + "_" + $Date + ".log"
	If ($Debug) {Write-Host "The filename is: $global:File"}

	# Initialize log
	$Log_Breaker | Out-File "$global:File" -Append
	" LogFile:  $global:File" | Out-File "$global:File" -Append
	" LogDate:  $Date" | Out-File "$global:File" -Append
	" CSV File: $CSV_File" | Out-File "$global:File" -Append
	$Log_Breaker | Out-File "$global:File" -Append
	Add-Content -Path c:\temp\SPN_List.txt -Value "`n"
}

#Set Search
cls
$search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)
$search.filter = “(servicePrincipalName=*)”
$Results = $search.Findall()

#list results
Foreach($Result in $Results)
{
	$userEntry = $result.GetDirectoryEntry()

	$Output = "Object Name = " + $userEntry.name
	If ($Debug) {Write-Host $Output -backgroundcolor "yellow" -foregroundcolor "black"}
	$Output | Out-File c:\temp\SPN_List.txt -Append
	$Break | Out-File c:\temp\SPN_List.txt -Append

	$Output = "DN = " + $userEntry.distinguishedName
	If ($Debug) {Write-host $Output}
	$Output | Out-File c:\temp\SPN_List.txt -Append

	$Output = "Object Cat. = " + $userEntry.objectCategory
	If ($Debug) {Write-host $Output}
	$Output | Out-File c:\temp\SPN_List.txt -Append
	$Break | Out-File c:\temp\SPN_List.txt -Append

	$Output = "servicePrincipalNames"
	If ($Debug) {Write-host $Output}
	$Output | Out-File c:\temp\SPN_List.txt -Append
	$Break | Out-File c:\temp\SPN_List.txt -Append

	$i=1

	foreach($SPN in $userEntry.servicePrincipalName) {
		If (($i).tostring().length -le 1) {
			$preZero = "0"
		}
		Else {
			$preZero = ""
		}
		$Output = "SPN(" + $preZero + $i + ") = " + $SPN
		If ($Debug) {Write-host "SPN(" $preZero$i ") = " $SPN}
		$Output | Out-File c:\temp\SPN_List.txt -Append
		$i+=1
	}
	If ($Debug) {Write-host ""}
	$Break | Out-File c:\temp\SPN_List.txt -Append
	Add-Content -Path c:\temp\SPN_List.txt -Value "`n"
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

.Synopsis

Go and poll all your SPNs registered in Active Directory and write them to a file

.Description

Go and poll all your SPNs registered in Active Directory and write them to a file.

.Author

Harold Preyers

.Parameter Debug

The Debug parameter shows output to the screen.

.Parameter Log_Dir

Supply your own LogFile location.

.Parameter Log_FileName

Supply your own LogFile location. Don't add a file extension, the script will construct a filename based on the time of launch and add a .log extension

.Example

# Start SPN_List.ps1 without debug output based on the default log file location

./SPN_List.ps1

.Example

# Start storage vmotions with debug output based on the default log file location

./SPN_List.ps1 -Debug $true

.Example

# Start storage vmotions with debug output based on custom log file location

./SPN_List.ps1 -Debug $true -Log_Dir "C:\Scripts\SPN_List\Logs" -Log_FileName SPN_list

# Accept parameters

[cmdletBinding()]

Param (

[Bool]$Debug = $false # Write output to console

[string]$Log_Dir, # Log Directory location

[string]$Log_FileName, # Log Filename

)

#Variables

$Break = "-------------------------"

# Logging

$Log_Dir="C:\Scripts\Storage vMotion\Logs"

$Log_FileName="svMotion_log"

$Log_Breaker="##########################"

$global:File=""

# Create the necessary directories if necessary

If(!(Test-Path -Path $Log_Dir )){

New-Item -ItemType directory -Path $Log_Dir

}

Function Initialize {

# Logging parameters

$Date = (get-date).tostring("yyyyMMdd_HHmmss")

$global:File = $LogDir + "\" + $Log_FileName + "_" + $Date + ".log"

If ($Debug) {Write-Host "The filename is: $global:File"}

# Initialize log

$Log_Breaker | Out-File "$global:File" -Append

" LogFile: $global:File" | Out-File "$global:File" -Append

" LogDate: $Date" | Out-File "$global:File" -Append

" CSV File: $CSV_File" | Out-File "$global:File" -Append

$Log_Breaker | Out-File "$global:File" -Append

Add-Content -Path c:\temp\SPN_List.txt -Value "`n"

}

#Set Search

cls

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)

$search.filter = “(servicePrincipalName=*)”

$Results = $search.Findall()

#list results

Foreach($Result in $Results)

{

$userEntry = $result.GetDirectoryEntry()

$Output = "Object Name = " + $userEntry.name

If ($Debug) {Write-Host $Output -backgroundcolor "yellow" -foregroundcolor "black"}

$Output | Out-File c:\temp\SPN_List.txt -Append

$Break | Out-File c:\temp\SPN_List.txt -Append

$Output = "DN = " + $userEntry.distinguishedName

If ($Debug) {Write-host $Output}

$Output | Out-File c:\temp\SPN_List.txt -Append

$Output = "Object Cat. = " + $userEntry.objectCategory

If ($Debug) {Write-host $Output}

$Output | Out-File c:\temp\SPN_List.txt -Append

$Break | Out-File c:\temp\SPN_List.txt -Append

$Output = "servicePrincipalNames"

If ($Debug) {Write-host $Output}

$Output | Out-File c:\temp\SPN_List.txt -Append

$Break | Out-File c:\temp\SPN_List.txt -Append

$i=1

foreach($SPN in $userEntry.servicePrincipalName) {

If (($i).tostring().length -le 1) {

$preZero = "0"

}

Else {

$preZero = ""

}

$Output = "SPN(" + $preZero + $i + ") = " + $SPN

If ($Debug) {Write-host "SPN(" $preZero$i ") = " $SPN}

$Output | Out-File c:\temp\SPN_List.txt -Append

$i+=1

}

If ($Debug) {Write-host ""}

$Break | Out-File c:\temp\SPN_List.txt -Append

Add-Content -Path c:\temp\SPN_List.txt -Value "`n"

}

Powershell Test AlwaysOn

by Harold Preyers | Apr 15, 2016

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.

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.
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.

<#
	.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
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

.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

}

Reconfigure diagnostic partition using Get-EsxCli -V2

PowerShell goodies

List registered SPNs in Active Directory: pimped

List all registered SPNs in Active Directory: pimped

Powershell Test AlwaysOn

Powershell Test AlwaysOn

Top Posts & Pages

Subscribe to Blog via Email