SQL server Jobs fail over

Question

SQL server Jobs fail over

Ratheesh4sql

SSCommitted

Points: 1811
More actions
March 11, 2013 at 5:41 am

#290616

Hi,
Is there any possibilities for SQL server Job fail over to the different server???

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you be more specific? Fail over when, to what, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 2

I am looking for solution to fail over my scheduled jobs to the different server when if there is any issue comes job server....

because its very critical jobs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Just the jobs? What about the databases those jobs connect to? What HA/DR do they have set up?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 4

Just the jobs? What about the databases those jobs connect to? What HA/DR do they have set up?

Yes..i have setup database level HA in four servers using sql always ON. The Fourth secondary server i have created number jobs pointing to the listener IP.

here is any possibilities to fail over jobs to third server if there any issue comes in fourth server ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Syncing the jobs is the easy part, just copy them to the other servers. You just need some check in the job to ensure that it won't run on the secondary if the secondary is inactive

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 6

U mean to say that we need keep the same Jobs in third server and run it manual when fourth server is goes down

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Well I didn't say run them manually...

If you want server 4 to be able to run server 3's jobs when server 3 fails, then you need to have those jobs on server 4. Since MSDB is a system database and written to by SQL Agent, that can't be via mirroring, log shipping or availability groups. Perhaps replication, I can't recall offhand whether MSDB can be published.

Then you either need to ensure that SQL Agent on server 4 is stopped and manually started when server 3 goes down, bit of a pain there, or have something in the jobs, probably as the first step, that checks what server they're on and what servers are active and online to see if they should run. So jobs on server 4 check to see what server they're running on, see server 4, then check whether server 3 is available, if it's not run.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 8

for us, we use an MSX server. each set of servers in that cluster or the target of replication get pushed those jobs hosted at the MSX server level. When a change to that job is made, it pushes it to all boxes it's targeting. That might be what you're wanting. we use this on clustered and transaction replication based setups. (as well as all other servers).

so not exactly a fail over item, but it would qualify as a failover solution.

.