question

johnsemi avatar image
johnsemi asked

Multi-Server Administration - Different schedules for different targets

I've been searching for a solution to this and haven't found ANY information on it (surely I can't be the only one..) Does anyone know a way to create different schedules for a multi-server job that apply only to specific designated target(s)? For example, you would have one generic MSX full backup job- then schedule it for Mondays on TSX A, Tuesdays on TSX B, and so on. I know you can specify certain target servers for multi-server jobs, but I have not found a way to assign schedules based on target.
sql-agentschedulemastermulti-server
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image SirSQL commented ·
Honestly, I wouldn't use MSX servers. I've found them to be notoriously unreliable. Jobs forget to run, changes don't get pushed out and forget it if you need to retire a server. I know this doesn't solve your problem, just wanted to share the information.
0 Likes 0 ·
johnsemi avatar image johnsemi commented ·
What SQL version was it? That's strange, hasn't been my experience at all (using 2008R2 as MSX if that makes a difference). Been using it for just over a year with no issues, and it's saved me hours and hours of creating/updating jobs.
0 Likes 0 ·
SirSQL avatar image SirSQL commented ·
Every version 2005 and up I've tried it, with little to no success.
0 Likes 0 ·

1 Answer

·
johnsemi avatar image
johnsemi answered
Maybe I was the only one.. What I ended up doing was using an admin database to manually run jobs. I created a schedules table for all jobs on all instances. Every ten minutes, I run 2 stored procedures. One queries the schedules table, calculates what should run in the next 10 minutes, and puts the jobs into a queue table. The second procedure loops through the queue table, starts each job on the instance specified, and removes the row from the table. The procedures are fairly complex, but the system has worked without fail for almost 9 months now.,Maybe I was the only one
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.