September 26, 2016 at 1:33 am
Hi SQL Experts,
We have SQL 2014 Enterprise Edition instance in our environment and Alwayson availability groups configured.
Assume Server-1 and Server-2 are the two AG replicas.
In our environment (dev reporting enviornment), we have sql agent jobs configured only on "Server-1" and no jobs present on "Server-2".
Just incase if any reason(patching,unexpected sql shutdown, unexpected reboot) if it fails over to Server-2, I have create a sql agent job on Server-2 which runs every 5 mins, just to check if it became primary replica,
if so, I wanted to fail back to Server-1. Today, there was failover but the was continously failing with below error. Can anybody help me in fixing this.
Always my preferred node is Server-1 to run my jobs.
Job NameFailover AG group
Step Namestep1
Duration00:00:00
Sql Severity16
Sql Message ID41122
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: domain\user. Server-2 [SQLSTATE 01000] (Message 0) PRIMARY [SQLSTATE 01000] (Message 0) Cannot failover availability group 'MytestAG' to this instance of SQL Server. The local availability replica is already the primary replica of the availability group. To failover this availability group to another instance of SQL Server, run the failover command on that instance of SQL Server. If local instance of SQL Server is intended to host the primary replica of the availability group, then no action is required. [SQLSTATE 42000] (Error 41122). The step failed.
Job step logic :
===============
SET NOCOUNT ON
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
PRINT @ServerName
PRINT @RoleDesc
IF @RoleDesc = 'PRIMARY'
BEGIN
ALTER AVAILABILITY GROUP [MytestAG] FAILOVER;
END
Thanks,
Sam
September 26, 2016 at 6:22 am
September 26, 2016 at 8:23 am
From https://msdn.microsoft.com/en-us/library/ff878601.aspx about ALTER AVAILABILITY GROUP...FAILOVER:
Initiates a manual failover of the availability group without data loss to the secondary replica to which you are connected[/i].
(my emphasis)
The problem is as the error message indicates. You're requesting a failover to an instance that is already the primary.
The simplest way to do what you're wanting is to have the job run on Server-1, and check if it is the secondary. If it is, initiate failover to Server-1.
Having said that about the specific error you're getting, I'm not so sure you necessarily want to automatically fail back to Server-1 any time there is a failover to Server-2. If the failover occurs because there is real problem with Server-1, you should probably want to keep Server-2 as the primary until Server-1 has been investigated and a cause for the initial failover determined.
I'd prefer just raising an alert/sending an email on failover, so the cause can be investigated and any necessary fixes implemented prior to failing back to Server-1.
Cheers!
September 26, 2016 at 8:23 am
vsamantha35 (9/26/2016)
IF @RoleDesc = 'PRIMARY'
BEGIN
ALTER AVAILABILITY GROUP [MytestAG] FAILOVER;
END
Here you are telling the AG to failover if it's the primary. Failover should be initiated from the secondary replica. So you should setup your job on the secondary, and have that do the failover in the event that @RoleDesc = 'SECONDARY'
September 26, 2016 at 6:20 pm
Thanks a ton Jacob and Nicholas.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply