question

b0redj0rd avatar image
b0redj0rd asked

Job to fail back SQL databases after failover from Primary to Mirror.

Hello guys,

I have the following scenario which I was hoping to resolve using a job if possible. We have databases across 2 servers some of which are primary on (a) and some are primary on (b). Once per month the server is patched and reboots - All DB's failover to their mirror at this point, as they should.

I would like to know if there is a way of failing the databases that have moved off their primary server to their mirror back to their original location (once the patching is complete). At the moment this is done manually by right-clicking-tasks-mirroring-failover and it doesn't seem to be an efficent way of doing this.

Can I create a job to do this task, perhaps something that can run hourly?

mirroringsql job
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

Sure. Let the job run on what's supposed to be a secondary server for a database and have it do something along the lines of below code. And as always, test this, run the code manually ni different situations to make sure you don't get a fail-fail situation. One thing would be to log the attempts to failover, using try/catch logic and set a retry-interval on it. Normally, if the mirroring_state is synchronized and the database is online on the server where the code is running, it shouldn't fail, but you never know. If for some reason the supposed primary isn't ready to take over as principal, your job might end up trying to fail over, fail and then retry the same thing when the job runs next time, which will for sure cause unnecessary downtime and/or interruptions for your applications using the database.


IF EXISTS(
 SELECT * 
 FROM   sys.database_mirroring 
 WHERE  mirroring_state = 4 -- Synchronized
  AND   mirroring_role = 1  -- Principal on current machine
  AND   database_id = DB_ID('DatabaseA')
 BEGIN
  ALTER DATABASE databaseA SET PARTNER FAILOVER;
 END
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

b0redj0rd avatar image b0redj0rd commented ·

@Magnus Ahlkvist thank you very much - That is very useful, I do appreciate your help!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.