October 31, 2001 at 4:11 am
Hello all!
I have master server (Msrv) and target server (T1). The problem
is that I need to start job_t on target server and if it completes
successfully -- then start job_M on master server. No idea how to do this, so I've
to ask your advice.
All servers run MS SQL Srv 7.0, SP 3 on Win NT 4.0 SP 6
Thanks in advance.
Mirochnick Natalia,
MCSE
October 31, 2001 at 5:15 am
Pretty interesting. I dont use the master server functionality at work, only have two boxes, hardly worth while. But anyway...
If the target subscriber has (or could have) access to the master, you could put in a final step that would connect to the master and run the job. Or you could have the target server job write something to a file/table, have your other job on the master run periodically and check for that to be present.
Neither of those seems very elegant, maybe some way to leverage the step functionality?
Andy
October 31, 2001 at 6:23 am
Thanks for your answer, Andy.
Actually we've got 6 target servers, but i wanted to tune at least one :). The problem is that jobs_T differs -- there is job_T1 for target server_1, job2 for Target2; job_M1 should start after job_T1, job_M2 - afte job_T2, etc... And all of them sould run one after on :(.
I've found something like another answer: create job, whose first step will look into table sysjobservers on master or execute sp_help_job @job_name='job_T1'. If job_T1 is completed -- it'll start job_M1, if not -- it'll try one more time after 10 minutes.
But implementing this scenario whith many servers isn't very interesting...
Anyway -- thanks alot!
October 31, 2001 at 10:32 am
This seems like a lot of work. What are these procedures doing? Perhaps we can suggest an easier solution.
Steve Jones
October 31, 2001 at 4:46 pm
We get around the problem by using linked servers and then executing
EXEC servername.msdb.dbo.sp_start_job @job_name = 'finance_cube_2002'.
This allows the control to be kept within the steps of the job using the "on success" and "on failure" to dictate what actions are taken..
Tony
October 31, 2001 at 5:16 pm
Thats not bad. But wouldnt success or failure in this case just indicate if the job started ok, rather than the job itself completed ok?
Andy
October 31, 2001 at 5:45 pm
Andy
Yes but in our case we have an idea what should be the result of the job so we can test for it and take the necessary action.
In fact we also use you suggestion of writing data to a table accessible by all our linked servers this then gives us notice first thing in the morning if there has been any problems in the overnight jobs and we can take any necessary action...
Tony
November 1, 2001 at 4:39 am
Thanks to all discussion members for their useful and quick answers!
I've been asked about purpose of my stored procedures, so i'd better dicribe the whole situation.
We've got master-server with tape-backup device attached, so it's the only server for tape backups of all our databases. On all other production servers i've to create local backup of all databases, transfer it to master (with help of st.proc. job_T1), restore databases on master (job_M1 on master-server). And this steps repeats 6 times (for all target servers).
November 1, 2001 at 4:58 am
Interesing. Have you considered log shipping or replication as an alternative? For once a day, snapshot replication would work well.
Andy
November 1, 2001 at 10:07 am
Why worry when the databases appear? Maybe just scan for "new" databases and run the backups? Or just schedule the backups for a short period of time after they will be transferred to the master server. If they are not there, they will not be backed up.
I'd keep these tasks separate (for fault tolerence) and suggest you write something to scan the backup logs for items that are not backed up.
Steve Jones
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply