SQL Pass Summit 2014 – First day

Finally settled in Seattle for SQL Pass Summit 2014. Had a pleasant trip with IcelandAir from Stockholm in just above 12 hours. Feels lucky since a friend spent 22 hours.

Registration went smooth with a nice swag in form of a backpack that actually seems useable. Great !

Now I’m seated for the first pre conference day. I’m not going to cover everything in the session, just write down things of interest.

“Zero to hero with Powershell and sqlserver”

On stage are Bradley Ball (@sqlballs), Robert C. Cane (@arcanecode) and Jason Strate (@stratesql).

I’m not new to powershell, I have worked with it for maybe two years, but I never had any real training, so I thought starting from scratch would be a good thing. I believe there is always something new that you can learn.

Use #Region #EndRegion to being able to collapse sections of the code was new to me.

F5 – runs whole script

F8 – runs selection

Here strings ideal for SQL statements

$heretext = @”




First section of the session involved standard powershell cmdlets and programming syntax and functions. We learned the basic stuff.

Second session started to focus on SQL Server interaction, starting by loading SQL modules.

New in SQL 2012 was interaction with SSIS and SSAS.

Great example in the session on reading the errorlog with filters to find backup failures.

All scripts will be available at SQLPass website for download.

SQL Provider vs. SMO was discussed the primary difference is the depth of the details available for creating objects. SQL provider calls the same DLL’s as SMO. SMO seems a bit more complicated, but gives more options.

SQL Provider is much slower when traversing objects in a DB, SMO is the solution for speed.

Policy based management is not one of my favorite features in SQLServer, I’m not using it today, running it through powershell seems to ease the burden.

$serverName = "STRATE-SQL\SQL2014"

$policyFolder = "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Policies\DatabaseEngine\1033"
#explorer $policyFolder

$policy = $policyFolder+"\Backup and Data File Location.xml"

Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $serverName -AdHocPolicyEvaluationMode  Check -OutputXML

Enterprise Policy Management Framework seems to be a great tool to make it much simpler to use and also to create SSRS reports about how your environment comply.

Check out https://epmframework.codeplex.com

Ever wanted to move indexes between file groups ? This powershell script will do it quite easy for you.

$SQLServerName = "STRATE-SQL\SQL2014"
$databaseName = "Northwind"
$fromFileGroup = "PRIMARY"
$toFileGroup = "INDEX"

#$fromFileGroup = "INDEX"
#$toFileGroup = "PRIMARY"

$SQLServerInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServerName
$($SQLServerInstance.Databases | where {$_.name -eq $databaseName}).tables | % {
	$t = $_
#	write-host "$_.schema $_.name"
	$_.indexes | % {
		if ($_.id -ge 2 -and $_.filegroup -eq $fromFileGroup -and $_.IndexKeyType -eq "none") {
				$script = $($_.script()).replace("ON [$fromFileGroup]", "ON [$toFileGroup];").replace("(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)","(DROP_EXISTING = ON)")

				invoke-sqlcmd -ServerInstance $SQLServerName -Database $databaseName -Query $script

# check to see where all of the indexes are
foreach ($t in $($SQLServerInstance.Databases | where {$_.name -eq $databaseName}).tables) {
	$tableName = $t.Name
	$schemaName = $t.schema
	$t.Indexes | % {
		$index = $_.name
		$fileGroup = $_.filegroup

		Write-Host "$fileGroup, $schemaName, $tableName, $index"

Then we got into using SQLIO through powershell, this seems like a very interesting idea.

Much easier to run SQLIO through powershell, don’t have  to write so many different rows for a complete test. Also easy to run it against remote servers.

Jonathan Kehayias has created a nice Powershell script to parse the sqlio output and create an excel file with graphs and all. You can find it here:


Remaning of the sessions got more into different use cases, ETL loading through powershell is an interesting new concept. Able to build very dynamic scripts that for smaller load might be better than SSIS which is much more status when it comes to changes in source data. Have a look at:


In the end we looked at writing your own modules for powershell and how to execute sql scripts from within PS.

Great session all in all and it’s becoming more and more obvious that powershell is playing a larger and larger role in SQL server. Going forward with automation and orchestration, powershell knowledge is a must.

Now time for Networking Dinner with Steve Jones and Andy Warren at Buffalo Wild Wings.

This entry was posted in powershell, SQLServer and tagged . Bookmark the permalink.

One Response to SQL Pass Summit 2014 – First day

  1. Pingback: News About PASS For Week Ending November 8, 2014 | PASSWatch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s