XML FeedsI have a bunch of SQL Agent jobs that execute T-SQL against databases. A bunch of these databases are mirrored. Of course if the database is the principal then these jobs will work without a problem. But what happens if you failover? Now these jobs will start failing. You can either have the same jobs on both servers and have them enabled or disabled depending where the mirror or principal is. You can very easy enable or disable these whenever you failover. For example
- USE msdb
- GO
- UPDATE sysjobs
- SET enabled =1
- WHERE name IN ('',''....) --use a table that has all the jobs instead
Or another way would be to check if the database is online to see if the job should continue running
Here is such an example
- IF EXISTS(
- SELECT 1 FROM sys.databases
- WHERE state_desc = 'ONLINE'
- AND collation_name IS NOT NULL
- AND name = 'YourDB')
- BEGIN
- PRINT 'yep, good to go'
- END
The reason we also check for collation_name in addition to state_desc is documented in Books On Line
A database that has just come online is not necessarily ready to accept connections. To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For AlwaysOn databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.
Yet another option would be to have a table with the 'live' server for the database, this however is more used for jobs, SSIS packages and programs that live on other servers
How do you manage your jobs when dealing with mirroring? Leave me a comment, I am interested in your approach.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Isnt it unsafe to update system table
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.