What are my option to do backup and restore a prod db to a test server on separate thread?
-
I have a nightly production job A that has 9 steps for processing on different dbs, and after step #3, I want to add a backup dbX on a separate process (because dbX is ready for backup and it takes 1/2 hour to back it up, it is big.), so that my main job A will not be delayed due to the back up and can continue the rest of the steps.
My question is what are my options to make this happen on SQLServer 2000? Currently, I have the backup step at the end of the job, because I need to get most of the critical step finish ASAP. But that put me behind on using that BAK file to restore to my test env. If I move the backup step up and get kick off somehow after step #3, this whole backup and restore process can happen during off hours and be done simulatanously.
I have thought of having a dump file somewhere to flag the Step#3 is done, so that some other job can ping it. But I feel like there must be a better way or a easier way to implement this kind of process. Since I am new in this area, I thought I should pick some brain out there and see what kind of practice people are doing on a similar process like this.
Thanks in advance for any feedback.
Sin
-
Putting a flag somewhere is common, whether it be a file or table. Another option is to have your job or process fire an alert that can start a separate job. In this case why not just call sp_start_job and put your backup code in a separate job?
-
Oh, that sounds good. But does the sp_start_job return to the main process right away, or does it wait until my new backup job finish?
Thanks.
Sin
-
Its asynchronous.
-
I have just tested it and it works great. Except I have to get into setting up my server as MSX (Master Server) and then my restore db job has a target server, etc....
I am not familiar with how this MSX work, but it looks interesting but yet difficult to see job status, like the non MSX way that I am used to.
Is there any server load or support implication in setting something like MSX for my main production server and my test server?
Without the MSX, my process of restoring on the Test server won't work.
So I am just being cautious about this change before implementing on the production server.
Thanks.
Sin
-
I've posting into another thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=49&messageid=97068 with SinLam on a closely related subject.
I think I have Asynchronous & Synchronous confused
. I always think of Synchronous as "at the same time" or "parrallel operation". I actually took the time to edit my SP to change all Async to Sync prior to my post. Please clear up my confusion. ThanksSQL Job start is just a "directive" to the Agent to go ahead and start the job. It takes very little time. Both Andy & my posts deal with jobs and then "just directing them to start" when appropriate, and nearly instantly continuing on with "local scope" processing without a care for the task the "other job" is doing.
Once you understand the BITs, all the pieces come together
-
ThomasSH,
Thanks for your clarification. I will make the same change to the copy of the your script then.
I am evaluating different options. I think the difference between your script then the setting up a Job to run SP_START_JOB @ServerName = 'TEST', is that using your script I don't need to configure my main server to be the Master Server and my TEST server to be my Target Server. But I get more other features that come with MSX that your scripting option won't have.
Am I correct on this assessment so far? Does anyone think that I am on the wrong track if I go with the MSX option?
I can see us going down the road of having a few more Asyn jobs to run, for setting up a mirror of my production env for testing/reporting....fair efficently and easily, without the impact of any production job delay.
Thoughts?
Thanks.
Sin
-
My "slant" was having a generic "hey SQL, go do [this], and come back right away, don't wait for [this] to finish".
I'll usually write an SP that handles all the complexities of whatever I'm trying to accomplish, then just call the SP either directly, or with my ExecSQLSync SP. I like to keep the TSQL code inside the Job Steps very simple.
As far as the two servers issue... I they are linked, then you can start a job on ServerB from ServerA by coding on ServerA something like EXEC ServerB.MSDB.dbo.sp_start_job ... or EXEC ServerB.[some database].dbo.ExecSQLSync 'YourRestorSP'. (Note: I did not test the above syntax for remote Linked Server SP calls, but I think it's correct)
Once you understand the BITs, all the pieces come together -
To me (and I could be wrong!), synchronous operations happen in a linear fashion. If you're running steps A, B, C, step C doesnt run until B completes and B doesn run until A completes. In most cases this is the desired behavior.
Sometimes you want to start a process and keep going rather than wait on it to complete. In the coding world this is where you start thinking about threads and callbacks. I consider this to be async.
-
Agree with Andy.
Sin
-
Understood. So I had my SP named ExecSQLAsyncSP named right to start with.
Asynchronous = Parrallel
Synchronous =Serial
Once you understand the BITs, all the pieces come together
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply