Category Archives: Scripts

Know When SQL Server Availability Group Fails Over by Using Powershell

We recently started using HAG (High Availability Groups) in SQL Server and had several unplanned failovers. The failovers occurred for various reason which this article does not present. What was relevant to us was the failovers occurred without our knowledge and in one case, several days passed before we were aware a group had failed over to a secondary instance.

The purpose of this article is to show what was done to make us immediately aware of a failover as it occurs. This is accomplished by identifying an event occurrence associated with a failover and associating with a Windows Event Task. The event task would  then send an email containing the event message to a responsible group or person.

The event which I identified as being most relevant is Event 19406 from the SQL Instance (in this case the default instance of ‘MSSQLSERVER’) as presented here
image

I already knew how to attach a Windows Task to an event but what I did not know was how the data from the event is passed to the task. I wanted the task to be able to provide the event data in an email. In researching this I came across a great article on this very subject. The article presented all the information I needed including Powershell source code. I had to tweak the source code ever so slightly for my own use. The full article can be found at this link http://blogs.technet.com/b/wincat/archive/2011/08/25/trigger-a-powershell-script-from-a-windows-event.aspx and you should follow it. There is essential information you will need to know to edit and add to the Windows Task. Kudos to OttoHelweg2 for this fine article.

And, here is the source code I used based on the above mentioned article

Param($eventRecordID,$eventChannel)

$xpath = "<QueryList><Query Id='0' Path='$eventChannel'><Select Path='$eventChannel'>*[System[(EventRecordID=$eventRecordID)]]</Select></Query></QueryList>"
$event = Get-WinEvent -LogName $eventChannel -FilterXPath $xpath
$message = $event.Message

