Set PageVerify in Sql Server via Powershell

Quick post on how to set the page verification level in sql server with powershell.  This script will set all the user database page verification option to CheckSum if they’re not already set that way.  It’s best practice (and the safest option) to have this set to CheckSum.  For a more in-depth description of page verification, see here.  This uses SQLPSX.

Import-Module SqlServer

Get-SqlDatabase -sqlserver $(Get-SqlServer -sqlserver 'ServerName' -username 'UserName' -password 'Password') | where{-not $_.IsSystemObject} |%{
	if($_.PageVerify -ne [Microsoft.SqlServer.Management.Smo.PageVerify]::Checksum){
		$_.PageVerify = [Microsoft.SqlServer.Management.Smo.PageVerify]::Checksum
		$_.Alter();
	}
}

Using Secure Connections With SQLPS in Powershell

Quick note on how to use secure connections for Sql Server Authentication with SQLPS.  First step, you’ll need to create a text file to hold your encrypted password.  In this case, it’s just encrypted using the user key of the current logged in user (Windows Data Protection API).  Keep this in mind, as your sql server agent will (hopefully) be running under a different account, you’ll need to encrypt the file using that account if you’re planning on calling this via sql jobs.  Only the account that created this file will be able to decrypt and read it.

read-host -assecurestring | convertfrom-securestring | out-file C:\Powershell\Password.txt -Force

Next, you’ll need to create a ServerConnection object and pass in your credentials to it.  One caveat to note, is that you don’t pass in your encrypted password to the Password property, you have to pass it to the SercurePassword property of the ServerConnection.  The Password takes just a regular string as a parameter, whereas the SercurePassword property takes a securestring. 

Import-Module SQLPS -DisableNameChecking

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null

$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $InstanceName
$srvConn.Login = "LoginName"
$srvConn.SecurePassword = get-content "c:\Powershell\Password.txt" | convertto-securestring 

$RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $srvConn
$RepInstanceStatus = New-Object "Microsoft.SqlServer.Replication.ReplicationMonitor" $srvConn

In this case I’m using the connection to connect to RMO for a script for monitoring replication, but you can use this same method to connect to anything that accepts a ServerConnection.