Using SQL Agent Job Categories to Automate SQL Agent Job Enabling with Database Mirroring

In SQL Server 2005, 2008 and 2008 R2 Database Mirroring works at the individual database level.  One challenge to using database mirroring is to keep your important SQL Agent jobs enabled or disabled depending on a database’s current mirroring role. If a database is acting as a Principal, you will want the SQL Agent jobs associated with that database to be enabled, while if it is acting as a Mirror, you will want to disable the SQL Agent jobs associated with that database. This does not happen automatically without a little bit of preparation.


Any SQL Agent job that tries to access a database while it is acting as a Mirror will fail. It is a common best practice to script out database specific SQL Agent jobs from the instance where your databases normally run in the Principal role, and then create the same jobs on the instance where your databases are mirrored to, keeping them disabled until they are needed. If you have a planned or unplanned database mirroring failover you should consider having an automated method of enabling the Agent jobs on the Principal side of the mirror and disabling the Agent jobs on the Mirror side of the mirror.


One way to help facilitate this is to add your own custom job categories for SQL Agent Jobs, so you can group SQL Agent jobs by database. SQL Server Management Studio (SSMS) has a GUI that lets you add or delete job categories. You can get to this by right-clicking on “Jobs” under SQL Server Agent, and selecting “Manage Job Categories”.


For this article, a customer environment is used. That customer is NewsGator (www.newsgator.com).


clip_image002


Information about SQL Agent jobs is stored in the msdb system database. Inside of the msdb database, you will find job information in the sysjobs table and category information in the syscategories table. You can look at your current job and category information with this query:


— Get Agent jobs and Category information


SELECT sj.name AS [JobName], [enabled], sj.category_id,


sc.name AS [CategoryName]


FROM msdb.dbo.sysjobs AS sj


INNER JOIN msdb.dbo.syscategories AS sc


ON sj.category_id = sc.category_id


ORDER BY sj.name;


It is a best practice to create a custom Job Category that has the same name as each user database that is mirrored. You should also create a Job Category called “Instance Level Job” for any Agent jobs that are not database specific. After you have created the new Job Categories, you need to assign each Agent job to the correct category. You can do this by right-clicking on a job and selecting “Properties”. You can select the correct category in the Categories combo-box. You will want assign each database specific Agent job to the correct category. Don’t forget user database backup jobs (both full and transaction log), because even though they operate in the context of the master database, they will fail if they are run against a database that is in the mirror role.


clip_image004


Another best practice is to create a database called ServerMonitor on each SQL Server instance. This is a database that can be used to run things like instance level DMV queries. This database is not mirrored by design, since we want a database that will always be available on the instance, regardless of the mirroring role status of the other user databases.


Add a stored procedure to the ServerMonitor database called DBAdminCheckMirroringStatus. This stored procedure checks the mirroring_role for each mirrored database, and then calls another stored procedure called DBAdminChangeJobStatus that either enables or disables the SQL Agent jobs that are assigned to that databases job category. Then have another SQL Agent job run this DBAdminCheckMirroringStatus stored procedure every minute (or less often, depending on your business requirements). This will take care of keeping your SQL Agent jobs enabled on the Principal side of the mirror as needed. The important thing to remember is that these stored procedure need to be run on both servers. It is equally important to turn on the jobs on the new principal when the failover occurs as well as turn off all the jobs on the original principal.



USE [ServerMonitor]


GO


 


 


SET ANSI_NULLS OFF


GO


SET QUOTED_IDENTIFIER OFF


GO


 


 


/* DBAdminChangeJobStatus =============================================================================


Description : Change Agent job status for all jobs in a Category             


                                   


Used By: Only used to monitor the database instance              


 


Last Modified           Developer         Description


—————————————————————————–


===========================================================================*/


CREATE PROCEDURE [dbo].[DBAdminChangeJobStatus]


(@CategoryID int,


 @CurrentEnabledValue tinyint,


 @NewEnabledValue tinyint)


AS


 


      SET NOCOUNT ON;


     


      DECLARE @JobName nvarchar(128);


     


      — Declare cursor


      DECLARE curJobNameList CURSOR


      FAST_FORWARD


      FOR


           


            SELECT [name]


            FROM msdb.dbo.sysjobs


            WHERE category_id = @CategoryID


            AND [enabled] = @CurrentEnabledValue;


                 


            OPEN curJobNameList;


 


            FETCH NEXT


            FROM curJobNameList


            INTO @JobName;


 


            WHILE @@FETCH_STATUS = 0


                  BEGIN


 


                        EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled = @NewEnabledValue;


 


                        FETCH NEXT


                        FROM curJobNameList


                        INTO @JobName;


 


                  END


 


            CLOSE curJobNameList;


            DEALLOCATE curJobNameList;


     


      RETURN;


 


GO


 


 


SET ANSI_NULLS OFF


GO


 


SET QUOTED_IDENTIFIER OFF


GO


 


 


/* DBAdminCheckMirroringStatus ==========================================================================


Description : Get database mirroring status for all mirrored databases and change Agent job status if needed


                                   


Used By: Only used to monitor the database instance              


 


Last Modified           Developer         Description


—————————————————————————–


=============================================================================


*/


CREATE PROCEDURE [dbo].[DBAdminCheckMirroringStatus]


AS


 


      SET NOCOUNT ON;


     


      DECLARE @DatabaseName nvarchar(128);


      DECLARE @MirroringRole tinyint = 0;


      DECLARE @CategoryID int = 0;


     


      DECLARE curDatabaseNameList CURSOR


      FAST_FORWARD


      FOR


           


            — Get list of all mirrored databases


            SELECT DB_NAME(database_id) AS [DatabaseName]


            FROM sys.database_mirroring


            WHERE database_id > 4


            AND NOT mirroring_role IS NULL;


                 


            OPEN curDatabaseNameList;


 


            FETCH NEXT


            FROM curDatabaseNameList


            INTO @DatabaseName;


 


            WHILE @@FETCH_STATUS = 0


                  BEGIN


                 


                        — Get the CategoryID for the CategoryName that matches the DatabaseName


                        SET @CategoryID = (SELECT TOP(1) sj.category_id


                              FROM msdb.dbo.sysjobs AS sj


                                   INNER JOIN msdb.dbo.syscategories AS sc


                                           ON sj.category_id = sc.category_id


                                           WHERE sc.name = @DatabaseName


                                           ORDER BY sj.category_id);


 


                       


                        IF @CategoryID > 0


                              BEGIN


                                    — Get mirroring role for database


                              SET @MirroringRole =


(SELECT mirroring_role


                                    FROM sys.database_mirroring


                                    WHERE DB_NAME(database_id) =


 @DatabaseName);


                                   


                                    IF @MirroringRole = 1  — Principal


                                    BEGIN


                                          — Enable all jobs in this Category that are disabled


                                          EXEC dbo.DBAdminChangeJobStatus


                                                @CategoryID, 0, 1;


                                    END


                                    IF @MirroringRole = 2 — Mirror


                                    BEGIN


                                          — Disable all jobs in this Category that are enabled


                                          EXEC dbo.DBAdminChangeJobStatus


                                          @CategoryID, 1, 0;


                                    END


                              END


                             


                        SET @CategoryID = 0;


                       


                        FETCH NEXT


                        FROM curDatabaseNameList


                        INTO @DatabaseName;


 


                  END


 


            CLOSE curDatabaseNameList;


            DEALLOCATE curDatabaseNameList;


     


      RETURN;


GO


AUTHORS:  Glenn Berry, Newsgator; Kevin Cox SQLCAT

Reviewers:  Kun Cheng