August 25, 2015 at 3:01 pm
Hi All,
Need a query help.
I am running SQL 2014 2-node AlwaysON Availability groups, Enterprise Edition in our environment
and 5 databases are part of AG.
Question is, sometimes AG is getting failed over to node2 but always our preferred node is node1 due to some business needs otherwise some of our jobs will fail.
So, what I looking for is, a sql script which can handle a situation wherein, for some reason, AG is failed over to node2, it should be able to detect if node1 is back online or not and if so, it should fail back to node1. How to do this using tsql query or stored proc or sql agent job ?
Thanks in advance.
August 25, 2015 at 5:29 pm
The failover script is easy and there are a lot of options for detecting whether or not a server is back online. The problem is going to be how do you know it failed over? Also after it fails over, what do you do to run the job to fail it back?
Someone here probably has a lot more knowledge than I do, but you can set up an alert to run a job when the ag fails to the secondary. From there you can figure out a way to keep that job running, trigger an proc, etc. to check for the status of the primary.
Here is a script I made that I haven't been able to test but you can take a look at it.
-- A little elegant
declare @state int
select @state = connected_state
from sys.availability_replicas ar
inner join sys.dm_hadr_availability_replica_states ags on ar.replica_id=ags.replica_id
where ar.replica_server_name='Replica Name'
if @state = 1
begin
ALTER AVAILABILITY GROUP AvailGroup FAILOVER;
--Notice email perhaps here
end
-- Less Elegant
if exists (select *
from sys.dm_hadr_availability_replica_states
where connected_state=0)
Print 'Oh No, Offline!'
else
ALTER AVAILABILITY GROUP AvailGroup FAILOVER;
August 26, 2015 at 1:41 pm
Thanks Josh. Agree with what you said.
August 27, 2015 at 1:51 am
vsamantha35 (8/25/2015)
Hi All,Need a query help.
I am running SQL 2014 2-node AlwaysON Availability groups, Enterprise Edition in our environment
and 5 databases are part of AG.
Question is, sometimes AG is getting failed over to node2 but always our preferred node is node1 due to some business needs otherwise some of our jobs will fail.
So, what I looking for is, a sql script which can handle a situation wherein, for some reason, AG is failed over to node2, it should be able to detect if node1 is back online or not and if so, it should fail back to node1. How to do this using tsql query or stored proc or sql agent job ?
Thanks in advance.
Set a preferred node on the cluster role\group and then set the failback property on the cluster role\group.
Bear in mind that in this case the group may go offline unexpectedly to move back to the preferred node when it comes online, this may not be desirable
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply