Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management


Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management

Author
Message
Richard Waymire
Richard Waymire
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 64
Comments posted to this topic are about the item Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management



logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1389
This is nice article..

when i tried to implement the Enlist and Ensure process doesn't work. so do i have to make any security changes ?

Aim to inspire rather than to teach.
SQL Server DBA
Richard Waymire
Richard Waymire
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 64
Are the service accounts the same? What was the exact error you got?



logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1389
Says Access Denied..

Aim to inspire rather than to teach.
SQL Server DBA
David Korzennik
David Korzennik
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 155
What are some of the bigger MSX/TSX deployments out there? Are there upper limits? Has anyone done or seen large scale enterprise deployments?



rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
I would like to use a master job to deploy maintenance scripts. One of the requirements is to stagger the schedule. For example, separate the DBCC a few minutes for instances sharing the same set of disks (either on direct attached disk or on a SAN). I can't think of a way to do this except perhaps to create a master job that creates and schedules a regular job. Sure would be nice if there was an option to add some customization for each target.

Also, when supporting 2008+, do you have to use a SQL 2008 server for the master? Can you use SQL 2012 with jobs provided everything in the job is compatible with a SQL 2008 system?

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
I also noticed that there is no option to run the MSX job on the master. The master can't enlist itself or another master. It appears I will need to keep a duplicate copy of the maintenance jobs (different names) on the master server. It's a nice feature, but would be a lot nicer if it had a little flexibility.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
David Korzennik
David Korzennik
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 155
Hi

To stagger the jobs on the different target servers you would have to, in the 1st job step for example, have a 'randomizer' based on the host name.
Eg:
DECLARE @HostN VARCHAR(128) = @@ServerName
IF @HostN = ServerA WAITFOR DELAY '00:01:00'
Etc



David Korzennik
David Korzennik
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 155
Re the 2012 / 2008 question. Yes, you can use a SQL 2012 MSX server and enlist 2008 R1 and R2 and SQL 2005 servers if you have those still. Obviously be aware of using 2008 or above features in jobs that will target 2005 servers. These will not work. What works quite well is to create Target server groups, see BOL for detail. You could then create a 2005 group if this is an issue. You can also populate your Target server groups from a SQL CMS server which may have groups defined in it.



rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
I was thinking of a waitfor, but that would make the run time information harder to use. I want to know how long the backup takes, for example. If it runs 20 minutes rather than 10 minutes, I will want investigate. However, perhaps the metric is the actual end time of the last backup - one metric for all servers. I could also create a report that gets the backup times directly instead.

In the long run, I was hoping the master job script can include a list of average runtimes. (With a job that collects this data and updates the job being deployed.) I suppose I could collect the second step time from each instance and create a wait that depends on the sum from instances "scheduled" before it (e.g., those prior in the list).

I also can't have it completely random because of other non-maintenance jobs. However, I do like the idea. It would be an easy way to handle some of the servers on the SAN.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search