[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mirroring: Jobs on Secondary Server

Posted on 2011-10-06
11
Medium Priority
?
184 Views
Last Modified: 2012-05-12
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
Comment
Question by:bamapie
  • 7
  • 4
11 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36925395
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36925416
If you are talking about "Clustering" rather than Mirroring the SQL Cluster will move the resources to the active node including jobs.
0
 

Author Comment

by:bamapie
ID: 36925425
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 40

Expert Comment

by:lcohan
ID: 36925470
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36925495
You should have all your SQL jobs scripted and kept somewhere safe (including version control) so you can apply them after you failover.
0
 

Author Comment

by:bamapie
ID: 36926042
>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
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 36926144
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
 

Author Comment

by:bamapie
ID: 36926244
>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
 
LVL 40

Expert Comment

by:lcohan
ID: 36926445
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
 

Author Closing Comment

by:bamapie
ID: 36926484
Sincere apologies, lcohan, and thanks for the help and advice.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36926593
My pleasure - no issues with me.
I get frustrated many times with software I'm using and I can understand that...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question