AlwaysOn Part 10: Switching Scheduled tasks automatically
As we talked about leveraging Log-Shipping together with AlwaysOn in Part #10, it became apparent that one would need to enable or disable scheduled tasks automatically on the primary and secondary replica in order to:
· Not flood the history files of the job with error messages telling that a backup can’t be taken from the secondary. Please note: Log-Shipping does allow to take Tlog backups from the primary replica only!!
· Have the Tlog backup job enabled after a role change or failover.
However this case is not limited to Log-shipping only. Let’s think about jobs like eventual consistency checks that are scheduled. Jobs that extract some monitoring data or perform some update statistics on specific tables. These usually fail if those run on a secondary. Hence a broader solution to automatically switch scheduled jobs/tasks from enabled to disabled is necessary.
We introduced such a solution for DBM already
In an earlier article on this blog, we introduced the very same problem and a solution for DBM already. The article can be found here: https://blogs.msdn.com/b/saponsqlserver/archive/2008/08/23/dealing-with-scheduled-tasks-in-a-sql-server-environment-using-database-mirroring.aspx
What is the solution for AlwaysOn?
Not too surprising the solution looks exactly the same and is based on the same three pillars again
· For scheduled tasks which need to be enabled/disabled based on whether they need to run on the primary and secondary, we are introducing a naming convention. A prefix of ‘<DB Name>_PRI_’ for those which need to be active on the primary replica, but inactive on the secondary replica(s). ‘<DB Name>_SEC_’ for those which need to be active on the secondary replica, but inactive on the primary. Having the database name in the prefix is new to this solution since we can have multiple AGs per instance where one AG might run on the server on primary role and others in secondary role.
· This certainly requires to have the same jobs created with the same names on the instances running primary and secondary replicas. This so far is a manual process, which either happens by creating those configurations (like in Part #10 when establishing Log-Shipping) or where one needs to script certain jobs on the primary in order to recreate those on the secondary side.
· The stored procedure is created in msdb and will be scheduled to check every n minutes (recommended 5 minutes). It then will check which role the current instance is running. The job then would adapt the job/task status accordingly to the role the instance is running. If there are multiple AGs running on the same instances, one would setup several of those jobs using that stored procedure and give one of the databases in the AG as call parameter to the stored procedure.
We adapted the procedure a bit for AlwaysOn where the notion of ‘Online’ as in this query:
select name, state_desc from sys.databases
changed in its results between Database Mirroring and AlwaysOn. In Database Mirroring the state_desc column would come back with the value ‘RESTORING’ when the query was issued against the instance running the mirror. However with AlwaysOn the state_desc columns will return the value ‘ONLINE’ even for the case that the instance is running a secondary replica of the database. Reason for this change was that a secondary replica in principle is online, despite the fact that one might not be able to read from it. Therefore we are taking as a distinction between primary and secondary role, the indication delivered in the DMV sys.dm_hadr_availability_replica_states in column role_desc. This column will either have the values ‘RESOLVING’, ‘PRIMARY’ and ‘SECONDARY’.
If the value is RESOLVING or any other value not PRIMARY or SECONDARY the stored procedure will print a message and exit.
If the role is PRIMARY, the procedure will enable all jobs which match the prefix of database name and ‘_PRI’. The database name is an input parameter and is used throughout the stored procedure. Hence it is obvious that the name of the database submitted to the stored procedure needs to match the upper/lower case writing, especially when the collation is case sensitive.
Additionally eventual Log-Shipping alerts will be enabled or disabled.
Please find the code of the stored procedure below. Many thanks to Eric Holling form Microsoft IT for continuously improving this stored procedure.
drop procedure sp_SAP_auto_job_status_toggle_HADRON
go
create procedure sp_SAP_auto_job_status_toggle_HADRON
(@dbname sysname)
as
--FOR ALWAYSON ROLE CHANGE, IF DATABASE NOW THE PRIMARY/PRINCIPAL,
--THEN THE FOLLOWING IS DONE:
--ENABLES AGENT JOBS WITH NAMES LIKE '<DB_Name>_PRI%'
--DISABLES AGENT JOBS WITH NAMES LIKE '<DB_Name>_SEC%'
--ENABLES LOG SHIPPING PRIMARY ROLE ALERTING
--FOR ALWASYON ROLE CHANGE, IF DATABASE NOW THE SECONDARY/MIRROR,
--THEN THE FOLLOWING IS DONE:
--DISABLES AGENT JOBS WITH NAMES LIKE '<DB_Name>_PRI%'
--ENABLES AGENT JOBS WITH NAMES LIKE '<DB_Name>_SEC%'
--DISABLES LOG SHIPPING PRIMARY ROLE ALERTING
set nocount on;
declare @jobid uniqueidentifier;
declare @jobprefix_pri sysname;
declare @jobprefix_sec sysname;
declare @jobname sysname;
declare @dbstate_desc nvarchar(120);
declare @HADRON_role_state nvarchar(120);
declare @db_role nvarchar(120);
select @HADRON_role_state=ars.role_desc from master.sys.dm_hadr_availability_replica_states ars, master.sys.availability_databases_cluster dc
where ars.group_id=dc.group_id and ars.is_local=1 and dc.database_name=@dbname;
set @jobprefix_pri = @dbname + '_PRI%';
set @jobprefix_sec = @dbname + '_SEC%';
if @HADRON_role_state in ('PRIMARY','SECONDARY')
select @db_role=@HADRON_role_state;
else
begin
if @HADRON_role_state = 'RESOLVING'
begin
print 'AlwaysOn role currently in RESOLVING state - please try again later';
return(-2)
end
else
begin
print 'AlwaysOn role could not be identified - Make sure that database is part of an AG';
return (-1);
end
end
--Now let's check for the jobs
if @db_role = 'PRIMARY'
begin --Database is ONLINE
if exists (select * from msdb..sysjobs where name like @jobprefix_pri and enabled=0) or
exists (select * from msdb..sysjobs where name like @jobprefix_sec and enabled=1) or
exists (select * from msdb..log_shipping_monitor_primary where threshold_alert_enabled<>1 and primary_database=@dbname) or
exists (select * from msdb..log_shipping_monitor_secondary where threshold_alert_enabled<>0 and secondary_database=@dbname)
begin
print getdate();
print 'Database ONLINE; Need to enable/disable jobs due to role change...';
declare job_cursor cursor for
select job_id, name from msdb..sysjobs where name like @jobprefix_pri and enabled=0;
open job_cursor;
fetch next from job_cursor into @jobid, @jobname;
while @@fetch_status=0
begin
exec msdb..sp_update_job @job_id=@jobid , @enabled=1;
print 'Need to enable the following PRI jobs: ' + @jobname;
fetch next from job_cursor into @jobid, @jobname;
end
close job_cursor;
deallocate job_cursor;
--Get SEC jobs
declare job_cursor cursor for
select job_id, name from msdb..sysjobs where name like @jobprefix_sec and enabled=1;
open job_cursor;
fetch next from job_cursor into @jobid, @jobname;
while @@fetch_status=0
begin
exec msdb..sp_update_job @job_id=@jobid , @enabled=0;
print 'Need to disable the following SEC jobs: ' + @jobname;
fetch next from job_cursor into @jobid, @jobname;
end
close job_cursor;
deallocate job_cursor;
if exists (select * from msdb..log_shipping_monitor_primary where primary_database=@dbname and threshold_alert_enabled<>1)
begin
--Enable Log Shipping Primary role alerting for the database
print 'Enabling log shipping Primary role alerting...';
update msdb..log_shipping_monitor_primary set threshold_alert_enabled=1 where primary_database=@dbname;
end
if exists (select * from msdb..log_shipping_monitor_secondary where secondary_database=@dbname and threshold_alert_enabled<>0)
begin
--Disable Log Shipping Secondary role alerting for the database
print 'Disabling log shipping Secondary role alerting...';
update msdb..log_shipping_monitor_secondary set threshold_alert_enabled=0 where secondary_database=@dbname;
end
end
end
else
if @db_role = 'SECONDARY'
begin --Database RESTORING
if exists (select * from msdb..sysjobs where name like @jobprefix_pri and enabled=1) or
exists (select * from msdb..sysjobs where name like @jobprefix_sec and enabled=0) or
exists (select * from msdb..log_shipping_monitor_primary where threshold_alert_enabled<>0 and primary_database=@dbname) or
exists (select * from msdb..log_shipping_monitor_secondary where threshold_alert_enabled<>1 and secondary_database=@dbname)
begin -- Need to enable/disable jobs due to role change
print getdate();
declare job_cursor cursor for
select job_id, name from msdb..sysjobs where name like @jobprefix_pri and enabled=1;
open job_cursor;
fetch next from job_cursor into @jobid, @jobname;
while @@fetch_status=0
begin
exec msdb..sp_update_job @job_id=@jobid , @enabled=0;
print 'Need to enable the following PRI jobs: ' + @jobname
fetch next from job_cursor into @jobid,@jobname;
end
close job_cursor;
deallocate job_cursor;
declare job_cursor cursor for
select job_id, name from msdb..sysjobs where name like @jobprefix_sec and enabled=0;
open job_cursor;
fetch next from job_cursor into @jobid,@jobname;
while @@fetch_status=0
begin
exec msdb..sp_update_job @job_id=@jobid , @enabled=1;
print 'Need to enable the following SEC jobs: ' + @jobname;
fetch next from job_cursor into @jobid,@jobname;
end
close job_cursor;
deallocate job_cursor;
if exists (select * from msdb..log_shipping_monitor_primary where primary_database=@dbname and threshold_alert_enabled<>0)
begin
--disable Log Shipping Primary role alerting for the database
print 'Disabling log shipping Primary role alerting...';
update msdb..log_shipping_monitor_primary set threshold_alert_enabled=0 where primary_database=@dbname;
end
if exists (select * from msdb..log_shipping_monitor_secondary where secondary_database='E64' and threshold_alert_enabled<>1)
begin
-- enable Log Shipping Secondary role alerting
print 'Enabling log shipping Secondary role alerting...';
update msdb..log_shipping_monitor_secondary set threshold_alert_enabled=1 where secondary_database=@dbname;
end
end
end
Comments
- Anonymous
February 23, 2014
Hi, I have been trying out the stored procedure, but I'm always getting exit code 0. We are using scenario 2, i.e. Always On in our main datacenter, with a Log shipping configuration to a remote DR site. It is very difficult to find some relevant information on a setup combining AO + LS. Do you know if anyone has successfully implemented this stored procedure? Regards, Stijn