Powershell Test AlwaysOn

by Harold Preyers | Apr 15, 2016 | Microsoft, PowerShell, SQL Server 2012

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

by Harold Preyers | Apr 13, 2016 | PowerCLI, VMware, vSphere

Reconfigure diagnostic partition with PowerCLI using Get-EsxCli

The following Get-EsxCli command will unconfigure your diagnostic partition and reconfigure with smart selection. This was needed because the install partition uuid had changed due to an option in the NetApp system while doing system testing.

$server_list = Get-VMhost

Foreach ($srv in $server_list)
{
 $esxcli = Get-EsxCli -VMhost $srv
 #$esxcli.system.coredump.file.add($null,"VMFS_log_partition","$srv.name",$null)
 $esxcli.system.coredump.partition.set($null,$null,$null,$true)
 $esxcli.system.coredump.partition.set($true,$null,$true,$null)
 $esxcli.system.coredump.partition.get()
}

$server_list = Get-VMhost

Foreach ($srv in $server_list)

{

$esxcli = Get-EsxCli -VMhost $srv

#$esxcli.system.coredump.file.add($null,"VMFS_log_partition","$srv.name",$null)

$esxcli.system.coredump.partition.set($null,$null,$null,$true)

$esxcli.system.coredump.partition.set($true,$null,$true,$null)

$esxcli.system.coredump.partition.get()

}

Many thanks to http://www.virten.net/2014/02/howto-use-esxcli-in-powercli/

PowerCLI goodies

by Harold Preyers | Apr 12, 2016 | PowerCLI, VMware

These are powercli goodies I use on a regular base. I have collected them here to find them easily. Some I wrote myself, some are copied from other sites. If I didn’t reference the source, I don’t know anymore where I found it.

Change portgroup on a lot of vm’s

Testing with one vm

get-vm my_vm_name&nbsp;| Get-NetworkAdapter |where {$_.networkname -eq "current_network_label"} | set-networkadapter -portgroup "new_network_label" -confirm:$false

1	get-vm my_vm_name \| Get-NetworkAdapter \|where {$_.networkname -eq "current_network_label"} \| set-networkadapter -portgroup "new_network_label" -confirm:$false

Reconfigure all vm’s with the string “vdi” in the name

get-vm *vdi*&nbsp;| Get-NetworkAdapter |where {$_.networkname -eq "current_network_label"} | set-networkadapter -portgroup "new_network_label" -confirm:$false

1	get-vm vdi \| Get-NetworkAdapter \|where {$_.networkname -eq "current_network_label"} \| set-networkadapter -portgroup "new_network_label" -confirm:$false

Find all vm’s with more than 8 CPUs

(get-view -viewtype virtualmachine).summary.config | where {$_.numcpu -gt 8}

1	(get-view -viewtype virtualmachine).summary.config \| where {$_.numcpu -gt 8}

Reload Syslog via esxcli

While executing the NetApp MetroCluster testplan, the syslog service stops logging to the presented syslog datastore. To restart the logging reload the syslog service on all impacted hosts. The following command will reload the syslog service on all hosts in the connected vCenters. Check the $global:defaultviservers to know which vCenters are connected.

$server_list&nbsp;= Get-VMhost

Foreach ($srv in $server_list)
{
   $esxcli = Get-EsxCli -VMhost $srv
   $esxcli.system.syslog.reload()
}

$server_list = Get-VMhost

Foreach ($srv in $server_list)

{

$esxcli = Get-EsxCli -VMhost $srv

$esxcli.system.syslog.reload()

}

Speed-up the initialization of PowerCLI

This needs to be done for each registered version of PowerCLI. This one worked for me on Windows Server 2012 R2

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe install “VimService55.XmlSerializers, Version=5.5.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f” /ExeConfig:c:\windows\system32\WindowsPowerShell\v1.0\PowerShell_ISE.exe

Change the Power Management Policy for each host in cluster

#get connected esxi hosts
$vmhostlist = get-vmhost |sort|select name

#for each host show the power management policy setting
foreach ($entry in $vmhostlist) {
   #list power management policy on all connected esxi hosts
   get-vmhost |sort|select name, @{ N="CurrentPolicy"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.config.PowerSystemCapability.AvailablePolicy.ShortName}
}

#for each host change the power management policy to high performance
foreach ($entry in $vmhostlist) {
   $view=(get-vmhost $entry.name|get-view);(get-view $view.configmanager.powersystem).configurepowerpolicy(1)}
   #list power management policy on all connected esxi hosts
   get-vmhost |sort|select name, @{ N="CurrentPolicy"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.config.PowerSystemCapability.AvailablePolicy.ShortName}
}

#get connected esxi hosts

$vmhostlist = get-vmhost |sort|select name

#for each host show the power management policy setting

foreach ($entry in $vmhostlist) {

#list power management policy on all connected esxi hosts

get-vmhost |sort|select name, @{ N="CurrentPolicy"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.config.PowerSystemCapability.AvailablePolicy.ShortName}

}

#for each host change the power management policy to high performance

foreach ($entry in $vmhostlist) {

$view=(get-vmhost $entry.name|get-view);(get-view $view.configmanager.powersystem).configurepowerpolicy(1)}

#list power management policy on all connected esxi hosts

}

Update:

Apparently there were some errors (curly brackets missing or in the wrong place) in the previous code.

It also ran several times per host because per host there was a Get-VMHost in the ForEach iteration. So If you had three hosts it would run three times per host.

The updated and optimized code:

$VMHosts = Get-VMHost

#for each host show the power management policy setting
ForEach ($entry in $VMHosts) {
   #list power management policy on all connected esxi hosts
   $entry | Select Name, @{ N="CurrentPolicy"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.Config.PowerSystemCapability.AvailablePolicy.ShortName}}
}

#for each host change the power management policy to high performance
ForEach ($entry in $VMHosts) {
   $view=($entry | Get-View);(Get-View $view.ConfigManager.PowerSystem).ConfigurePowerPolicy(1)
   #list power management policy on all connected esxi hosts
   $entry | Select Name, @{ N="CurrentPolicy"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.Config.PowerSystemCapability.AvailablePolicy.ShortName}}
}

$VMHosts = Get-VMHost

#for each host show the power management policy setting

ForEach ($entry in $VMHosts) {

#list power management policy on all connected esxi hosts

$entry | Select Name, @{ N="CurrentPolicy"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.ShortName}},@{N="CurrenPolicyKey"; E={$_.ExtensionData.Config.PowerSystemInfo.CurrentPolicy.Key}},@{N="AvailablePolicies";E={$_.ExtensionData.Config.PowerSystemCapability.AvailablePolicy.ShortName}}

}

#for each host change the power management policy to high performance

ForEach ($entry in $VMHosts) {

$view=($entry | Get-View);(Get-View $view.ConfigManager.PowerSystem).ConfigurePowerPolicy(1)

#list power management policy on all connected esxi hosts

}

show connected vcenters/esxi’s

$Global:DefaultVIServers

1	$Global:DefaultVIServers

connect to previously connected vcenters/esxi’s

connect-viserver -menu

1	connect-viserver -menu

move vm storage vmotion

get-vm Win* | move-vm -Datastore (Get-Datastore sdc-t*)

1	get-vm Win* \| move-vm -Datastore (Get-Datastore sdc-t*)

Mounting and unmounting NFS datastores

Mount NFS datastore

get-vmhost | New-Datastore -Nfs -Name Datastore_Name -Path /vol/Name_on_NFS_host -NfsHost NFS_host_IP

1	get-vmhost \| New-Datastore -Nfs -Name Datastore_Name -Path /vol/Name_on_NFS_host -NfsHost NFS_host_IP

Remove NFS datastore

get-vmhost | Remove-Datastore -datastore Datastore_Name

1	get-vmhost \| Remove-Datastore -datastore Datastore_Name

Change the default ‘ESX Admins’ AD group to your group name

ESXi servers will by default search for the group ‘ESX Admins’ in Active Directory. The following command will change this to ‘my_group_name’.

get-vmhost | Get-AdvancedSetting "Config.HostAgent.plugins.hostsvc.esxAdminsGroup" | Set-AdvancedSetting -value "my_group_name" -Confirm:$false

1	get-vmhost \| Get-AdvancedSetting "Config.HostAgent.plugins.hostsvc.esxAdminsGroup" \| Set-AdvancedSetting -value "my_group_name" -Confirm:$false