Automate SQL Agent Job Deployment with SMO

By: Carla Abanes   |   Comments (1)   |   Related: > 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.

About the author

Carla Abanes is a certified Microsoft SQL Server Database Administrator with extensive experience in data architecture and supporting mission critical databases in production environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips