bamapie
asked on
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
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
lcohan
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)
If you are talking about "Clustering" rather than Mirroring the SQL Cluster will move the resources to the active node including jobs.
ASKER
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...
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...
For details about that please read more at: "Managing Logins and Jobs After Role Switching"
http://msdn.microsoft.com/en-us/library/ms191458.aspx
http://msdn.microsoft.com/en-us/library/ms191458.aspx
You should have all your SQL jobs scripted and kept somewhere safe (including version control) so you can apply them after you failover.
ASKER
>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-ma
>(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-ma
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>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.
>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.
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.
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.
ASKER
Sincere apologies, lcohan, and thanks for the help and advice.
My pleasure - no issues with me.
I get frustrated many times with software I'm using and I can understand that...
I get frustrated many times with software I'm using and I can understand that...