August 6, 2010 at 10:59 pm
Hi All,
I was doing testing with database mirroring on sql server 2008.
intrestingly it gets done and now i can fail over to my mirror database perfectly.
Now, i would like to know what about database and transactional log backup...
do i have to create backups on both instances (Principal,Mirroring) ??
or simply on Principal instance ?
kindly confirm
August 6, 2010 at 11:09 pm
You can't take the backup of DB which is in the restoring state. Create a job which takes the backup of only principal DB. This can be found from database_mirroring catalog.
-Lucky
August 7, 2010 at 9:58 am
You actually need to set up your backups on both servers. The trick is getting them to run on only the server with the principal database.
I am finishing an article where I explain how we did this at my last employer.
As a hint, check out event notifications and Service Broker.
August 7, 2010 at 12:59 pm
thanks to both of you for your reply.
may i have that document so, i may create jobs on both instances 🙂
August 7, 2010 at 2:45 pm
fawwad (8/7/2010)
thanks to both of you for your reply.may i have that document so, i may create jobs on both instances 🙂
The second half isn't finished yet. When it is, I'll have it published here on SSC. Until then you should start doing the reading I suggested.
August 8, 2010 at 9:55 am
yes sure 🙂
i will look at notifications and service broker.
never read about service broker before, its in my study plan but a bit far.
thank
August 8, 2010 at 11:08 am
Well, do understand what I did to implement failover for database specific processing you are going to need to understand the basics of Service Broker and Event Notifications.
August 8, 2010 at 11:37 pm
Using Event Notifications for the backups is overkill. All you need to do is to check the status of the database and not perform the backup if the database is not in an online status. Also, you should check to see if the database is log shipped before performing transaction log backups.
August 9, 2010 at 6:52 am
I have to disagree. Using event notification and service broker permits the automating the failover of database specific processing, the notification of appropriate personnel that a failover has occurred, and if needed the resyncing of SQL Server Logins on the new principal server.
All of these were needed when I implemented database mirroring.
If you only check the status of the database prior to running a backup or accomplishing specific processing, how do you notify someone that a failover has occurred?
Our last failover occurred because the principal server lost connection to the SAN and the Application Server failed to failover so that batch processing could run against the mirror.
If there had been no notification this could have gone unnoticed and unresolved for hours.
August 9, 2010 at 8:40 am
You're preaching to the wrong person. I documented how to use event notifications with database mirroring 3 years ago. However, the poster didn't ask about all of the stuff you called out. He only asked about backups. Backups should not need to rely on event notifications to activate them. What if you have some databases that are live on the mirror and some that are not? Then what?
Backups should be set to run on the mirror at all times and it should be made robust enough that it knows how to handle an offline database properly.
Backups are too critical to add dependencies to their proper functioning. Treat them separately from the rest of your processes.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply