Speaking at #sqlsat145 and #sqlsat167

Continuing my torrid pace of speaking at SQL Saturdays once in a while, I’ve been fortunate enough to be selected to speak at #167 in Columbus, GA (which I also have a small part in organizing) and at Nashville #145 in October. I’m particularly excited because I’m delivering one of my favorite talks: T-SQL Beyond the Basics. This presentation digs into what happens after you submit a query for execution. Many SQL developers spend an awful lot of time writing queries, but never understand the beautiful marvel of computer science that goes on just on the other side of their SSMS window or application. So I get to spend an hour talking about SQL Server architecture and execution plans. It’s one of those types of presentations where the ‘Ah ha!’ moments I love abound.

One of my own ‘Ah ha!’ moments is another reason why I enjoy this presentation so much. At my first PASS Summit I went to Maciej Pilecki’s (T) session on execution plans (plan caching specifically) and during that 90 minutes all sorts of bits of learning that I had been gathering over the years fell into place. Digging into the internals of SQL Server query executions suddenly made connections that were big revelations to me and my excitement level about my profession was through the roof.

I’m extraordinarily fortunate to do what I do. And I’m looking forward to sharing some of the excitement I have about the amazing world of data and the tools we use with the sql family.

Speaking at SQL Saturday #111

I’m completely thrilled to be invited back to Atlanta on April 14th to speak at SQL Saturday #111. I’ll be throwing caution to the proverbial wind and tackling a rather difficult subject…concurrency in PowerShell. Concurrency is a fascinating and complicated subject that both solves and creates all kinds of interesting Computer Science problems. There’s no way I’ll be able to talk about everything in 60 minutes, but there should be plenty of time to make everyone in the room awfully dangerous with multiple processors.

I chose the word “concurrency” carefully. Many people think that running processes at the same time (asynchronously) is multi-threading. To me, however, true multi-threading requires shared memory space and not all of PowerShell’s asynchronous capabilities use that model. In fact, you have to dig in to the PowerShell .NET assemblies to start understanding how to actually do true multi-threading. It isn’t the most intuitive, but it’s incredibly powerful when you wrap your hands around it.

I’m enjoying the topic so much, in fact, that I’m going to start my first series on this blog. Starting the Monday after SQL Saturday, I’ll be posting an in-depth look at the various methods of asynchronous processing in PowerShell. There is a small amount of information scattered around on various blog posts and forums out there, but I don’t know of anywhere that has a consolidated, more-or-less exhaustive look at the subject, so I think I’ll be bringing something good to the awesome PoSH community.

If you read this blog and come to SQL Saturday, make sure to say ‘hello!’ And make sure to follow the #sqlsat111 hash tag on Twitter.

#sqlsat89: No Excuses. Be Involved.

When I first submitted my sessions to SQL Saturday #89 in Atlanta, I had no idea that so many fantastic speakers would be there.  If I had known, I probably would not have gotten involved.  You shouldn’t be like me.

From my point of view, it was an injustice to the attendees for me to take a speaking slot from any one of the amazing experts in attendance.  Would those who attended my session have learned more about PowerShell from Nic Cain (T | B), Jim Christopher (T | B) or Aaron Nelson (T | B)?  Yep.  Is there someone who was overlooked who would have had a better session than mine?  Certainly.  It would have been a stronger event if someone else had taken my place, but it may not have been a stronger community.

What I understand now is that there isn’t much to a community where only the top experts contribute.  We need them.  I need them.  But, a community is composed of people who are involved.  I have some knowledge of and a lot of passion for PowerShell and SQL Server and I wanted to give as much of that as I could to the people who were in my session.  And that’s what PASS is all about: giving whatever you can, even if others can give more.

Speaking at #sqlsat89 is one of the best experiences I’ve ever had in my career.  My session went very well and some new opportunities opened for me. I learned about parallelism from Adam Machanic (T | B) and about waits and latches from Bob Ward (T | B) .  Most importantly, I met many fantastic people I hope to see many more times at future events.  I will speak again and I will continue getting better. I will learn more and contribute more.  And one day I will be one of the experts getting bumped for a first time speaker.  I hope that will be someone who reads this and decides to become part of this fantastic SQL Server community.

#sqlsat89 scripts

Here are the scripts I used during my session, Getting Started With PowerShell. I’ll have an after event post soon.

Starting Services:


#Start SQL Server
Get-Command
Get-Command -CommandType Cmdlet
Get-Command -CommandType Cmdlet | Format-Table name

# List all services
Get-Service 

# Attempt to filter on the "SQL" string
Get-Service | Select-String -Pattern "SQL"

# The filter doesn't work, why not?
Get-Help Select-String

# The output of Get-Services is an array of objects!
Get-Service | Get-Member ;

# Use the Where-Object cmdlet to filter the results
Get-Service | Where-Object{$_.Name -match "SQL"}

# You can start every service on the server at once if you want. 
# Get-Service | Where-Object{$_.Name -match "SQL"} | Start-Service

# Use the -Force parameter to ignore dependencies
# Get-Service | Where-Object{$_.Name -match "SQL"} | Stop-Service -Force

# There are several ways to start the services
# Start-Service "SQLSERVERAGENT" ;
# Start-Service "MSSQLSERVER" ;

Start-Service "MSSQLSERVER", "SQLSERVERAGENT" ; 
"MSSQLSERVER", "SQLSERVERAGENT" | Start-Service;

Renaming Agent Jobs:


# Declare a variable for the desired username
$job_owner = "DBAsRule" ;

# Even text is an object!
$job_owner |Get-Member

# Declare the intention to use the Microsoft.SqlServer.SMO .NET namespace
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

# The PowerShell 2.0 convention is to use Add-Type, but it is sometimes problematic.
# Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
# Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”

# Store an instance of the local SQL in a variable
$sql_server_object = New-Object Microsoft.SqlServer.Management.SMO.Server('localhost')

# Get-Help Get-Member

# Get the SQL Agent SMO object
$sql_server_agent = $sql_server_object.JobServer;

# $sql_server_agent | Get-Member

# Get the JobCollection object
$jobs = $sql_server_agent.Jobs;



foreach($j in $jobs)
{
	$j.OwnerLoginName;
}


foreach($j in $jobs)
{
	if($j.OwnerLoginName -ne $job_owner )
	{
		$j.OwnerLoginName = $job_owner;
	}	
}