Send-MailMessage -From AvailabilityMonitor@myorganization.com -Subject "An SQL Failover is in Progress" `
    -To SQLServerGroup@myorganization.com -Body $message -SmtpServer mailserver.myorganization.com

The $eventRecordId and $eventChannel are passed to the Windows task from the triggering event and are used by Powershell to obtain the actual event record. The “message” data is then used as the Body of a Send-MailMessage cmdlet.

As mentioned, we are trapping the 19406 event. When reviewing the Event list, you will notice the event will fire several times in response to a failover. However, because the event is generated by the same task, the task associated with the event only fires once.

How To: Capture PowerShell Output in Windows Scheduled Tasks

In a previous blog Capture PowerShell Output in Windows Scheduled Tasks I stated the only reliable way I found to capture PS output is by using a CMD or Bat to execute the PS script. After stumbling upon the correct  way to call PowerShell using a –Command variable, I changed my mind. I found the correct way by reading the PowerShell help in PS v3 (imagine that) which says under –Command:

To write a string that runs a Windows PowerShell command, use the format:
“& {<command>}”
where the quotation marks indicate a string and the invoke operator (&)
causes the command to be executed.

So now, I can call PowerShell from Windows Task Scheduler with the correct syntax while still supplying various parms and send the whole command to an output file. The command would look like this:

Powershell.exe -NonInteractive -NoLogo -NoProfile -Command “& {D:\Scripts\SQL-Servers\Backup-SqlServer.ps1 –Server SQLSERVER -Verbose; Return $LASTEXITCODE}” 2>&1> D:\logs\SQL-Servers\Backup_SQLServer.txt”

This will execute my script, return whatever code I emit from the script, and write any output to a file.

Use Powershell to Copy Database Table Data

Note: Requires PS version 3.0 and must import module SQLPS

I had been using SSIS to copy some database tables between different servers. However, I really like Powershell and I thought it might be a little easier to control things when things change (like servers, table columns, etc.)

I found a really good article with a great code example here at Hey, Scripting Guy. For most people, the code will work “out of the box”. However, I did run into a snag on one of my tables. It turned out the table had two computed columns and SqlBulkCopy did not like that at all!

After a little research I found I could still use the “bulk” of the code (snicker) with some adjustments. To eliminate references to the computed columns I had to build the SELECT using column names vs. the out-of-the-box SELECT *.  This was easily accomplished be getting all the column names and then using the –join operator to construct a list of comma separated names. Here’s the piece of code to do just that:

$cols = @(Get-ChildItem SQLSERVER:SQL\$Server\databases\$SrcDatabase\Tables\$SrcTable\Columns |
		Where-Object -Property Computed -NE $true | Sort-Object -Property ID |
		ForEach-Object{"[" + $_.Name + "]"})
	$colnames = $cols -join ","

The other change I needed was to add column mapping to the SqlBulkCopy. That was another piece of easy code. However, it is only easy if the column names are identical between tables. In my case they are. And, I already have an array of column names from the code I used above.

# Map the columns
Foreach($col in $cols)
{
    [Void]$bulkCopy.ColumnMappings.Add($col, $col)
}

That is the extent of changes. All credit really goes to the Hey, Scripting Guy Blog! Here is the entire source if you need to borrow it.

<#
.Synopsis
	Copies data from a database table to another database.
.Description
	Copy the data from a source table to a destination table. This script uses 
	the .NET SqlBulkCopy class with a streaming IDataReader to achieve the 
	optimal performance (using a single thread).
.Parameter SrcServer
	The name of the source SQL server instance.
.Parameter SrcDatabase
	The name of the source databases.
.Parameter SrcTable
	The name of the source table to copy data from.
.Parameter DestServer
	The name of the destination SQL Server instance
.Parameter DestDatabase
	Name of the destination database. When omitted, it is set to the source
	database.
.Parameter DestTable
	Name of the destination table. When omitted, it is set to the source
	tabel
.Parameter Truncate
	When this switch is included, the destination table is truncated prior to 
	loading
.NOTES
	AUTHOR:		John P. Wood
	Company:	ATOS Origin\Lee County BOCC
	CREATED:	August, 2013
	VERSION:	1.0.0	
	WEB:		http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/06/use-powershell-to-copy-a-table-between-two-sql-server-instances.aspx
#>
#requires -version 3.0
[CmdletBinding()]
Param (
      [parameter(Mandatory = $true)] 
      [string] $SrcServer,
      [parameter(Mandatory = $true)] 
      [string] $SrcDatabase,
      [parameter(Mandatory = $true)] 
      [string] $SrcTable,
      [parameter(Mandatory = $true)] 
      [string] $DestServer,
      [string] $DestDatabase, 
      [string] $DestTable, 
      [switch] $Truncate 
  )
Function ConnectionString([string] $ServerName, [string] $DbName) 
{
	"Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
}

If ($DestDatabase.Length –eq 0) 
{
	$DestDatabase = $SrcDatabase
}

If ($DestTable.Length –eq 0) 
{
	$DestTable = $SrcTable
}

Try
{
	$SrcConnStr = ConnectionString $SrcServer $SrcDatabase
	$SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
	# Build SELECT with discreet column names due to possibilty of computed 
	# columns which cannot be bulk copied
	$server = $SrcServer
	If(-not($SrcServer.Contains('\'))) 
	{
		$server = "$SrcServer\default"
	}
	$cols = @(Get-ChildItem SQLSERVER:SQL\$Server\databases\$SrcDatabase\Tables\$SrcTable\Columns |
		Where-Object -Property Computed -NE $true | Sort-Object -Property ID |
		ForEach-Object{"[" + $_.Name + "]"})
	$colnames = $cols -join ","
	
	$CmdText = "SELECT $colnames FROM " + $SrcTable
	$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)  
	$SrcConn.Open()
	[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
	
	If ($Truncate) 
	{ 
		$TruncateSql = "TRUNCATE TABLE " + $DestTable
		Sqlcmd -S $DestServer -d $DestDatabase -Q $TruncateSql
	}

	$DestConnStr = ConnectionString $DestServer $DestDatabase
	$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, `
		[System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
		
#	$bulkCopy.BatchSize = 1000
	$bulkCopy.BulkCopyTimeout = 0

	$bulkCopy.DestinationTableName = $DestTable
	
	# Map the columns
	Foreach($col in $cols)
	{
		[Void]$bulkCopy.ColumnMappings.Add($col, $col)
	}
	$bulkCopy.WriteToServer($sqlReader)
	
	Write-Output "$SrcTable in $SrcDatabase on $SrcServer"
	Write-Output "`tcopied to $DestTable in $DestDatabase on $DestServer"
}
Catch 
{
	$ex = $_.Exception
	Write-Error $ex.Message
	While($ex.InnerException)
	{
		$ex = $ex.InnerException
		Write-Error $ex.Message
	}
}
Finally
{
	$SrcConn.Close()
	$SqlReader.Close()
	$bulkCopy.Close()
}

