March 9, 2005 at 2:48 pm
I am having some issues with my backup plan effecting the production environment. Currently I have about an 8-9 hour window to do the full backup while there are no users on the system. We will be approaching a 24/7 usage level.
There are 2 systems accessing the database. A VB application that inserts records into specific tables and a web application that mines data and allow manipulation and additions to the data.
My current backup plan is a full backup every night and only keep the most recent due to space issues. I also do a full backup every Sunday, only keeping the most recent. I back the transaction log up every night and keep a weeks worth of those.
I have a situation where the SQL backups are effecting the web application causing things to time out.
I think the log backup and the database backup may be overlapping. Could that explain what is happening?
I was thinking to increasing the log backup to run once an hour (or 1/2 hour maybe) and keep those for a week to match the Sunday backup.
I'm looking for recommendations on how to back up the database files without impacting usage. My file size stats are:
Database Size: approx. 200GB
Backup Files: approx. 164GB
What would the ideal backup strategy be?
I also have other databases on the server that are much smaller that need backups also. I am trying to have them run way before the large database backups are executed.
Any help is greatly appreciated.
March 9, 2005 at 3:17 pm
The main thing that you will see with the transaction log backup and full backup running concurrently is that one or the other will be blocked until the other is finished. Only one backup operation can execute on a particular database at a particular time. You may want to review backup strategies in Books Online to get a better feel for the usage of the different types of backups. Your suggested strategy of running log backups every 1/2 hour or every hour makes much more sense than what you currently have. Theoretically, backups should not impact production to a great extent. Obviously, though, anything which is using resources on the box is competing with the application for those resources. This isn't to say that you should back off from your backups. I'd rather have that overhead than not be protected from potential disaster. If your backups are adversely affecting your application, you may want to look at upgrading the server. Something else to look at is the location of the data files, log files, and backup files. Are they all on separate devices? Obviously, a backup is going to be disk I/O intensive. If the backup is going to the same drive as the data is stored on, this could create a considerable bottleneck.
Steve
March 9, 2005 at 7:20 pm
You might want to explore differential backups or if you have some tables for say historical purposes and some for the live transaction consider a file group stratagey where you can backup in groups instead and resotre in groups (make sure you test after going this route that you can restore successfully). Another option is to add more backup devices and span the devices (more threads faster completetion). Aslso what type of backup device do you currently have. May need to consider and updated device with a higher throughput.
March 10, 2005 at 12:07 am
You may think of having a mixture of Full, Differential and T-Log backups.
As per your scenario, having 1 FUll backup per night seems fine. I would recommend having Differential backups occuring every 12 hours (i.e. 2 Differential Backups in a day). Also T-Log backups occuring every 1 hour.
Your backup strategy would look something like this :
A. full backup 12:00 AM -- deleting the last full backup
B. Differential backups -- delete old backups older than 24 hours
6:00 AM , 12:00 PM & 6:00 PM
C. T-log backups -- delete old backups older than 7 hours
1:00 AM 2:00 AM
3:00 AM
4:00 AM
5:00 AM
6:00 AM
7:00 AM
-
12:00 AM
March 10, 2005 at 7:43 am
I really think the bottle neck is I/O... In a week or two I will be connecting a new SANS device to that same machine, then the backup will be able to write to those disks while the apps use the database on the current attached storage array.
That should hopefully solve the issue.
In theory, the SQL backup should not impact performance of the SQL services, it's more of an I/O issue?
A lot of the data resides in a large table due to the transactional nature of our system. Would the backups put any locking on the table?
March 10, 2005 at 8:17 am
Backups will not lock any data. After the backup has "completed", it goes back to the transaction log and picks up any changes that occured during the backup.
Steve
March 10, 2005 at 8:31 am
So if the transaction log backup ran the same time the database backup ran, then that would be create locks for sure?
That would explain what we were seeing.
March 10, 2005 at 9:58 am
No. If the tlog backup runs at the same time the db backup runs, whichever one starts first will BLOCK (not lock) the other until it finishes. Nothing at all to do with data or data access except for the overhead caused by the backup, which is mostly input/output, and this COULD be significant if the backup is being written to the same drive that the data resides on.
Steve
March 10, 2005 at 12:07 pm
You may want to explore SQL Litespeed. You will be able not only save the time, while doing backup(compare to the regular by MS), but also you will be able to save a lot of space. Ex: over the weekend I backed up my 600GB Database with SQL Litespeed- it took 1 hr 15 mins, resulting in 90GB backup file. Can't beat that.
March 10, 2005 at 12:28 pm
They are giving me a call next week. Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply