SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Still confused over backups
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 07/23/2004 :  11:58:13  Show Profile  Reply with Quote
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 - 07/23/2004 :  12:03:49  Show Profile  Reply with Quote
Yo, Pool dude...

been awhile...

Who set up the back ups?

1.4 gb isn't all that big

Where 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.



Brett

8-)
Go to Top of Page

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 07/23/2004 :  12:14:22  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/23/2004 :  12:22:57  Show Profile  Reply with Quote
"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
Go to Top of Page

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 07/23/2004 :  12:46:10  Show Profile  Reply with Quote
Hmmm...let me write that down, "get red cables and see if that helps".

Got it!

Adrian
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/23/2004 :  12:48:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
Performance Tuning Tips for SQL Server Backup and Restore:

http://www.sql-server-performance.com/backup_restore_tuning.asp

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/23/2004 :  12:48:56  Show Profile  Reply with Quote
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...



Brett

8-)
Go to Top of Page

gwhiz
Yak Posting Veteran

USA
78 Posts

Posted - 07/23/2004 :  13:27:21  Show Profile  Reply with Quote
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.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  13:49:05  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/23/2004 :  14:00:15  Show Profile  Reply with Quote
Have you noticed any improvement with the Red cable, or is it a bit early to tell?

Brett / ajthepoolman
The following should give you the recent backup history, without the bother of trawling around the Wizard and his noxious cousins

SELECT 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_size
FROM	msdb.dbo.backupset
WHERE	database_name = 'MyDatabaseName'
ORDER BY backup_start_date DESC

Kristen
Go to Top of Page

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 07/23/2004 :  17:13:14  Show Profile  Reply with Quote
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, INIT

Brett, 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

Edited by - ajthepoolman on 07/23/2004 17:18:11
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/24/2004 :  00:50:43  Show Profile  Reply with Quote
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
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 07/24/2004 :  01:36:19  Show Profile  Reply with Quote
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.




Edited by - ValterBorges on 07/24/2004 01:39:43
Go to Top of Page

scullee
Posting Yak Master

Australia
103 Posts

Posted - 07/24/2004 :  04:12:40  Show Profile  Reply with Quote
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.

Edited by - scullee on 07/24/2004 04:13:54
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 07/24/2004 :  12:42:32  Show Profile  Reply with Quote
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%.


Edited by - ValterBorges on 07/24/2004 12:47:57
Go to Top of Page

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 07/26/2004 :  10:39:34  Show Profile  Reply with Quote
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 SIMPLE

Database_Name >> Properties >> General Tab >> DB Size: 1879.17 MB

Database_Name >> Properties >> Data Files Tab >> Data File Size: 1501 MB

Database_Name >> Properties >> Transaction Log Tab >> Transaction Log Size: 379 MB

Both 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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/26/2004 :  11:39:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000