Move MSSQL Databases using Powershell with Detach\Attach

This is a script which can assist you in moving or copying a number of databases to a new server. We recently had to move move a lot of databases to new hardware and this came in very handy. The script uses the Detach and Attach method. You can move one database or you can pipeline a number of databases through the script. Database owners are maintained so you will need to migrate those logins prior to using the script. Also, the script does not reattach the databases on the source server. At some point I may update the script to accommodate reattaching the databases. For my current purpose, I did not need to do so. However, a switch is provided which informs the script to reattach the source database after it has been copied.

There is an assumption the script is run by a member of the ‘Domain Admins’ AD group. It shouldn’t be too difficult to change the script to use “shares” other than admin shares (i.e. Drives with a $ sign).

The code contains several references to functions which I have in an imported module. Over time I’ve developed a long list of my own helper functions and found it easier to have them in a module. The functions I reference are listed individually after the main source code.

Here is the mainline source for the script.

<#
.SYNOPSIS
	Copy SQL databases to another server.
.DESCRIPTION
	Uses the Detach and Attach method to copy or move a database or databases
	from one SQL Server to another. 
.PARAMETER Database
	Name of the database to be copied
.PARAMETER SourceServer
	Server and instance name of the source SQL Server
.PARAMETER DestinationServer 
	Server and Instance name of the destination SQL Server
.EXAMPLE
	$dblist | Migrate-Databases.ps1 -SourceServer 'SQLSERVER1' -DestinationServer 'SQLSERVER2\SQL2'
	Pipelines an array list of database names into the migration script with
	SQLSERVER1 as the source and SQLSERVER2\SQL2 as the destination.
.EXAMPLE 
	Migrate-Databases.ps1 -database 'MyDB' -SourceServer 'SQLSRV1' -DestinationServer 'SQLSRV2'
	Copies MyDB from SQLSRV1 to SQLSRV2
.NOTES
	Script uses administrative UNC shares names for all files to be copied and must be run by someone
	in the 'Domain Admins' group. Many of the functions called are part of an Imported 
	module containing many helper funtions. The 
	Author: 	John P. Wood
	Company:	ATOS, Inc\Lee County, FL BOCC
	Date:		June, 2013
	This code can be copied and used as long as proper attribution is given and
	these notes are kept intact.
#>
[CmdletBinding()]
Param(
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
	[String]$Database,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$SourceServer,
	[Parameter(Mandatory=$true,Position=2)]
	[string]$DestinationServer,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$ReattachSource
)

