Art of the DBA Rotating Header Image

April, 2015:

#Powershell in Atlanta

SQL Saturday Atlanta is coming up in two weeks and I’ll be giving my Introduction to Powershell for SQL Server DBAs precon the day before the event. I’m extremely excited to offer this precon and get more SQL Server professionals exposed to Powershell. Think you might be interested? Here’s the synopsis:

Maybe you’ve only heard of Powershell. Maybe you’ve seen a little bit of Powershell code, but still don’t understand how it works. You’ve been telling yourself for months now that you need to learn it. Why wait? The time to get started with Powershell is now.

This full day session will help build your foundation for learning and using Powershell. While we will be focusing on using Powershell as a SQL Server Database Administrator (or Developer), much of the material will also review general use for system administrators. By attending this training, you will gain an understanding of what Powershell is, how you can use it in your day to day management of your environments, and what specific things can be done using Powershell in a SQL Server environment.

You can sign up on Eventbrite and use the discount code ‘Mike_Fal’ to save $15 off the registration fee.  The session will be held at the Georgia State University Alpharetta Center on May 15 from 9am to 5pm.

I hope to see you there!

 

Availability Groups, Agent Jobs, and #Powershell

My current gig has me working a lot with Availability Groups. I love the options they give me with their ability to failover fast and replicate my data to other servers. It is a lot of peace of mind for high availability and data protection. The price of all this comes from a much more complex system to manage and the extra considerations needed to have a properly deployed Availability Group.

One such consideration is your SQL Agent Jobs. It is pretty well documented that your server level objects must be replicated or created outside of the actual Availability Group process. It’s not difficult, but must be thought about and addressed when setting up a server. If you source control your management jobs, then it should be a simple matter of executing a script to create these jobs on a new server. There are cases, though, where either jobs get out of sync or are created outside of the source control process. When this happens, administrators need to copy these jobs to the other servers.

The common way to accomplish this is to simply script out the job in Managment Studio, then run the script on the other nodes. While effective, this is also manual and prone to error, such as forgetting a server.

By automating this process, we can ensure consistency.  You can use Powershell to leverage the SMO and the .Script() method to basically duplicate your Management Studio action.  However, by using this and incorporating it into a script, we can ensure our consistency.  The following function is a pretty basic use of this that allows you to copy a single job from a source server to any number of targets:

function Copy-SQLAgentJob{
  param([string]$Source
  ,[string[]]$Targets
  ,[string]$JobName)

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$smosource = New-Object Microsoft.SqlServer.Management.Smo.Server $Source
$JobScript = $smosource.JobServer.Jobs[$JobName].Script()

foreach($Target in $Targets){
 $smotarget = New-Object Microsoft.SqlServer.Management.Smo.Server $Target
 if($smotarget.JobServer.Jobs.Name -notcontains $JobName){
   $smotarget.Databases['msdb'].ExecuteNonQuery($JobScript)
    }
  }
}

All this function does is find the source job, script it out, and then run that job creation script against all the other targets. Simple enough. One thing to call out is I’ve specifically written this function to stay completely within the SMO. Normally, I would use Invoke-SqlCmd to execute the SQL script, but I thought it simpler to use the SMO .ExecuteNonQuery() method (which runs a SQL batch and does not expect a return).

Now let’s implement this for a hypothetical Availability Group to copy from one node to all the others:

$PrimaryNode = 'PICARD'
$nodes = (Get-ClusterNode -Cluster (Get-Cluster $PrimaryNode).Name | Where-Object {$_.Name -ne $PrimaryNode}).name
Copy-SQLAgentJob -Source $PrimaryNode -Targets $nodes -JobName 'Backup Databases - Powershell'

Of course, we could also use this in a much simpler fashion, such as deploying a new job across all of our servers. Remember the Central Management Server trick I’ve implemented in the past? We can fold that in here for an easy deployment once we’ve created the job on a single server in our environment.

$CMS=’PICARD’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)
$servers+=$cms

Copy-SQLAgentJob -Source $CMS -Targets $Servers -JobName 'Backup Databases - Powershell'

As always, the lesson learned here is to leverage our tools to make more our work more repeatable and consistent. While everything we have done here can be done in other methods, I prefer this because it minimizes the interaction needed to accomplish the task. The more any of us have to touch a process to get work done, the greater our risk of error. You’ve heard it before: consistency through automation, this time for SQL Agent jobs.

 

#TSQL2SDAY 65 – TIL Edition: Whaddya mean there’s no endpoint?

This month’s T-SQL Tuesday is from Doc Mike Donnely(@SQLMD) with a simple premise: show us something you learned. Considering that working in the information technology field means we are always learning (or at least should be), blogging on this should be a snap. It’s a great fit for the #SQLNewBlogger challenge if you’re in on it, or just a nice reason to write if you’re not.

My tidbit is somewhat Powershell related, but touches on general setup of Availability Groups. If you read my early short series on automating SQL Server builds, I show you how you can build an Availability Group using Powershell. When I was testing it, the process works just fine for building the Availability Group, but I ran into problems when adding databases. When I went through the troubleshooting, I discovered that while you specify an endpoint in the Powershell cmdlet call, that cmdlet doesn’t actually create said endpoint. It just records the endpoint as part of the config.

This should not really come as a surprise. If you ever use the wizard in SQL Server Management Studio to create an Availability Group, it will script out commands to create the proper endpoint. I assumed that the Powershell cmdlet would work similar to the wizard, but it does not. This means I will have to add a step in my process to actually create the endpoint.

There are two ways to do this. The first is to simply use T-SQL in my script and pass it to either Invoke-SqlCmd or sqlcmd:

$endpointsql = @"
CREATE ENDPOINT [HADR_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES);
"@

Invoke-SqlCmd -ServerInstance 'NODE1' -Database 'master' -Query $endpointsql

The second option is to use the SMO to create an endpoint:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$smonode = New-Object Microsoft.SqlServer.Management.Smo.Server ‘NODE1’

#create endpoint
if($smonode.Endpoints.Name -notcontains 'HADR_endpoint'){
    $EndPoint = New-Object Microsoft.SqlServer.Management.Smo.Endpoint($smonode, 'HADR_endpoint')
    $EndPoint.EndpointType = 'DatabaseMirroring'
    $EndPoint.ProtocolType = 'Tcp'
    $EndPoint.Protocol.Tcp.ListenerPort = 5022
    $EndPoint.Payload.DatabaseMirroring.ServerMirroringRole = 'All'
    $EndPoint.Payload.DatabaseMirroring.EndpointEncryption = 'Required'
    $EndPoint.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm ='Aes'
    $EndPoint.Create()
    $EndPoint.Start()
}

The choice between these options is down to personal comfort, either one would be acceptable in my eyes. I lean towards the SMO method just because I’m likely to be using the SMO objects for other operations in a script.

The lesson here is the old line about assumptions being the mother of all screwups and why you should always understand what your tools are doing. If you get stuck in to using wizards all the time, you will not see what is happening under the covers. Using a tool is fine, even necessary, but you should always make sure you understand the mechanics of those tools in case something does not work as expected.

How can you learn those mechanics? Almost every action in SSMS can be scripted out into T-SQL. This key learning tool is something I always stress to new SQL Server folks. It is the easiest way to see what SQL Server is trying to do and helps build your skills. The next time you’re trying to do something in Management Studio, study the script, and teach yourself something new.

Update:

So there’s a third option and I’m mildly annoyed at myself for missing it. As Nic Cain(@SirSQL) points out in the comments, Microsoft provides cmdlets for endpoints. In keeping with the links above, here’s your use of them:

$endpoint = New-SqlHadrEndpoint HADR_Endpoint -Port 5022 -Path SQLSERVER:\SQL\NODE1\DEFAULT
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"

A LOT simpler using the SMO and remains consistent with the rest of the script. Probably the better way to go.  Thanks Nic!

The PASS Summit 2015 Call for Speakers

Here we are, the annual community tradition of submitting sessions to the PASS Summit. This is the big time, the real deal, the event everyone wants to be seen at (as far as SQL Server goes). We all have hopes and dreams of standing on that stage, sharing what we love about SQL Server with the greater community.  Now that the submission deadline has passed, I wanted to post a few thoughts I had on this year’s process.

And yes, I want to do it before the OTHER annual community tradition of “bitching about why I didn’t get selected” starts.

Too Many Topics

The first thing that struck me was the excessive number of topics in each track. I didn’t count them all, but it was a little overwhelming. What made the process especially difficult was that there’s a lot of overlap with these subcategories, making it confusing about which to select.  This is especially a challenge for me with Powershell submissions, because many of the abstracts I created could have easily gone under two or three subcategories.

One example is my abstract on automating SQL Server builds with Powershell. Now, there’s a Powershell/SMO subcategory and one for Installing/Upgrading SQL Server. Which do I choose? I went with Powershell because that’s the focus of my session, but it might have been more applicable to the other.  This is a problem.

Suggestion: Either PASS needs to cull the overall list of topics or allow you to select multiple topics for your abstract.  Personally, I’d prefer the former.

Too Many Submissions

We have a good problem in the SQL Server community: tons of great speaking talent. There a lots of folks at the local and regional level who give great presentations at local user groups and SQL Saturdays. There’s also a fair amount of mediocre talent as well. However, all of these speakers can submit to Summit, regardless of their experience level. This places an overwhelming burden of work on the program committee.

I don’t want to make speaking at the Summit an exclusive club. I think all community members should have the opportunity to present at the Summit, but we need to balance that with consideration for the program committee volunteers and trying to provide the best product for Summit attendees. As such, the process should focus on standards that encourage both active community participation and a focus on public speaking.

Suggestion: PASS needs to start placing restrictions on who can and can’t submit. They already do this for the precons. My initial thought is a speaker should have four public SQL speaking events in the past calendar year. With opportunities like local user groups, SQL Saturdays, and virtual chapters, this is not a difficult number to hit.  It ensures that the speaker is an active community participant, someone who is contributing and practicing their craft.

We need more faces

In general, this isn’t a big deal, but we do have many popular speakers get multiple sessions at the Summit. I understand these folks are a draw and will put butts in seats, but the reality is that every person who gets doubled up means one less other speaker who will be selected. Again, considering the depth of speaking talent in the PASS community, this strikes me as problematic in general and a wasted opportunity to increase the variety of speakers at the Summit.

Suggestion: All speakers are limited to a single session, no exceptions.

More Transparency Around the Selection

I know this is a constant refrain regarding PASS, but it is needed. This is not me piling on the organization for being closed and secretive, but simply reminding them that we still need to know more about what’s going on. I’ve been paying attention to the selection process for the past two years and it IS getting better. I like how open PASS has been with how the program committee works.

The challenge is that the program volunteers don’t make the actual selections. They make recommendations that are passed to the selection committee. The selection committee then performs “levelling” and uses that to select sessions. This is the black box, because I don’t know what the levelling process entails and how things are judged. I’m not advocating some grand conspiracy theory around who gets selected, but there will always be questions and criticism until we are better informed about why.

Suggestion: PASS needs to continue what they have been doing and communicate more about the process. As I said, I recognize that the process of improvement is ongoing and it is getting better, but it can’t stop. The community needs communication, especially as we grow larger and larger.

Videos Don’t Add Value

I understand that PASS is trying to gather as much information about a speaker as possible. Videos are not the way to do it. There are too many issues with recording quality and software that make this a reliable method. Additionally, many videos won’t actually display a candidate’s public speaking skills, only their ability to sit down and chat in front of a camera. These combine to make video recordings more subjective than objective, and we need to focus on objective quantifiers.

Suggestion: Just ditch the video submission.

You Don’t Deserve To Speak At The Summit

This one is directed to the entire community, the people submitting. Every year I see the same tweets and posts griping about how certain people did not get selected or others did. How the process is horribly broken. To be fair, when you craft abstracts and then wait two to three months for a response, the resulting rejection is disappointing.

I think, because of all the other opportunities in the community, being rejected from a speaking event is a bit of a shock. The reality is that competition is tight for the Summit and speaking slots are limited. Getting turned down really is not that surprising. What bothers me most is that I see an attitude of entitlement from some of community members concerning presenting at the Summit. None of us are owed a speaking slot and the burden is on each individual speaker to prove their worth.

I view speaking at the Summit a lot like free agency in a professional sport. There’s a lot of competition to get noticed and a lot of reward if you do. Some people have an edge because they’re really good at what they do and demonstrate that on the public stage. Some others are hidden gems, waiting for their opportunity to get noticed. However, the market is driven by demand and selections are made based on that demand, not by attribution or “right”. We as speakers should always be striving to prove ourselves to the community and our selection should be based on ability, presence, and contribution, all of which will drive up demand.

Suggestion: Chill out if you don’t get selected. Focus on what you can do locally and regionally, through your own user group and SQL Saturdays.The Summit is awesome and I love going, but the truth is I have MUCH more of an impact speaking at smaller events. I encourage all community members to have a presence at these events, to focus on what they can do in their city. Build your community resume and skill set so that when you do submit to the Summit, you’ll be better prepared when you do make it to that stage.

In Conclusion…

I’ve already shared some of this with some members of the PASS board privately, but I wanted to share this publically as well. This is our community and we all have the ability to affect this change by being involved and reaching out. What has always impressed me about PASS is how grass roots it is and how members feed off each other. We need to focus on that, because it is what makes our community stronger than any other technical community out there.