SQL Agent Jobs for Mirrored Databases

Question

Post reply

SQL Agent Jobs for Mirrored Databases

Green Armchair

Hall of Fame

Points: 3561
More actions
April 28, 2008 at 9:00 am

#125069

Hi
Is there a way to automatically enable SQL Agent jobs whenever a mirrored database fails over automatically?
Database mirroring looks like an option for us, but I'm concerned about objects outside the database (sql agent jobs, logins, linked servers etc) and how to automatically enable a job on the failover server.
Thanks
Andy

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 1

You'd have to script something. Mirroring tackles databases, not anything on the instance.

You could try a connection to the principal server and enable them if it doesn't work.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 2

I'd also be sure that job continues to run and if it does work, disable the jobs. That way if things fail back you're ok.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

Green Armchair

Hall of Fame

Points: 3561

April 28, 2008 at 9:51 am

#807907

Thanks Steve

Andy

Paul Mu SSCertifiable Points: 5547 More actions · Answer 4

What you could do is to create a single job that checks the status of the database participating in database mirroring.

Essentially, this SQL Agent job polls every minute or so by checking the mirroring_state attribute of the catalogue view sys.database_mirroring. And depending on the state you would either enable or disable all other jobs.

Paul

Green Armchair Hall of Fame Points: 3561 More actions · Answer 5

Thanks Paul

Does that catalog view exist on both servers?

Andy

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 6

I have a maintenance solution that has been designed for database mirroring. The stored procedures only work with databases that are online. This means that you can have the jobs enabled and running on both servers. In a failover scenario they will automatically start doing backups (and other maintenance things) on the mirror server as these databases now become online.

http://ola.hallengren.com/sql-server-backup.html

http://ola.hallengren.com/sql-server-integrity-check.html

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

Paul Mu SSCertifiable Points: 5547 More actions · Answer 7

Well, according to msdn, this is what it has to say about sys.database_mirroring:

"Contains one row for each database in the instance of SQL Server. If the database is not ONLINE or database mirroring is not enabled, the values of all columns except database_id will be NULL."

So, the answer should be 'yes'.

Finally, note the view is located in the master database.

Paul

Green Armchair Hall of Fame Points: 3561 More actions · Answer 8

Thanks Paul

If (and I'm not convinced yet!) we go down the mirroring route, I will investigate using this DMV.

Regards

Andy

kozloski SSC Eights! Points: 804 More actions · Answer 9

Another idea may be to use WMI event alerts and the DATABASE_MIRRORING_STATE_CHANGE event.

You could set up a WMI event alert to look for DATABASE_MIRRORING_STATE_CHANGE events ( SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE ) and could then define a SQL Agent job that runs in response to that alert to enable or disable other jobs, depending on the state the mirroring session has changed to.

-- J.Kozloski, MCDBA, MCITP

Andeavour SSCrazy Points: 2600 More actions · Answer 10

We've just (today) encountered a situation where SQL Agent jobs do actually appear to be "Mirror Aware". We have a set of backup jobs for databases running on the principal (performing log backups every 15 minutes), driven by a series of parameters in an Admin database which call nested procs that then call RedGate SQL Backup.

Today when we fipped the mirrors for a DR test we expected these jobs to fail as the principals became unavailable. However what we actually saw was that the jobs kept right on running, backing up the now active DR databases and using the Admin parameters off the DR server.

This totally unexpected behaviour has several of us scratching our heads, as we never expected the SQL Agent jobs to be mirror aware.

It turns out that if the Database referenced in the Steps stage of the jobs wizard is the one being mirrored, it seems that it somehow manages to resolve which is the principal and which is the mirror. If the job runs in the master databases and has a "USE [DB]" command in the job steps, it fails.

Andeavour SSCrazy Points: 2600 More actions · Answer 11

UPDATE:

We've tested this now and found ut to be consistent behaviour, if the database named in the job step:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step Name',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'exec Database..procname @DBName =''MirroredDB''',

@database_name=N'MirroredDB',

@flags=0

Then the SQL Agent consistently works out which is the active partner in the relationship, and runs the job against that database - even when the job itself is running only on the Principal server.

THis actually gives us a number of options for controlling which agent jobs will be "mirror aware" and which ones won't, by simply using the database in the job step, or (if we want the job to fail after failover) use "master" in the job definition and put a "USE [DB]" statement into the SQL of the job.