Begin
{
	Set-StrictMode -Version Latest
	Try
	{
		$smosrc = New-SMOconnection -server $SourceServer
		$smodest = New-SMOconnection -server $DestinationServer
		$filedest = Get-UncPath -server $DestinationServer -path $smodest.DefaultFile
		$logdest = Get-UncPath -server $DestinationServer -path $smodest.DefaultLog
	}
	Catch
	{
		Throw
	}
}
Process
{
	Try
	{
		$db = $smosrc.Databases[$database]

		# Break the mirror
		If($db.IsMirroringEnabled -eq $true) {
			Write-Verbose "Breaking mirror for $($db.Name)"
			$db.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off)
			$db.Alter()
			$db.Refresh()
		}

		# Get Data files
		$files = @(Get-DatabaseDataFiles -Database $db.Name -Server $SourceServer |
			ForEach-Object{$_.FileName})
		$files | Write-Verbose 

		# Get log files
		$logfiles = @($db.LogFiles | ForEach-Object{$_.Filename})
		$logfiles | Write-Verbose

		# get Owner
		$owner = $db.Owner
		$owner | Write-Verbose

		# Detach database
		$smosrc.KillAllProcesses($db.Name)
		$smosrc.DetachDatabase($db.Name,$false)

		# Copy files to destination
		$destfiles = @()
		$destlogs = @()

		$files | Get-UncPath -Server $SourceServer | Copy-Item -Destination $filedest
		$files | ForEach-Object {
			$destfiles += [System.IO.Path]::Combine($filedest,
				[System.IO.Path]::GetFileName($_))}

		$logfiles | Get-UncPath -Server $SourceServer | Copy-Item -Destination $logdest 
		$logfiles | ForEach-Object {
			$destlogs += [System.IO.Path]::Combine($logdest,
				[System.IO.Path]::GetFileName($_))
		}

		# Attach database
		$sc =  New-Object System.Collections.Specialized.StringCollection

		$destfiles | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$destlogs | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$smodest.AttachDatabase($db.Name, $sc, $owner, 
			[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

		# Reattach the source databases if switch is on
		If($ReattachSource.IsPresent)
		{
			$sc.Clear()
			$files | ForEach-Object{$sc.Add($_ )}
			$logfiles | ForEach-Object{$sc.Add($_ )}
			$smosrc.AttachDatabase($db.Name, $sc, $owner, 
				[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)
		}
		Return $smodest.Databases[$database]
	}
	Catch 
	{
		$ex = $_.Exception
		Write-Error $ex.Message
		While($ex.InnerException) 
		{
			$ex = $ex.InnerException
			Write-Error $ex.Message
		}
	}	
}
End
{
	$smosrc.ConnectionContext.Disconnect()
	$smodest.ConnectionContext.Disconnect()

}

A small function to get a UNC path

Function Get-UncPath {
<#
.Synopsis
	Returns a windows UNC path when given a server and path on the server.
.Description
	Returns a windows UNC path when given a server and path on the server.
.Parameter ComputerName
	The name of a computer or full dns name. This will also take an SQL Instance
	and remove the Instance name leaving the server name.
.Parameter Path
	The local path name of a a path on the computer
.Example Get-UncPath -ComputerName "LCFSQL07" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
.Example Get-UncPath -ComputerName "LCFSQL07\SQL01" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
#>
	[CmdletBinding()]
	Param(
		[Parameter(Position=0,Mandatory=$true)]
		[Alias("ComputerName")]
		[string]$Server,
		[string]$path
	)
	Process{
	Join-Path "\\$($server.split('\')[0])" $path.replace(':','$')
	}
}

Function to get an SMO connection

Function New-SMOconnection
{
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$Server,
		[Parameter(Mandatory=$false)]
		[string]$ApplicationName = "Powershell SMO",
		[Parameter(Mandatory=$false)]
		[int]$StatementTimeout=0
	)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($Server)
	$conn.ConnectTimeout = 5
	$conn.applicationName = $ApplicationName
	$conn.StatementTimeout = $StatementTimeout
	Try 
	{
		$conn.Connect()
		Return New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	}
	Catch 
	{
		Throw 
	}
}

A function to get physical files associated with a database

Function Get-DatabaseDataFiles
{	
	[CmdletBinding()]
	Param(
		[string]$Server,
		[string]$Database
	)
	Try
	{
		$smo = New-SMOconnection -server $Server
	}
	Catch
	{
		Throw $_.Exception
	}
	Try
	{
		$files = @()
		$db = $smo.Databases[$Database]
		If(!($db)) { Throw "$Database was not found" }
		Foreach($group in $db.FileGroups)
		{
			Foreach($datafile in $group.Files)
			{
				$hashtable = @{Group = $group.Name;
					Name = $datafile.Name;
					FileName = $datafile.Filename
				}
				$file = New-Object -TypeName PsObject -Property $hashtable
				$files += $file
			}
		}
	}
	Finally
	{
		$smo.ConnectionContext.Disconnect()
	}
		Return $files
}

Convert a UNC path back to a local path

Function Get-LocalPath {
	[CmdletBinding()]
	Param(
		[Parameter(Mandatory=$true)]
		[string]$uncpath
	)
	Process{
	$dirs = ($uncpath.Remove(0,2)).Split('\')
	$dir = ($dirs[1]).Replace('$',':')
	For([int]$ix=2; $ix -lt $dirs.Count; $ix++) {
		$dir += "\$($dirs[$ix])"
	}
	$dir
	}
}