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?
Answer by Magnus Ahlkvist ·
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