| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2004-07-23 : 11:58:13
|
| Hello all. I just read the thread How Backups Work here, http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=37825.I am certain that if I had some courses on SQL Administration that the thread would of been easier for me to understand. But I haven't, so it isn't.So, here is my problem. Any advice in simple terms you can offer would be appreicated! We have a production database that gets backed up nightly. These nightly backup files are then moved to another server which restores them for other uses. When this backup and transfer is happening, our software application slows down considerably. Our latest backup file is 1.4GB in size. These are (to the best of my knowledge) full backups.What I am wanting to know is this. Can we do something with our backups to either shrink their size, or make the backup go faster? Is there a way to optimize SQL Server so that during backups the machine is not so bogged down? I know next to nothing about SQL administration. So I am sure that my explainations leave more questions than answers. I am happy to look at configuration settings and such if that would help you help me. I have heard a couple of buzz words like Differential Backups and Transaction Logs, but I don't really know what those are. I am currently reading some help files, but have not found the holy grail of information yet.Thanks everyone!Adrian |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-23 : 12:03:49
|
| Yo, Pool dude...been awhile...Who set up the back ups?1.4 gb isn't all that bigWhere is the backup being done to? a local drive or across the network?And what about the other server?Is it read only?And what abouth the main server, how many updates, deletes and Inserts are there?What's it used for?A backup strategy is all based on what the database is used for and how it's used.Brett8-) |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-07-23 : 12:14:22
|
| I have been busy lately! Most of the SQL stuff has been simple, so I have not relied on you guys. But I know where to come when I need answers!Here is the setup.I work for a social services agency, in fact we oversee all of the adoptions and most of the foster care services for the state of Kansas in the United States. The backups are being done to the local drive in a subfolder. Then that backup file is copied to a reporting server and a development server, which then do their own restores. The reporting server is read-only. The development server is wide open.Some information about the database being backed up: SQL 2000. Current production database has 785 tables and 4957 stored procedures, and 61 user defined functions. Most of these stored procs are the standard select, insert, update and delete queries.Like I said, the database stores highly sensitive information about the adoption and foster care cases. Our operation works 24 hours a day 365 days a week. Our slowest time is after 5 PM.If I did a backup right now through Enterprise Manager, it takes approx 5 minutes. Transfering that file across the network to the reporting and development server takes another 5 - 10 minutes. I don't know jack about our network other than there are an impressive number of blue cables running throughout the building!I hope this helps!Adrian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 12:22:57
|
| "Blue Cables"? There's your problem, get them changed to Red. :)I've not known a "significant" slow down on SQL during backup. I don't know about the network copy - that might be grabbing bandwidth away from the things that are trying to talk to the SQL box itself - but I do know that our SQL box at our ISP has two network cards, one of which conencts it to a separate LAN that just carries the backup traffic. I suppose that's the one they use the Red cable for!Kristen |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-07-23 : 12:46:10
|
| Hmmm...let me write that down, "get red cables and see if that helps". Got it!Adrian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-23 : 12:48:56
|
| Does nightly backups mean 5:00PM?And again, who set up the backup?Do you know the schedule.Sounds like 5:00PM is when someone set up database integrity checking.Can you look at the server? Dis you/they use a maintenance plan?Do you have any scheduled jobs that do maintenance.My guess is the Integrity checks, Index reindexes, ect... not the dumps...Need mor info...Brett8-) |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-23 : 13:27:21
|
| You also say that you perform the backup to a subfolder on the SQL Server. Is this folder on the same drive as the data file? If so it may be that you are running into disk contention issues that are slowing down reads and writes to the same disk. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-23 : 13:49:05
|
quote:
Originally posted by gwhiz
You also say that you perform the backup to a subfolder on the SQL Server. Is this folder on the same drive as the data file? If so it may be that you are running into disk contention issues that are slowing down reads and writes to the same disk.
This is most likely the situation. The default backup path is to the same drive where the datafiles are located.An inexpensive solution would be to add a separate drive for the backup destination. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 14:00:15
|
Have you noticed any improvement with the Red cable, or is it a bit early to tell?Brett / ajthepoolmanThe following should give you the recent backup history, without the bother of trawling around the Wizard and his noxious cousinsSELECT TOP 500 database_name, backup_start_date, [Type] = CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff.' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'F.Group' ELSE 'Dunno' END, backup_sizeFROM msdb.dbo.backupsetWHERE database_name = 'MyDatabaseName'ORDER BY backup_start_date DESC Kristen |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-07-23 : 17:13:14
|
| Red cables didn't help. I will pick up some yellow ones.Here is some more information for you guys.We have a Database Maintenace Plan and under the Complete Backup tab is the following information. Back up the database as part of the maintenance plan is checked. Verify the integrity of the backup upon completion is checked.We are writing to Disk. The disk we are writing to is partitioned. The sql program files are on one partition, the backup is written to another partition.Create a sub-directory for each database is checked.Remove files older than 3 days is checked.Backup extension is BAK. Schedule is daily at 8:30PM (so technically the backup only takes 3 minutes).The Transaction Log Backup tab is blank. We have 7 Jobs on this server. 6 of them run daily with one running every 3 days. 4 of the 7 Jobs are also backups. In fact, one of the backups is on the same production database for the Database Maintenance Plan. Might look into why we need that one. Otherwise, they backup different databases. Some info on the Jobs:One is scheduled to run at 5 AM everyday.(populates some base tables)One is scheduled to run at 6 AM everyday.(DTS package)One runs every three days at 8:30 PM (backs up a small db)One is scheduled to run at 11 PM everyday. (production db backup)One is scheduled to run at 11:10 PM everyday. (backs up a small db)One is scheduled to run at 11:30 PM everyday. (runs a small cursor against one table)One is scheduled to run at 11:45 PM everyday. (backs up a test db)On the four that do backups, here is the general syntax:BACKUP DATABASE DB_Name TO DISK = 'D:\Backups\SQLReport\Prod.bak' WITH RESTART, INITBrett, as far as I know one of our former directors set up the maintenance plan. He has been gone for about a year now though. His background is in programming, not administration.Adrian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-24 : 00:50:43
|
| NO Transaction Backups right? (Clearly not in this maintenance plan, but there's nothing else doing them is there? Another maintenance plan?)Is your Data Recovery Model set to SIMPLE (Enterprise Manager : Right click database : Properties : Options)?If not then your log is growing and growing, and maybe the backup is having to work hard to trawl through it and that is causing performance problems.What size is the log relative to the data file? (It's on one of the other tabs in E.M. where OPTIONS was.)Kristen |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-07-24 : 01:36:19
|
| Use sisoft sandra to determine your disk io rates MB/s and your bandwidth between the two machines. Gigabit networks run somewhere between 50-100MB/s, while 100Mbit runs at 8MB/s and good raid disk arrays between 30-100MB/s on writes and between 50-200MB/s on reads.A good raid controller card for scsi 320 will cost around $600 and a good gigabit card around $250. Ultra 320 Disk/15Krpm, Enclosures, SAN, Fiber, and Gigabit backbone will obviously cost more. If you already have the infrastructure the price shouldn't be a problem otherwise it may get costly.If your database is large and you have a powerfull machine with multiple cpu's then also take a look at a product called SQL Lite Speed 500-1000 dollars depending on number of cpus. Some more things to consider are number of processes/jobs running on the machine at the same time, any locks or blocking that may be occuring, splitting the backup to using file groups and having a rotation schedule for the file groups, splitting the data, log, and temp to separate raid arrays. |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2004-07-24 : 04:12:40
|
| We have a 6gb database and can back it up in about 10-15 mins to disc, i can then zip it and copy it without much degredation in performance (the users havent noticed it but i can see the server getting hit pretty hard during the zip)The other option might be to setup a log shipping process that runs every 30 mins, that was you can go from doing a full backup to doing smaller chunks.I dont think 1.6gb is much and should take bugger all time to do. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-07-24 : 12:42:32
|
| 250GB /hour at 70MB/s using raid 10 array fibre channel ultra 320 15KRPM drives on a hp msa1000.Backup 2TB in 8 hours. MSA is connected via fiber to hp dl 580 w/ 4 xeon 4MB L3 cache and 8GB RAM.Less than 5% cpu utilization and no impact on any other database since it's on separate backplane/lun/array.Using SQL Lite 2TB takes 4 hours but cpu utilization goes between 25-50%. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-07-26 : 10:39:34
|
| Hello again all. Hope everyone had a good weekend.Kristen, I don't believe anything else is doing a Transaction backup. I know that the maintenance plan isn't. I will check those jobs and see if books online gives me any insight to the generic syntax that is being used.Regarding the file sizes, here is what I found:Database_Name >> Properties >> Options Tab >> Recovery Model is set to SIMPLEDatabase_Name >> Properties >> General Tab >> DB Size: 1879.17 MBDatabase_Name >> Properties >> Data Files Tab >> Data File Size: 1501 MBDatabase_Name >> Properties >> Transaction Log Tab >> Transaction Log Size: 379 MBBoth Data Files and Transaction Logs are set to grow automatically by 10%.We set up some performance testing over the weekend. We will look at the logs and see when our server is taking the biggest hits. I really appreciate all the help so far! Hopefully I can find something that will help this server do it's job more effectively!Thanks again!Adrian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-26 : 11:39:15
|
| Your Recovery Model is set to SIMPLE so you don't have to worry about transaction logs growing, or any of the other stuff I mentioned as possible problems.Just by way of a sensibility test, your database is 1.5GB and I've had a quick look at one of my clients boxes; they use Maintenance Plan Wizard to do backups. Their main DB is 3GB, backup time is 1m30s, so your 5 minute backup is looking to be a little on the slow side.The client has a Dell server with a RAID5 disk system (everything on the one RAID, partitioned for C: and D: - so its about as Basic as they come, but machine is new (April 2004) with 4xCPU)Kristen |
|
|
|
|
Still confused over backups