February 16, 2007 at 12:25 pm
We have a Data Repository on a SQL 2000 SP4 server Standard Edition 4GB RAM. We are in the process of purchasing more SAN Disk space. When we add more physical disk space to our SAN I was wondering if I should add a new database disk group the the new LUN or just keep increasing the LUN and the existing database file size. Has anyone had trouble using and backing up 300GB + databases in one file? We are also using the backup from MS Sql. We do not have 3rd party SQL backup tool. Our livedb is about to exceed 300GB all in one primary file group. Any help would be appreciated, thanks.
February 16, 2007 at 1:04 pm
"Has anyone had trouble using and backing up 300GB + databases in one file? "
With a SAN, not using large database specifically.
For backups of larger databases, recommend performing parallel backups where multiple backup files are used. Each backup file should be on a seperate lun.
For example, have 3 luns mounted as S, T and U and run:
BACKUP DATABASE MyBigDB to
disk = 'S:\MyBigDB_db_20070216000000.bak'
, disk = 'T:\MyBigDB_db_20070216000000.bak'
, disk = 'U:\MyBigDB_db_20070216000000.bak'
As maintenance plans do not support multiple backup files, you will need to write some code to:
1. delete older backup files
2. Generate a unique backup file name.
SQL = Scarcely Qualifies as a Language
February 19, 2007 at 7:29 am
it only becomes a problem if you find it a problem, one person's large database is another's small?
Using multiple devices increases complexity, as does multiple filegroups. Litespeed will make short shrift of a 300Gb database.
I always have problems with SAN's < grin > if you're going to use multiple LUNs make sure they are seperate physcial spindles, ideally not shared and ideally raid 10. If you're currently using raid 5 then a comparable raid 10 would backup in roughly 25% of the time. ( available spindles equal - not total number of spindles )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 19, 2007 at 7:49 am
Thank you all for the replys. I will look into litespeed for future purchase.
February 20, 2007 at 2:48 am
- also with that kind of size, consider using filegroups !(check BOL)
Especialy if you are planning on migrating to sql2005 where you can perform piecemeal restores so you actualy get an advantage regarding DRP. See sql2005 BOL.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2007 at 7:45 am
Have backed up 600GB db to one file over 4 years ago with no issues. If you have the space (and time) it works.
Litespeed, SQL Backup, or other backup tools can definitely help. We did native and Litespeed (v2.x) backups with our large dbs.
February 21, 2007 at 11:14 am
I split all of our full Litespeed backups into 4 files, this includes a database approaching 3TB. I send different files to different SANs through multiple HBAs for better throughput.
If your SAN defines aggregates on specific drives, and your individual aggregates do not each touch all the drives, then shooting multiple files at multiple LUNs defined on different spindles should speed your backup times, provided you are not being held back by other factors (heavy workload, etc).
If your SAN spreads every aggregate across the full SAN (3Par, LeftHand, etc.), and you are connecting through a single HBA, then large files like backups should be hitting most spindles as a single file. However, I would still advise comparing backup times using 1 file, 2 files, and more. When specifying multiple files, the backup process will spawn multiple reader and writer threads and write to the files in parallel. Several third-party tool such as Litespeed and RedGate can also be configured to spawn multiple threads even when backing up to a single file.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply