October 31, 2016 at 11:23 am
Hi all,
As we have already know -> system databases are not mirrored nor replicated in both mirroring and alwayson availability groups.
We have application jobs that are scheduled to run recorded in the msdb and on every application deployment, more jobs might be added.
What would be the best practice to "copy/replicate" these jobs over ?
-- doing backup of msdb and frequent restore to the mirrored/replica instance ?
Is there anyway we can test if the jobs are working properly in the standby/DR instance (since the mirrored database cannot be opened, and replicas are read only) ?
Any advise will be deeply appreciated - thanks once again!
Regards,
Noob
November 4, 2016 at 4:53 am
szejiekoh (10/31/2016)
What would be the best practice to "copy/replicate" these jobs over ?-- doing backup of msdb and frequent restore to the mirrored/replica instance ?
No!
do not do this, MSDB like master has system information embedded within.
szejiekoh (10/31/2016)
Is there anyway we can test if the jobs are working properly in the standby/DR instance (since the mirrored database cannot be opened, and replicas are read only) ?Any advise will be deeply appreciated - thanks once again!
Regards,
Noob
Script the jobs, etc band apply them to the partner nodes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 5, 2016 at 2:29 am
Perry Whittle (11/4/2016)
szejiekoh (10/31/2016)
What would be the best practice to "copy/replicate" these jobs over ?-- doing backup of msdb and frequent restore to the mirrored/replica instance ?
No!
do not do this, MSDB like master has system information embedded within.
Hi Perry,
Good to hear from you! Thanks for replying.!
q1) Can you elaborate further ? Even if msdb has system information embedded within, what is the problem of taking a msdb backup and restoring it in a new instance ?
szejiekoh (10/31/2016)
Is there anyway we can test if the jobs are working properly in the standby/DR instance (since the mirrored database cannot be opened, and replicas are read only) ?Any advise will be deeply appreciated - thanks once again!
Regards,
Noob
Script the jobs, etc band apply them to the partner nodes
q2) Is there anyway we can obtain the script of all the jobs in msdb using SSMS ? what do you mean by "band" apply ?
Hope to hear from you soon.
Regards,
Noob
November 7, 2016 at 5:45 am
Band is a typo for and :-D.
November 7, 2016 at 5:50 am
szejiekoh (11/5/2016)
Even if msdb has system information embedded within, what is the problem of taking a msdb backup and restoring it in a new instance ?
The fact that it has references to its original server in terms of name, and hence you may get unexpected problems and strange behaviours. In general, system databases should not be restored to different instances.
Script your jobs, apply them on the secondary and make it part of your release process for job changes to apply the changes to both instances.
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
November 7, 2016 at 5:56 am
szejiekoh (11/5/2016)
Hi Perry,Good to hear from you! Thanks for replying.!
q1) Can you elaborate further ? Even if msdb has system information embedded within, what is the problem of taking a msdb backup and restoring it in a new instance ?
You're welcome.
Gail has already highlighted, but the reason is because the jobserver name will be the name of the source server and as such jobs will fail to execute. Do not restore system databases across instances!
szejiekoh (10/31/2016)
q2) Is there anyway we can obtain the script of all the jobs in msdb using SSMS ? what do you mean by "band" apply ?Hope to hear from you soon.
Regards,
Noob
You can script the jobs from within SSMS.
band is a typo :blush:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2016 at 8:29 am
Hi Gila, Perry,
Thanks for your wonderful advice!
q1) By scripting the job out, do you mean in "SMSS -> SQL Agent -> Jobs -> Jobname -> right click , Script job as create to" ?
q2) As part of the release process, Do we also have to add the following line in the script ->
"EXEC msdb.dbo.sp_update_job @job_name='Your job name',@enabled = 0 " so that the job doesn't run in the mirror instance ?
q3) is there anyway to test run the jobs in the mirror instance ? is the only way = create a snapshot and change the job to run on the snapshot instead ?
q4) If we change our db mirroring method to use alwayson AGs instead, is the method of migration jobs still the same as above ? (script out the jobs and run the scripts in the secondary replicas)
Regards,
Noob
November 7, 2016 at 10:40 am
szejiekoh (11/7/2016)
q2) As part of the release process, Do we also have to add the following line in the script ->"EXEC msdb.dbo.sp_update_job @job_name='Your job name',@enabled = 0 " so that the job doesn't run in the mirror instance
?
No, because if you do that the job won't run, and you don't want to be manually enabling jobs after an automatic failover
Add a first step to each job (on the primary, before you script out) to check whether the server hosts the primary/principle replica and if not to exit the job. Put exactly the same job onto the secondary.
For availability groups, see http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-7-detecting-primary-replica-ownership
q3) is there anyway to test run the jobs in the mirror instance ? is the only way = create a snapshot and change the job to run on the snapshot instead ?
Snapshots are read only, so unless your job makes no changes at all to the DB, that won't work.
Schedule a DR test, fail the system over to the mirror and test the jobs that way (plus lets you test your fail over and fail back processes, you do not want to be doing that first time in an emergency)
q4) If we change our db mirroring method to use alwayson AGs instead, is the method of migration jobs still the same as above ? (script out the jobs and run the scripts in the secondary replicas)
Yes
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
November 8, 2016 at 10:14 am
GilaMonster (11/7/2016)
szejiekoh (11/7/2016)
q2) As part of the release process, Do we also have to add the following line in the script ->"EXEC msdb.dbo.sp_update_job @job_name='Your job name',@enabled = 0 " so that the job doesn't run in the mirror instance
?
No, because if you do that the job won't run, and you don't want to be manually enabling jobs after an automatic failover
Add a first step to each job (on the primary, before you script out) to check whether the server hosts the primary/principle replica and if not to exit the job. Put exactly the same job onto the secondary.
For availability groups, see http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-7-detecting-primary-replica-ownership
q3) is there anyway to test run the jobs in the mirror instance ? is the only way = create a snapshot and change the job to run on the snapshot instead ?
Snapshots are read only, so unless your job makes no changes at all to the DB, that won't work.
Schedule a DR test, fail the system over to the mirror and test the jobs that way (plus lets you test your fail over and fail back processes, you do not want to be doing that first time in an emergency)
q4) If we change our db mirroring method to use alwayson AGs instead, is the method of migration jobs still the same as above ? (script out the jobs and run the scripts in the secondary replicas)
Yes
Hi Gila,
Thanks for the guidance. On a side note, if we schedule a DR test by failing over to the mirror, how do we prevent data generated in the mirror from syncing back to the previous principal ?
Does that means once we failover to the mirror database, we will suspend the mirroring back to the original principal and begin DR testing, once the testing is finish, we will remove the current principal (original mirror) and bring up the original principal + setup mirroring again ?
Regards,
Noob
November 8, 2016 at 12:16 pm
szejiekoh (11/8/2016)
how do we prevent data generated in the mirror from syncing back to the previous principal ?
why on earth do you want the synch to stop??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 9, 2016 at 10:10 am
Perry Whittle (11/8/2016)
szejiekoh (11/8/2016)
how do we prevent data generated in the mirror from syncing back to the previous principal ?why on earth do you want the synch to stop??
Hi Perry,
Thanks for your reply.
In our DR test, normally office users will prepare some sort DR test cases which will insert/generate test data.
In our scenario above, if we failover to the mirror , these data will be generated by the office users/applications which are actually for testing purpose and should not be included in actual production usage.
In another term, you can see it as a mockup DR. Thus, how do we actually remove these "test data" when we failback to the original principal mirror ?
Regards,
Noob
November 10, 2016 at 6:07 am
szejiekoh (11/9/2016)
Perry Whittle (11/8/2016)
szejiekoh (11/8/2016)
how do we prevent data generated in the mirror from syncing back to the previous principal ?why on earth do you want the synch to stop??
Hi Perry,
Thanks for your reply.
In our DR test, normally office users will prepare some sort DR test cases which will insert/generate test data.
In our scenario above, if we failover to the mirror , these data will be generated by the office users/applications which are actually for testing purpose and should not be included in actual production usage.
In another term, you can see it as a mockup DR. Thus, how do we actually remove these "test data" when we failback to the original principal mirror ?
Regards,
Noob
Have the users insert into a dummy table then remove the rows when you have failed back to the original primary.
If this is at the application level have the users enter a dummy item into the app, after failover and checking remove the dummy item
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2016 at 8:42 am
Perry Whittle (11/10/2016)
szejiekoh (11/9/2016)
Perry Whittle (11/8/2016)
szejiekoh (11/8/2016)
how do we prevent data generated in the mirror from syncing back to the previous principal ?why on earth do you want the synch to stop??
Hi Perry,
Thanks for your reply.
In our DR test, normally office users will prepare some sort DR test cases which will insert/generate test data.
In our scenario above, if we failover to the mirror , these data will be generated by the office users/applications which are actually for testing purpose and should not be included in actual production usage.
In another term, you can see it as a mockup DR. Thus, how do we actually remove these "test data" when we failback to the original principal mirror ?
Regards,
Noob
Have the users insert into a dummy table then remove the rows when you have failed back to the original primary.
If this is at the application level have the users enter a dummy item into the app, after failover and checking remove the dummy item
Perry Whittle (11/10/2016)
szejiekoh (11/9/2016)
Perry Whittle (11/8/2016)
szejiekoh (11/8/2016)
how do we prevent data generated in the mirror from syncing back to the previous principal ?why on earth do you want the synch to stop??
Hi Perry,
Thanks for your reply.
In our DR test, normally office users will prepare some sort DR test cases which will insert/generate test data.
In our scenario above, if we failover to the mirror , these data will be generated by the office users/applications which are actually for testing purpose and should not be included in actual production usage.
In another term, you can see it as a mockup DR. Thus, how do we actually remove these "test data" when we failback to the original principal mirror ?
Regards,
Noob
Have the users insert into a dummy table then remove the rows when you have failed back to the original primary.
If this is at the application level have the users enter a dummy item into the app, after failover and checking remove the dummy item
Hi Perry,
The level of testing includes updates and quite some vast changes. It will be really hard to remove those "test data" other then voiding them all together.
Can't we just remove the new(current) principal and force the current mirror (original primary) to become the principal back again ?
Regards,
Noob
November 10, 2016 at 10:31 am
you'd have to break the mirror and re initialise it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2016 at 8:53 am
szejiekoh (10/31/2016)
Hi all,What would be the best practice to "copy/replicate" these jobs over ?
-- doing backup of msdb and frequent restore to the mirrored/replica instance ?
I think that you need to use SQL Server Multi Server Administration on the third server. It will allow you to keep all of the jobs in synchronized state all the time. Also you can keep this server as a quorum resource in cluster.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply