• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

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

0
bamapie
Asked:
bamapie
  • 7
  • 4
1 Solution
 
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)
0
 
lcohanDatabase AnalystCommented:
If you are talking about "Clustering" rather than Mirroring the SQL Cluster will move the resources to the active node including jobs.
0
 
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...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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


0
 
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.
0
 
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.
0
 
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.
0
 
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.
0
 
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.
0
 
bamapieAuthor Commented:
Sincere apologies, lcohan, and thanks for the help and advice.
0
 
lcohanDatabase AnalystCommented:
My pleasure - no issues with me.
I get frustrated many times with software I'm using and I can understand that...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now