Contained SQL Server Agent Jobs in SQL Server 2022
This blog post is part of T-SQL Tuesday #154.
I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).
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.
