Sql-server – Are there any best practices before moving SQL Server to another node (controlled failover)

availability-groupsbest practicesclusteringfailoversql server

Let's say there are multiple jobs running on the SQL Server – we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there any best practices before performing such operation, for example disabling the jobs? Is there anything which could be done to minimize any potential risks?

Best Answer

First make sure you have both logins and jobs fully synchronised between your instances. there are different ways to achieve these.

index maintenance jobs and backups

I had good results by using ola hallegren maintenance jobs for these.

replication and service broker

Always point to the listener instead of individual IPs or server names.

inside the jobs

inside every step of every job I test if I am in the primary server, in my current availability group called sqlprodag. Something like this:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'SQLPRODAG'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

    EXEC MYDATABASE.[dbo].[usp_ins_APfactAPApplicationBreakdown]


end
else
begin
    -- this server is not the primary replica, (optional) do something here
    print 'Not a primary server'
end

and the job will only run on the primary server.

best practices before performing a failover

planned manual failover of an availability group 

--======================================================================================
-- set the availability group to synchronous mode
-- wait until all is synchronized
-- do the failover

--SQLPRODAG is the availability group name
--servers are SQLPROD1 and SQLPROD2
--======================================================================================


--ON THE PRIMARY
ALTER AVAILABILITY GROUP SQLPRODAG 
     MODIFY REPLICA ON N'SQLPROD2' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

--GO TO THE SECONDARY
ALTER AVAILABILITY GROUP SQLPRODAG FAILOVER;  

     SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc 
   --ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 

     SELECT ag.name, 
*
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;
Related Question