September 21, 2015 at 10:07 am
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups..
How can I make a scheduled SQL agent job aware of a node switch? For example I have a job running on the primary node which loads data each hour. Now if the primary goes down, how can I activate the job on the secondary which now becomes primary?
If I schedule the job always on the secondary it fails because then the secondary is read-only.
September 21, 2015 at 11:24 am
Check out the function fn_hadr_backup_is_primary. That will tell you if your server is currently the primary. Then, you enable the jobs on all the servers, but it will only run on the current primary server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2015 at 12:07 pm
Thank you for your reply. That function is only available in SQL 2014/2016. Do you know what function would work in 2012?
Shelley
September 21, 2015 at 12:23 pm
I do not have a 2012 instance to confirm this, but I think you can query dm_hadr_availability_group_states, you should be able to check the name of the primary_replica for the group_id. The view does exist in 2012. https://msdn.microsoft.com/en-us/library/ff878491(v=sql.110).aspx
September 21, 2015 at 1:05 pm
thank you. I found the below and it works by returning role_desc = secondary
SELECT AGS.name AS AGGroupName,
AR.replica_server_name AS InstanceName,
HARS.role_desc,
Db_name(DRS.database_id) AS DBName,
DRS.database_id,
AR.availability_mode_desc AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
DRS.last_hardened_lsn,
DRS.end_of_log_lsn,
DRS.last_redone_lsn,
DRS.last_hardened_time,
DRS.last_redone_time,
DRS.log_send_queue_size,
DRS.redo_queue_size
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
ORDER BY AGS.name,
AR.replica_server_name,
Db_name(DRS.database_id)
what I'm wanting to do.
I have a job that runs on the primary cluster and I want to set the same job to run on the secondary server.
SELECT AGS.name AS AGGroupName,
AR.replica_server_name AS InstanceName,
HARS.role_desc,
Db_name(DRS.database_id) AS DBName,
DRS.database_id,
AR.availability_mode_desc AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
DRS.last_hardened_lsn,
DRS.end_of_log_lsn,
DRS.last_redone_lsn,
DRS.last_hardened_time,
DRS.last_redone_time,
DRS.log_send_queue_size,
DRS.redo_queue_size
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
ORDER BY AGS.name,
AR.replica_server_name,
Db_name(DRS.database_id)
Go
If role_desc = 'secondary'
and db name = 'dbname'
Do nothing....exit
IF @RoleDesc = 'PRIMARY'
BEGIN
use dbname
--turn off messages
set nocount on
declare @people table (
[index] int identity,
[username] nvarchar(50),
[userId] int,
nvarchar(60),
[ssn] nvarchar(12),
[billpay] int,
[estatement] int
);
insert
into @people
select distinct
lower([userLogon].loginName) as [username],
.userId as [userId], .emailaddress as , .ssn as [ssn],
(select count(*) from [q2_thirdPartyData] [third] WHERE .userid = [third].userid) as [billpay],
(select count(*) from [q2_userAccount] join [q2_estatement] on [q2_estatement].hostAccountId = [q2_userAccount].hostAccountId where [q2_userAccount].userId = .userid and [q2_estatement].optIn = 1) as [estatement]
from
[q2_user] as
join [q2_userLogon] as [userLogon] on .userId = [userLogon].userId
join [q2_userAccount] as [userAccount] on [userAccount].userId = .userId
join [q2_email] as on .defaultEmailId = .emailId
--join [q2_thirdPartyData] as [third] on .userid = [third].userid
where
--Only customers with a host account ID
[userLogon].deletedDate is null and
len([userLogon].loginName) < 32 and
[userLogon].loginName not like '%/1' and
.ssn is not null and
.ssn != '' and
.ssn != '000000000';
select [ssn], [username], ,[estatement], [billpay] from @people
END
NOTE: I'm not sure how to put that all together.
September 21, 2015 at 2:31 pm
Do you mean something like this?
DECLARE @DB_name NVARCHAR(128) = N'My_database_name';
IF EXISTS ( SELECT * FROM [sys].[dm_hadr_database_replica_states] drs
LEFT JOIN [sys].[dm_hadr_availability_replica_cluster_states] rcs ON drs.[replica_id] = rcs.replica_id
WHERE drs.database_id = db_id(@DB_name)
AND rcs.replica_server_name = @@SERVERNAME )
BEGIN
print 'Do Primary stuff '
END
ELSE IF EXISTS ( SELECT * FROM [sys].[dm_hadr_database_replica_states] drs
LEFT JOIN [sys].[dm_hadr_availability_replica_cluster_states] rcs ON drs.[replica_id] = rcs.replica_id
WHERE drs.database_id = db_id(@DB_name)
AND rcs.replica_server_name <> @@SERVERNAME )
BEGIN
print 'Do Secondary stuff'
END
September 21, 2015 at 3:20 pm
yes,
But if it's set to 'secondary'. How do I just exit? I don't want the job to run if the DB is marked 'secondary'
(Sorry. I'm new to writing transact SQL)
September 21, 2015 at 3:44 pm
Shelley Allen (9/21/2015)
yes,But if it's set to 'secondary'. How do I just exit? I don't want the job to run if the DB is marked 'secondary'
(Sorry. I'm new to writing transact SQL)
Do the check for primary and only run the code if that's true. Don't bother checking for secondary at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2015 at 8:18 am
ok, this works........ which is good but I don't want it to run if it's not primary. What am I missing?
DECLARE @DB_name NVARCHAR(128) = N'Q2DB_312102';
IF EXISTS ( SELECT * FROM [sys].[dm_hadr_database_replica_states] drs
LEFT JOIN [sys].[dm_hadr_availability_replica_cluster_states] rcs ON drs.[replica_id] = rcs.replica_id
WHERE drs.database_id = db_id(@DB_name)
AND rcs.replica_server_name = @@SERVERNAME )
BEGIN
print 'Server is Primary'
use DBNAME
--turn off messages
set nocount on
declare @people table (
[index] int identity,
[username] nvarchar(50),
[userId] int,
nvarchar(60),
[ssn] nvarchar(12),
[billpay] int,
[estatement] int
);
insert
into @people
select distinct
lower([userLogon].loginName) as [username],
.userId as [userId], .emailaddress as , .ssn as [ssn],
(select count(*) from [q2_thirdPartyData] [third] WHERE .userid = [third].userid) as [billpay],
(select count(*) from [q2_userAccount] join [q2_estatement] on [q2_estatement].hostAccountId = [q2_userAccount].hostAccountId where [q2_userAccount].userId = .userid and [q2_estatement].optIn = 1) as [estatement]
from
[q2_user] as
join [q2_userLogon] as [userLogon] on .userId = [userLogon].userId
join [q2_userAccount] as [userAccount] on [userAccount].userId = .userId
join [q2_email] as on .defaultEmailId = .emailId
--join [q2_thirdPartyData] as [third] on .userid = [third].userid
where
--Only customers with a host account ID
[userLogon].deletedDate is null and
len([userLogon].loginName) < 32 and
[userLogon].loginName not like '%/1' and
.ssn is not null and
.ssn != '' and
.ssn != '000000000';
select [ssn], [username], ,[estatement], [billpay] from @people
END
ELSE IF EXISTS ( SELECT * FROM [sys].[dm_hadr_database_replica_states] drs
LEFT JOIN [sys].[dm_hadr_availability_replica_cluster_states] rcs ON drs.[replica_id] = rcs.replica_id
WHERE drs.database_id = db_id(@DB_name)
AND rcs.replica_server_name <> @@SERVERNAME )
BEGIN
print 'Node Is set to Secondary-Job will exit'
END
September 22, 2015 at 8:55 am
These two links helps me out in scheduled jobs to ensure they only run on the primary
September 22, 2015 at 10:39 am
That worked! Thank you for much for helping.
One additional question. I have the function dbo.fn_hadr_database_is_primary running on the Primary and Secondary server. I have a job setup on each server. On server A, the job kicks off and does exactly what I want it to do. The job calls a sqlcmd that runs a script and copies output to a shared drive.
sqlcmd -E -S DBListnerer -i c:\scripts\312102.sql -o \\servername\sharename\output.rpt
I have the same job step on Server B (secondary server) that I want to run daily also, but as of now, it updates the output.rpt with 'Server isn't Primary' which doesn't run the script like I want, but I don't want an output period. Just exit the script w/ success'. Don't update the share rpt if not primary
September 22, 2015 at 1:02 pm
Thank you so much for taking the time to answer my questions. Adding the dbo.fn_hadr_database_is_primary function worked just liked I wanted.
Additional question now.
Server A and Server B both has the job running. Server A works, Server B works but when the job runs it updates with report with "Server is not Primary" (which is doing what I want within the script, I just don't want the output to the report. I want server B scheduled job to run but exit on success without updating anything.
Here is the job. Any ideas? Not sure what I'm missing.
type = Operating system (CmdExec)
Command:
sqlcmd -E -S OLBGL -i c:\scripts\script.sql -o \\servername\fileshare\output.rpt -n 2 255
that job calls the script: (which works on both A and B)
If master.dbo.fn_hadr_database_is_primary('databasename')=1
BEGIN
print 'Server is Primary'
use databasename
--turn off messages
set nocount on
declare @people table (
[index] int identity,
[username] nvarchar(50),
[userId] int,
nvarchar(60),
[ssn] nvarchar(12),
[billpay] int,
[estatement] int
);
insert
into @people
select distinct
lower([userLogon].loginName) as [username],
.userId as [userId], .emailaddress as , .ssn as [ssn],
(select count(*) from [q2_thirdPartyData] [third] WHERE .userid = [third].userid) as [billpay],
(select count(*) from [q2_userAccount] join [q2_estatement] on [q2_estatement].hostAccountId = [q2_userAccount].hostAccountId where [q2_userAccount].userId = .userid and [q2_estatement].optIn = 1) as [estatement]
from
[q2_user] as
join [q2_userLogon] as [userLogon] on .userId = [userLogon].userId
join [q2_userAccount] as [userAccount] on [userAccount].userId = .userId
join [q2_email] as on .defaultEmailId = .emailId
--join [q2_thirdPartyData] as [third] on .userid = [third].userid
where
--Only customers with a host account ID
[userLogon].deletedDate is null and
len([userLogon].loginName) < 32 and
[userLogon].loginName not like '%/1' and
.ssn is not null and
.ssn != '' and
.ssn != '000000000';
select [ssn], [username], ,[estatement], [billpay] from @people
END
ELSE BEGIN
PRINT 'Not Primary Replica'
END
September 23, 2015 at 6:25 am
This is my 1st step in every job on all nodes of my AG servers. When the function returns "NO", I raise an error and "quit job with success" in the job step "advanced properties". The function is a custom script I wrote, so you already have your script that returns essentially the same thing. Just exit your job with success.
IF msdb.[dbo].[fn_AmI_PrimaryReplica]('mydbname') = 'NO'
BEGIN
RAISERROR('Not Primary replica, exit job', 16, 1)
END
If the step returns "YES", the job continues to step 2,3,etc...
September 24, 2015 at 2:16 pm
I'm apologize for the continued questions. But first time handling a job like this.
It works on the Primary AG group. On the Secondary, when the job runs it updates the output to 1 KB and says Not Primary replica.
I don't want it to update anything if IF master.[dbo].[fn_hadr_database_is_primary]('OLBAG') <>0
September 25, 2015 at 5:08 am
Shelley Allen (9/24/2015)
I'm apologize for the continued questions. But first time handling a job like this.It works on the Primary AG group. On the Secondary, when the job runs it updates the output to 1 KB and says Not Primary replica.
I don't want it to update anything if IF master.[dbo].[fn_hadr_database_is_primary]('OLBAG') <>0
IF master.[dbo].[fn_hadr_database_is_primary]('OLBAG') <>0
BEGIN
RAISERROR('Not Primary replica, exit job', 16, 1)
END
--Raise an error and "quit the job reporting success" in the job step "advanced"
There may be a more graceful way of handling but this works just fine. This will exit your job at that step and not run anything else.
Users in this thread have answered your question 3 different ways. Maybe we're missing something, but it's pretty straight forward?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply