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?
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
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
--====================================================================================== -- 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;