Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Agent Job Deployment with SMO


By:   |   Updated: 2015-10-22   |   Comments (1)   |   Related: More > SQL Server Agent

Problem

As a DBA you may have to deploy SQL Server Agent Lobs to all of your instances. If you haven't implemented Central Management Servers (CMS) where you have a master SQL Server Agent targeting multiple instances, job deployment may be a mundane task. Check out this tip for a very reliable and flexible way to do deploy SQL Server Agent Jobs via SMO scripting.

Solution

From my previous tip, I have described how you can script out all your SQL Server database objects. We will use the same principle and solution from this tip, so you can deploy your SQL Server Agent Jobs across multiple servers. It's really simple. Just follow the steps on creating a new console application up to the point that you are looping thru your database server list.

The SQL Server Agent Job that I want to deploy to all my instances is a health check job. It has the following job steps:

  1. Via the sqlcmd utility, the job will execute dailyhealthscript.sql that I placed in a shared folder. Then after executing the script, a log report text file is dumped to a shared folder that I created. Beforehand, I granted access to all of my SQL Server service accounts to these shared folders.

  2. Send email notification to our DBA email distribution list. I also enabled my database mail for all of my servers for the email notification. If you need help enabling database mail, see this link.

I have also prepared a job schedule to run daily at 6:00 AM. This is the job schedule that we will be created when we deploy the job.

With the above job steps, I created the following code:

   
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;

namespace Deploy_Job{

public class CreateJob
{
        public void CreateHealthCheckJob()
        {
        ServerConnection conn = new ServerConnection();
        conn.LoginSecure = true;
        conn.ServerInstance = "MSSQLSERVER"; //your instance name here
        Server srv = new Server(conn);

        Job job = new Job(srv.JobServer, "[Monitoring] Daily Morning Health Check");
        job.IsEnabled = true;
        job.Category = "Database Maintenance";
        job.Description = "Job to automate health check report summary";
        job.OwnerLoginName = "sa";

        JobStep jobStep = new JobStep(job, "Monitoring - Daily Morning Health Check - Run Script");
        jobStep.SubSystem = AgentSubSystem.CmdExec;
        jobStep.Command = @"sqlcmd -i DailyMorningHealthCheckScript.sql -o C:\HealthCheckReports\DailyMorningHealthCheckReport.txt";
        jobStep.OnSuccessAction = StepCompletionAction.GoToNextStep;
        jobStep.OnFailAction = StepCompletionAction.QuitWithFailure;
        jobStep.RetryAttempts = 0;

        JobStep js2 = new JobStep(job, "Send Health Check Report to DBA Team");
        js2.SubSystem = AgentSubSystem.TransactSql;
        js2.Command = @"EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Database Email Alert',
            @recipients = '[email protected]',
            @body = 'Please see attached Health Check Monitoring Report from server.',
            @subject = 'Daily Health Check Monitoring Report.'";

        js2.DatabaseName = "msdb";
        js2.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
        js2.OnFailAction = StepCompletionAction.QuitWithFailure;
        js2.RetryAttempts = 0;

        TimeSpan ts = new TimeSpan(6, 0, 0);

        JobSchedule jSked = new JobSchedule(srv.JobServer, "Monday to Friday at 6AM");
        jSked.FrequencyInterval = 1;
        jSked.FrequencyTypes = FrequencyTypes.Daily;
        jSked.FrequencySubDayTypes = FrequencySubDayTypes.Once;
        jSked.ActiveStartDate = DateTime.Today;
        jSked.ActiveStartTimeOfDay = ts;
        jSked.IsEnabled = true;
        jSked.Create();

        job.JobSteps.Add(jobStep);
        job.JobSteps.Add(js2);
        job.Create();
        job.AddSharedSchedule(jSked.ID);
        jobStep.Create();
        js2.Create();

        srv.ConnectionContext.Disconnect();
        Console.WriteLine(job.Name + " in " + conn.ServerInstance + " created");
    }
}}

Copy the above code into the new class you created in your project. Go to Project > Build to build the project file and create the executable file. Go to the project bin folder and you should be able to locate your new executable file. Open a cmd window and execute your executable file. Make sure the account you are using to execute the script has msdb permissions to create the SQL Server Agent Job.

To verify, check each database server in your server list to verify the SQL Server Agent Job and Job Schedule was successfully created. Wait for the next scheduled run and verify the results in the output folder you have specified in the code above.

Viola, you have automated your deployment and you have more time to learn more about SQL Server or coffee time!

Next Steps
  • You can create a project template on the same principle so you can use it as a way to deploy SQL Server Agent Jobs across multiple instances. There's a lot you can do with SMO objects and this project template.
  • For more articles on SQL Server Management Objects, visit this link.
  • For more tips on SQL Multi Server Administration, visit this link.


Last Updated: 2015-10-22


get scripts

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, March 28, 2016 - 5:31:05 PM - Ben Ramsey Back To Top

 Carla,

 

Great Article.

 

Can you give an overview of the items you included in the SQL script (DailyMorningHealthCheckScript.sql) that you reference?

jobStep.Command = @"sqlcmd -i DailyMorningHealthCheckScript.sql -o C:\HealthCheckReports\DailyMorningHealthCheckReport.txt

 

 


Learn more about SQL Server tools