Stuart Moore

Musings of a Data professional

Stuart Moore

Adding SQL Server jobs using PowerShell

PowerShellA sudden requirement for a new SQL Server agent job to be put on to every instance in our Enterprise was hardly greeted with joy. At least by those who hadn’t been playing with PowerShell.

Just like everything else in SQL Server, we can automate SMO to achieve our ends. And once we’ve got it working on one SQL Sever instance, it’s simple to extend this to hundreds.

So, to begin with a single instace:

#Connect to our SQL Sever Instance:
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("MyServer\Instance1")

#As this is a new job, we create a new object and then create it. If you look on your SQL instance now you'll see a job without steps, schedules or notifications
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $SQLSvr.JobServer, "Example_Job"
$SQLJob.Create()

#Now we add a step to our Job so it will actually do some work. Again as this is a new job step,
$SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step"
#For this example, we'll do something simple and foolproof........
$SQLJobStep.Command = "select * from sys.databases"
$SQLJobStep.DatabaseName = "master"
$SQLJobStep.Create()

#Now add a schedule to our job to finish it off
$SQLJobSchedule =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $SQLJob, "Example_Job_Schedule"

#Need to use the built in types for Frequency, in this case we'll run it every day
$SQLJobSchedule.FrequencyTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
#As we've picked daily, this will repeat every day
$SQLJobSchedule.FrequencyInterval = 1

#Need to tell SQL when during the day we want to acutally run it. This is a timespan base on 00:00:00 as the start,
#Here we're saying to run it at 13:30. You could combine these lines, but I've left them seperate to make it easier to read.
$TimeSpan1 = New-TimeSpan -hours 13 -minutes 30
$SQLJobSchedule.ActiveStartTimeofDay = $TimeSpan1

#Set the job to be active from now
$SQLJobSchedule.ActiveStartDate = get-date
$SQLJobSchedule.create()

And there we have it, a nice simple job built on our SQL Server instance. Now, what happens if we wanted to push that out to a whole load of instances. That’s easy, assuming you’ve a reliable list of your SQL instances lying around:

$ServerList = Get-Content c:\ListOfServers.txt
foreach ($srv in $ServerList)
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($srv)

    $SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist    $SQLSvr.JobServer, "Example_Job"
    $SQLJob.Create()

    $SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step"
    $SQLJobStep.Command = "select * from sys.databases"
    $SQLJobStep.DatabaseName = "master"
    $SQLJobStep.Create()

    $SQLJobSchedule =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $SQLJob, "Example_Job_Schedule"

    $SQLJobSchedule.FrequencyTypes =  "Daily"
    $SQLJobSchedule.FrequencyInterval = 1

    $TimeSpan1 = New-TimeSpan -hours 13 -minutes 30
    $SQLJobSchedule.ActiveStartTimeofDay = $TimeSpan1

    $SQLJobSchedule.ActiveStartDate = get-date
    $SQLJobSchedule.create()
}

And you’ve just deployed an identical SQL Server Agent job to every machine in your list. Suddenly that rush job doesn’t look quite so much like a right click hell.

But, what happens when the spec changes (because the requirements are always right the first time aren’t they?). Well, we can modify our jobs as well. In this example we’re going to change the T-SQL that’s going to be run, add another Job Step, and put in some basic control logic

Again, we’ll start with a single server instance:

#Connect to our instance
$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("MyServer\Instance1")

#As our job already exists, we can just assign it
$SQLJob = $sqlsvr.JobServer.jobs["Example_Job"]

#Now we modify our step to our Job. Again as this step already exists we can just assign it rather than creating a new object
$SQLJobStep = $sqljob.JobSteps["Example_Job_Step"]
$SQLJobStep.Command = "select * from sys.master_files"
$SQLJobStep.DatabaseName = "master"
$SQLJobStep.alter()

#Now we add a 2nd job step to our job. As this is a new step, we create a new object
$SQLJobStep2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step2"
$SQLJobStep2.Command = "select * from sys.databases"
$SQLJobStep2.DatabaseName = "master"
$SQLJobStep2.Create()

#Now we have multiple steps, we need to tell SQL Server Agent what to do when things succeed or fail
$SQLJobStep.OnSuccessAction = "GoToStep"
$SQLJobStep.OnSuccessStep=2
$SQLJobStep.OnFailAction = "QuitWithFailure"

#Finally we'll alter our job to save the changes
$SQLJobStep.Alter()

And again, applying that to a number of SQL instances, is as simple as wrapping it in a foreach loop:

$ServerList = Get-Content c:\ListOfServers.txt
foreach ($srv in $ServerList)
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($srv)

    $SQLJob = $sqlsvr.JobServer.jobs["Example_Job"]

    $SQLJobStep = $sqljob.JobSteps["Example_Job_Step"]
    $SQLJobStep.Command = "select * from sys.master_files"
    $SQLJobStep.DatabaseName = "master"
    $SQLJobStep.alter()

    $SQLJobStep2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $SQLJob, "Example_Job_Step2"
    $SQLJobStep2.Command = "select * from sys.databases"
    $SQLJobStep2.DatabaseName = "master"
    $SQLJobStep2.Create()

    $SQLJobStep.OnSuccessAction = "GoToStep"
    $SQLJobStep.OnSuccessStep=2
    $SQLJobStep.OnFailAction = "QuitWithFailure"

    $SQLJobStep.Alter()
}

And there we go. A nice simple way of adding complex multi-step SQL agent jobs to lots of servers with minimal effort.

Previous

SQL Saturday Exeter 2014 Redux

Next

Deleting Large amounts of data in SQL Server – Part 1

4 Comments

  1. Stuart, I get that you can do it this way, I’m just not sure you should? Wouldn’t it be cleaner to create the job on an instance, test it, then script it out – and then just execute that script on however many instances? That part could be done using Powershell, or as I often do, via the Central Management Server when it’s not a huge number of instances to hit.

    • Stuart Moore

      Hi Andy,
      Thanks for dropping by.

      PowerShell via SMO just generates T-SQL statements under the hood anyway (change the create() to script() and it’ll dump it out for you). So this is just skipping the stage where you build it through SSMS and then script it out (SSMS is just calling SMO under the hood as well), you could also alter this script to pull your good testing job config from a testing server and then deploy that out automatically.

      It’s one of those cases where there is no ‘one’ way to do something. I like having lots of tools in my toolbox as I find that sometimes a problem is made simpler if you can pick the right one for the job at hand. You could use PowerShell to run the SQL commands, but I find a hybrid version can be very hard to debug and parse. To me this is much easier to read through and immediately see, for instance, what the schedule is intended to be, rather than:

      sp_add_jobschedule @job_name='Example_job', @enabled=1, @freq_type=4, @freq_interval=1, @active_start_time=133000
      

      Extending this within PowerShell also makes it very easy to find jobs where someone’s tweaked it locally and report back before resetting the schedule for example, or even drop out different versions of the same job to different versions of SQL Server (and to that extent, SMO will handle the slight changes to job creation syntax for you as long as you don’t try to backport features to older versions).

      You’ve also preempted the post I’m planning for when I’m back from Norway there, stepping through your CMS with PowerShell to skip having to use text files 😉

      • Mark gribler

        Hi i sm a consultant and this is brilliant!
        I have been asked by a friend how to automate the enterprise using CMS and MSX
        Powershell is probably the most elegant way where we can use an inventory db and ps app or leverage the CMS
        Should we use the master msx tsx microsoft way with SMO or not?

        I would use ps but the maintenance may be an issue but we could do the monitoring security etc as well as linking in the policies for security

        Is ps the right tool for a non programmer to support?

        • Stuart Moore

          A lot depends on the homogeneity of you environment I find. If you’re running large numbers of SQL instance and they’re all at the same version and built to a good template then MSX/TSX can work well as there’ll be nothing unexpected.

          If it’s not then I find PS is better. It’s much easier to drop different versions of scripts out to different versions of SQL Server. There’s also the option to pull in more configuration options, for instance you can have a csv of runtimes for machines that have a different load patterns so that jobs don’t conflict, something that’s a bit tricky with MSX/TSX. I also prefer the error handling for complex tasks with PS, many more options to catch things, report them, or to do something different to workaround.

          Personal opinion is that anyone who wants to get ahead with MS products or protect their career really needs to add PowerShell to their skillset. But not everyone shares that, but if scripts are well written (ie, no shortcuts or confusing PS abbreviations) with lots of documentaion (I’ve been encouraging people to think about developing using Help-Drive Development (June Blender discusses here https://github.com/juneb/PesterTDD)) then they should ease people into supporting them

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén