| Author |
Topic |
|
notshai
Starting Member
8 Posts |
 Posted - 2011-05-18 : 11:43:11
|
| hi i am planning on migrating a stand alone SQL05 with a 22GB DB to TWO SQL08 with mirroring (one primary the other a mirror per MS recommended congif).currently i run 6 FULL daily backups (every 4 hours) and two differential backups per hour.what is the best backup configuration for a mirror environment? the mirror guarantee no loss of data for how long? (assuming the main dies, is the mirror updated with the last transaction from 5 seconds ago?)i would like to have a solid backup for in case of data contamination / data sabotage, what would be the best solution on top of the mirroring? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
notshai
Starting Member
8 Posts |
Posted - 2011-05-18 : 15:54:02
|
| would you recommend that* for a mirrored situation as well? is there anything to consider differently with a mirrored environment?i feel safer with a full backup, takes 18 minutes to complete, i will try your recommendation for a week and see the diff.*" daily full backups, differentials 12 hours after the full, and incrementals every 15 minutes" |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-18 : 16:06:49
|
quote:
is there anything to consider differently with a mirrored environment?
Only that mirroring requires the Full recovery model, and you should be making regular transaction log backups if you're not already. I'd suggest 15 minute intervals for those. (I think that's what Tara meant by "incrementals") |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-18 : 19:13:09
|
quote:
Originally posted by notshai
would you recommend that* for a mirrored situation as well? is there anything to consider differently with a mirrored environment?i feel safer with a full backup, takes 18 minutes to complete, i will try your recommendation for a week and see the diff.*" daily full backups, differentials 12 hours after the full, and incrementals every 15 minutes"
Just curious - by why do you feel safer performing full backups more often? As was previously stated, full backups are generally done no more often than once daily. As the database gets larger, performing a full backup daily sometimes cannot be done in the time available and then we see weekly fulls, with daily (or more frequent) differentials.In all cases, when the database is in full or bulk_logged recovery model - it is required that you perform frequent (at least every hour, if not more often) transaction log backups. If you do not do this, the transaction log will continue to grow until it fills the drive.The full recovery model is required for mirroring - and it cannot be changed to any other recovery model. You have to break the mirror to change the recovery model - and then you are going to have to rebuild the mirror.Now, to your other question: if the principal server/database dies, the mirror will take over immediately with all data *** if you set it up with both high safety and a witness server, and configure it for automatic failover ***. If you do not have it setup that way, the mirror will not automatically failover and there could be data loss.Jeff |
|
|
|
notshai
Starting Member
8 Posts |
Posted - 2011-05-19 : 11:35:51
|
| hi thank you all.currently were not in full recovery mode on the SQL05, so the tlogs aren't being backup as you mentioned, but will be obviously when will do mirroring.the reason why i feel "safer" with a full backup - in case of real DR, i prefer to not mess with differentials from more hours than necessary, it might be wrong thinking on my part. because our full backups take only 18 minutes to complete, its not so problematic, but we are starting to experience timeouts during the backup times and server performance is degraded, that in part has to do with the architecture and indexes but i want to take the system issues from the equations hence moving it to a 64bit 08 with 15k drives and more RAM, the initial question i had was not to critic the current way of my backups but HOW TO DO IT RIGHT on the new system.so i gathered from your replies - 1 full daily back, 4 daily differential, and 15min tlogs, is the way to go.i need to test a recovery with that new model, i guess i need to start digging on how to do a recovery from tlogs and differentials now :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-19 : 11:48:44
|
quote:
i need to test a recovery with that new model, i guess i need to start digging on how to do a recovery from tlogs and differentials now :)
Absolutely. Be able to restore a full chain. Even better, learn to do that without using the GUI (it's not hard). Practice, practice, practice until it's second nature, then write down the procedure so you or someone else can refer to it in case of emergency. In fact, if you have another server (not the principal or the mirror) that you can restore backups on, set up a job to do that automatically. This can be a standby server in case one of the others totally bombs and you want/need to reconfigure mirroring with new hardware.If you feel comfortable making more frequent full backups, you certainly can, but make absolutely sure you do regular log backups. Full and differential backups won't prevent the log from growing. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-19 : 21:46:47
|
| How often you backup your database is really dependent upon your RTO (Recover Time Objective). In other words, how long can the system be down while you are recovering from your backups. Then, depending on what that requirement is and how large the database is - you can determine how often you need to perform full backups, differentials (if needed) and transaction log backups.The transaction log backups are more related to your RPO (Recovery Point Objective). That is, how much data can you afford to lose if you have to recover from your backups. In most cases, a potential loss of up to 15 minutes is acceptable and why you hear that a lot of people recommend running transaction log backups every 15 minutes.Since you are also going to implement database mirroring - you are adding another safety net which can help reduce the potential data loss and allow for the system to be immediately available.Just note that this solution would only allow no data loss for any disaster scenario where your mirror instance is not also affected. If both systems are in the same data center - and you lose the data center, you need to have something in place to recover as much data as possible. Most companies solve this problem by copying the SQL backup files to tape and sending them offsite on a daily basis.Jeff |
|
|
|
|
SQL08 (w/mirroring) backup best practices?