November 25, 2014 at 4:04 pm
I want two write a small script to determine which is the currently active (primary) server in the AG. Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?
November 26, 2014 at 3:09 am
Yes, the role changes. When you fail over from one database to another within your availability group, the active node switches. If you're scheduling activities on the databases, you'll want to have identical SQL Agent jobs on each server and the check to validate whether or not it's currently the active process.
"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
November 26, 2014 at 5:11 am
to check the currently active primary for a group use the following
selectgroup_id
, primary_replica
, primary_recovery_health
, primary_recovery_health_desc
, secondary_recovery_health
, secondary_recovery_health_desc
, synchronization_health
, synchronization_health_desc
from sys.dm_hadr_availability_group_states
Looking at the replica level will only show replica info, looking at the group level shows meta data aboyut that group including the current Primary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2014 at 9:39 am
So will this also work,
if (select ars.role
from sys.dm_hadr_availability_replica_states ars
where ars.is_local = 1) = 2
begin
raiserror ('This is not the primary replica.',2,1)
end
December 9, 2014 at 9:25 am
Sorry for the bump guys. Do you happen to know if the script I listed above will work? I want to implement this within the next day or two.
December 9, 2014 at 9:36 am
I don't currently have an availability group set up in order to validate your script.
"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
December 9, 2014 at 10:01 am
JoshDBGuy (11/26/2014)
So will this also work,
if (select ars.rolefrom sys.dm_hadr_availability_replica_states ars
where ars.is_local = 1) = 2
begin
raiserror ('This is not the primary replica.',2,1)
end
Work from where, where will this be running from? Did you read my post above, if you query at the replica level you'll need to query each replica. Querying the group will show the current replica name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 9, 2014 at 10:05 am
Perry Whittle (12/9/2014)
JoshDBGuy (11/26/2014)
So will this also work,
if (select ars.rolefrom sys.dm_hadr_availability_replica_states ars
where ars.is_local = 1) = 2
begin
raiserror ('This is not the primary replica.',2,1)
end
Work from where, where will this be running from? Did you read my post above, if you query at the replica level you'll need to query each replica. Querying the group will show the current replica name
If this runs on the secondary, it will work currently. However, say the primary goes down and now the AG switches over to the secondary. Will this script work so that it thinks it's the primary?
December 10, 2014 at 8:02 am
JoshDBGuy (12/9/2014)
Perry Whittle (12/9/2014)
JoshDBGuy (11/26/2014)
So will this also work,
if (select ars.rolefrom sys.dm_hadr_availability_replica_states ars
where ars.is_local = 1) = 2
begin
raiserror ('This is not the primary replica.',2,1)
end
Work from where, where will this be running from? Did you read my post above, if you query at the replica level you'll need to query each replica. Querying the group will show the current replica name
If this runs on the secondary, it will work currently. However, say the primary goes down and now the AG switches over to the secondary. Will this script work so that it thinks it's the primary?
What are you expecting that script to do??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 10, 2014 at 9:41 am
Perry Whittle (12/10/2014)
JoshDBGuy (12/9/2014)
Perry Whittle (12/9/2014)
JoshDBGuy (11/26/2014)
So will this also work,
if (select ars.rolefrom sys.dm_hadr_availability_replica_states ars
where ars.is_local = 1) = 2
begin
raiserror ('This is not the primary replica.',2,1)
end
Work from where, where will this be running from? Did you read my post above, if you query at the replica level you'll need to query each replica. Querying the group will show the current replica name
If this runs on the secondary, it will work currently. However, say the primary goes down and now the AG switches over to the secondary. Will this script work so that it thinks it's the primary?
What are you expecting that script to do??
I'm going to stick it in a job as the first step. If it returns an error because it's not the primary node, I want the job to quit.
December 10, 2014 at 10:01 am
easy to test, but, affirm. The raiserror will be passed to the job step and fail, if this step is told to quit on failure the job will terminate
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 10, 2014 at 10:14 am
I've done the job test, but I'd like to failover to the secondary node and test it out. Unfortunately I can't do that on my box as I have windows 8.1 and I need 2012 server.
December 10, 2014 at 10:47 am
build yourself a virtual test system on your 8.1 machine 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 18, 2014 at 10:33 am
Unfortunately because I don't have server 2012, I can't. I did find through a Microsoft technet page that when it switches over, the replicated server should become the primary. So it should work.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply