Mirroring: Jobs on Secondary Server

SQL SERVER 2008 R2

I'm starting to set up a couple of SQL boxes as primary and secondary in a mirroring configuration.  There are actually no DBs on them yet, as I'm going to be first migrating a couple of databases from a 2005 box, then setting up the mirroring later.

But I'm starting to wonder how some of this will work, and I'd like to hear from some of you with experience.

Today, my question is this:  I have a dozen or so jobs that execute stored procs within my soon-to-be-mirrored databases.  In the event of failover, I would still need these jobs to execute on the secondary server.

But I don't need them executing BEFORE there's a failover.

How do I accomplish that?  How do I make it so that *after* failover, my jobs on the secondary server begin working--but not BEFORE failover?

Thanks

bamapieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Your jobs will be executed against the "Principal" of the two mirrored db's and data will go to the "Secondary" or mirrored DB via the mirroring process - no need to mirror the jobs other than if you want to backup the mirrored db(s)
lcohanDatabase AnalystCommented:
If you are talking about "Clustering" rather than Mirroring the SQL Cluster will move the resources to the active node including jobs.
bamapieAuthor Commented:
No, I mean mirroring.

Okay, I *really* am not tracking now...

>Your jobs will be executed against the "Principal" of the two mirrored db's

But the principal is dead, in this scenario...right?  He's down, unavailable...
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

lcohanDatabase AnalystCommented:
For details about that please read more at: "Managing Logins and Jobs After Role Switching"

http://msdn.microsoft.com/en-us/library/ms191458.aspx


lcohanDatabase AnalystCommented:
You should have all your SQL jobs scripted and kept somewhere safe (including version control) so you can apply them after you failover.
bamapieAuthor Commented:
>You should have all your SQL jobs scripted and kept somewhere safe
>(including version control) so you can apply them after you failover.

So from reading that, apparently the idea is, "re-create your jobs on the secondary server following failover"... ?  

That's disappointing, since it kind of kills the whole automatic-failover thing.  This would be, like, automatic-failover-plus-manual-job-replication.
lcohanDatabase AnalystCommented:
You are correct however if you are looking for high availability  and can afford the hardware/software then I suggest you look at: "Getting Started with SQL Server 2008 R2 Failover Clustering"

http://msdn.microsoft.com/en-us/library/ms189134.aspx

This is - you can't mirrir master and/or msdb databases and if the cost is too high for you to choose SQL Clustering method than I believe the fact that you have to run one SQL script after the failover against the new "principal" is a small price to pay in my opinion.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bamapieAuthor Commented:
>I believe the fact that you have to run one SQL script after the failover
>against the new "principal" is a small price to pay in my opinion.

Seriously?

It's not the work involved in executing the script.  It's the fact that that script needs to be executed when the primary goes down at 2am on a weekend when the DBA is out of town visiting his in-laws.  It's not the freakin' calories exerted in running the script.
lcohanDatabase AnalystCommented:
Look - I'm trying to help you (for free) with options/workarounds, to share my knowledge and my comments are based on my experience not to offend you however if you have anything against the product or wish for a change in SQL mirroring please take that with Microsoft.

Good luck!

P.S. I'm a dba and often had to do the "on call" over weekends, new year and so on so no need for nasty comments.
bamapieAuthor Commented:
Sincere apologies, lcohan, and thanks for the help and advice.
lcohanDatabase AnalystCommented:
My pleasure - no issues with me.
I get frustrated many times with software I'm using and I can understand that...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.