Update sql server operators (redux)

I have a version of this already on here that uses SQLPSX, but since that’s not being updated anymore and that you may not always have SQLPSX available, here is a version that does not use SQLPSX. I’ve more or less stopped using SQLPSX, but I do miss it at times.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$outputs = @();
$newEmailAddress = 'NewEmailAddress@me.com'
$oldEmailAddress = 'OldEmailAddress@me.com'
$reportPath = 'c:\UpdatedOperators.csv'
$servers = gc -Path 'C:\Servers.txt'

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $_
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	$srv.JobServer.Operators | where{$_.EmailAddress -eq $oldEmailAddress} | %{
		$output = New-Object -TypeName PSObject -Property @{
		    ServerName = $srvName
		    OperatorName = $_.Name
		    OldEmailAddress = $_.EmailAddress
			NewEmailAddress = $newEmailAddress
		}
		$outputs += $output
		
		$_.EmailAddress = $newEmailAddress
		$_.Alter();
		
	}
	
}
$outputs | SELECT ServerName, OperatorName, OldEmailAddress, NewEmailAddress | Export-Csv -Path $reportPath -NoTypeInformation -Force

Perform Volume Maintenance task in database creation

If you have to do any file growing\restoring\creation at all, best grant the Perform volume maintenance tasks in the group policy edior (run –> gpedit.msc –> Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment) to the account that Sql Server is running under.

Just a short totally subjective note on the performance of creating a 5GB database with a 1GB log file. Without the Perform volume maintenance tasks right, this took about 2 minutes to complete.

After adding the service account to the Perform volume maintenance tasks (and restarting the Sql Server Service via configuration manager [a must]), the database takes 17 seconds to create.

When you add the service account to the Perform volume maintenance tasks it enables sql server to skip zeroing out the data file after it allocates space for it. Unfortunately, this can’t be done for the log file though. It must be zero’d out when it is grown or created.

Get Sql Server major version number from serverproperty function

More for me than for you. I’ve written and re-written this about 6 times this week and can’t seem to remember to save it off, so here I go. This will return the major version number for the sql server being queried. Sql Server 2000 = 8, Sql Server 2005 = 9, etc…

SELECT LEFT(CONVERT(SYSNAME,SERVERPROPERTY('ProductVersion')), CHARINDEX('.', CONVERT(SYSNAME,SERVERPROPERTY('ProductVersion')), 0)-1)

Pass a table type parameter to sql server via powershell

Still don’t like the title, but I’m pushing it anyway. Here is how to pass a DataTable to a Sql Server stored procedure via powershell. In my case, I’m gathering a boat-load of data from the sys.dm_os_performance_counters DMV for numerous server to be queried and reported upon. First I query the target server, get the results into a datareader, load said datareader into a DataTable and then pass the DataTable as a parameter to a stored proc to be saved off. Use at your own risk, this has been only minimally tested.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][reflection.assembly]::LoadWithPartialName("System.data")

$servers = gc -Path 'C:\Servers.txt'
$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName'

$PerfQry = "
SELECT 
	[OBJECT_NAME],
	counter_name,
	instance_name,
	cntr_value,
	cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name NOT IN(
	'SQLServer:Deprecated Features', 
	'SQLServer:Database Replica', 
	'SQLServer:Memory Broker Clerks',
	'SQLServer:User Settable',
	'SQLServer:Broker Statistics',
	'SQLServer:Availability Replica',
	'SQLServer:Access Methods',
	'SQLServer:Broker/DBM Transport',
	'SQLServer:Broker Activation',
	'SQLServer:Broker TO Statistics')
AND instance_name != 'mssqlsystemresource'
"

try{

	$servers | %{
		#$srvName = $_
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $_

		[System.Data.DataTable]$perfTable = New-Object('system.Data.DataTable')
		[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')
		[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')
		
		$c.connectionstring = $cnString;
		$c.open();
		
		$dr = $srvConn.ExecuteReader($PerfQry);
		$perfTable.load($dr);
		
		$cmd.Connection = $c;
		$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
		$cmd.commandText = "dbo.InsertPerfCounters"
		$serverIDParam = New-Object('system.data.sqlclient.sqlParameter')
		$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')
		
		#serverid param
		$serverIDParam.ParameterName = "ServerID"
		$serverIDParam.SqlDBtype = [System.Data.SqlDbType]::Int
		$serverIDParam.Direction = [System.Data.ParameterDirection]::Input
		$serverIDParam.value = 1
		
		#sql table type param
		$perfResultsParam.ParameterName = "PerfCounters"
		$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured
		$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input
		$perfResultsParam.value = $perfTable
		
		$cmd.parameters.add($serverIDParam);
		$cmd.parameters.add($perfResultsParam);
		
		$cmd.executeNonQuery();
		
	}
}
catch{
	$_ | fl -Force
}
finally{
	$c.close();
}