July 1, 2009 at 9:29 am
I have set up mirroring for a MOSS 2007 application and everything is working. My question concerns the nightly full backups and the hourly log backups that occur from 9 am to 5 pm Mon - Fri. And the question is this - Let's say that the above backups are all scheduled on server A and a failover occurs and server B becomes the primary. I'm pretty sure the backups that run on server A will fail. So if I had the same backups running on server B I would get good backups. But I don't really want to have a full set of scheduled backups on both servers with one set working most of the time and the other set blowing up most of the time.
What is the "best practice" for ensuring that you get good backups when you are trying to backup a potentially moving target??
Thanks - Any insight or experience with this is appreciated.
Joyce
July 1, 2009 at 11:49 am
Unfortunately, the backups will fail when the active node fails over to the passive node if the backup job is currently executing. You can have a job scheduled to monitor the backup jobs and if it has failed status kick-off the backup job.
July 2, 2009 at 9:34 pm
Yes , when the failover occurs the backups will fail as your principal will be mirror and viceversa .You need to have the same jobs configured on the mirror as well .
So the backups will run on the server which is principal .
you can create a WMI alert to send you a mail related to failover and then you can disable the jobs on the standby server or simply let the jobs fail for that database.Its not easy to disable the job(s) using a cursor .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 6, 2009 at 8:59 am
Thanks for your help. This is not going to be much fun because I have 13 SharePoint dbs and I currently have the full nightly backups and the 9:00am - 5:00pm log backups set up as maintenance plans.
It sounds like now I am going to have to break everything out into its own separate job and either do the WMI alert or something like the following code I found (courtesy of this link - http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/ba56240d-7182-4c92-b387-ff2068082e10/)
declare @is_db_principle bit
set @is_db_principle = (select mirroring_role from msdb.sys.database_mirroring where database_id = db_id(yourdabatasename'))
--Then enable or disable jobs according to the above
if @is_db_principle = 1
msdb.dbo.sp_update_job @job_name = 'yourjobname', @enabled = 0
else
msdb.dbo.sp_update_job @job_name = 'yourjobname', @enabled = 1
July 7, 2009 at 8:59 pm
If you are scripting your backups you can check the database status in sys.databases and only backup those databases that are available. The same script can then run on both servers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply