Create a Contained SQL Server Agent Job

As of the writing of this blog, contained SQL Server Agent jobs can only be created using Transact-SQL. If this changes, I will update the blog post accordingly.

Remember per my other blog post, the key is to do this in context of the contained AG. An example is shown in Figure 1.

Figure 1. In context of the contained AG.

The job I created inserts a random character every minute into the table TestTbl.

USE ContainedRobotoAG_msdb;
GO

EXEC sp_add_job
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl'; 
GO

EXEC sp_add_jobstep
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl',
   @step_name = N'Insert Value'
   @step_id = 1,
   @subsystem = 'TSQL',
   @database_name = N'ContainedAGDB1',
   @command = N'INSERT INTO TestTbl (TestVal) VALUES (CHAR (ROUND(RAND() * 93 + 33,0)))',
   @on_success_action = 1,
   @on_fail_action = 2,
   @retry_attempts = 5,
   @retry_interval = 5;
GO

DECLARE @schedule_id int;
EXEC sp_add_schedule
   @schedule_name = N'Every Minute',
   @enabled = 1,
   @freq_type = 4,
   @freq_interval = 1,
   @freq_subday_type = 4,
   @freq_subday_interval = 1,
   @freq_relative_interval = 0,
   @freq_recurrence_factor = 1,
   @active_start_date = 20220913,
   @active_end_date = 99991231,
   @active_start_time = 0,
   @active_end_time = 235959,
   @schedule_id = @schedule_id OUTPUT
SELECT @schedule_id;
GO

EXEC sp_attach_schedule
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl',
   @schedule_name = N'Every Minute';
GO

EXEC sp_add_jobserver
   @job_name = N'Insert Value Into ContainedRobotoAG TestTbl';
GO

The Challenge of Contained SQL Server Agent Jobs

Just like with logins, the SQL Server Agent job is not visible in SSMS. Like creation right now, it is just Transact-SQL. An example is shown in Figure 2.

Figure 2. Contained SQL Server Agent jobThis has some nasty side effects.

Someone could create a duplicate job at the instance (not AG) level. I tested this – see Figure 3. It’s not only doable, but the possibilities of how that can go wrong is endless.
Figure 3. Duplicate jobs – one in the contained AG, one at the instance level
The only way right now to see if a contained Job ran successfully (one time or recurring) is via T-SQL. See Figure 4 for an example. Many admins have scripts as part of their processes (hello DevOps folks!), but I know just as many or more who rely on SSMS to see the status of a job. What if a backup job created in context of a contained AG is failing and no one catches it because it’s not visible?
Figure 4. Job status
Somewhat associated with #1, I see a potential security hole here. What if someone creates a nefarious SQL Server Agent job in a contained database which does nasty things and no one knows about it and what it is doing? The implications – especially because Agent jobs can be more than just T-SQL – are not good.

The Bottom Line

I still like contained AGs, but this post highlights the biggest blind spot and challenge with them: administration. My hope is that SSMS is updated by the time SQL Server 2022 is released to make them not only easier to create and manage, but make SQL Server more secure.

Contained SQL Server Agent Jobs in SQL Server 2022

Create a Contained SQL Server Agent Job

The Challenge of Contained SQL Server Agent Jobs

The Bottom Line

Categories