SQL Server 2005 and VLDBs
-
Hi everyone -
I would like to know about key things a DBA should know in working with Very Large Data Bases. Please be as detail and specific as possible. Some things that I can think about pertain to backup/restore (files and filegroups), vendor backup software, compression, partitioning, performance related issues. I am a mid level DBA and don't have the depth in working with VLDBs.
Thanks very much.
sheppc1214.
-
The question you asked is very generic and open ended. As per my opinion, resource (Memory, CPU utilization, space etc) manangement is the biggest challenge when you manage VLDBs.
Ofcourse business continuity plan is again an area which you should give ample attention.
-
Ok, to be more specific, I'll take backing up VLDBs. If my database is 1TB, how do I perform backup with files/filegroups?
Thanks,
sheppc1214
-
sheppc1214 (6/7/2009)
Ok, to be more specific, I'll take backing up VLDBs. If my database is 1TB, how do I perform backup with files/filegroups?Thanks,
sheppc1214
I haven't had to deal with a database of that size, yet, so haven't looked at filegroup backups and restores at this time. But, if I found myself there I would start by reading Books Online (BOL), the SQL Server Help System. You can access it by pressing the {F1} function key while in SQL Server Management Studio (SSMS). It does provide a fairly good overview of the processes involved. Once you have read that, and you still have questions, here is the place to come.
I'm sure others may have other sources of information on this and will be more than happy to provide them to you.
-
sheppc1214 (6/7/2009)
Ok, to be more specific, I'll take backing up VLDBs. If my database is 1TB, how do I perform backup with files/filegroups?Thanks,
sheppc1214
I haven't meet that large database, maybe you can separate some stable data into another database. can we know what make your database so large?
-
You can make use of quest product "litespeed" or any third arty products for reducing the backup time.
http://www.quest.com/Quest_Product_Demos/LiteSpeedforSQLServer_demo_043007/chap00.htm
MJ
-
There are probably several options for your backups. On one of our databases we are lucky enough to have "monthly" data where each month I can create new filegroups and files. As these get older, I can do a final backup of them and change to read_only. The regular backups then are just of read_write_filegroups. We burn the read_only to DVD and keep some on tape.
Look up piecemeal restores in BOL for some ideas.
Some other good ideas for maintenance are here:
Good luck.
-
Compressed backups (either native SQL 2008 or 3rd party)
Differential backups (maybe)
Striped backups (maybe)
File/Filegroup backups
Fast backup drive(s) with enough space to keep one or two
A carefully designed and well tested database restore plan that takes into consideration downtime SLAs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Log shipping can actually become your key backup strategy in those cases. Combine that with file backups (and split the database into multiple files, either through splitting tables and indexes, or by partitioning data within tables), and you have a pretty good continuity option.
All in all, 1 TB isn't that big these days, in terms of storage space and all that. My computer at home has more HDD space than that, and I'm looking into adding an SSD to that. It's only big because it's "one thing" - one database. Break it up, and it'll be easier to manage.
One thing to watch out for, is that DBCC CHECKDB and other common maintenance commands can take WAY too long on such a file. Federating the database, if that's an option, into multiple databases, can help with that. Again, it's not that big a deal to handle, and there are several options available, like PHYSICAL_ONLY on CHECKDB. Other maintenance, like index rebuilds, can also take a lot of time, and need to be planned accordingly and broken into smaller tasks quite often.
Another thing that can happen with VLDBs is sudden, huge growth, if you have autogrow set to a percentage of database size. That one can be a major disk killer.
SQL 2008 Enterprise's database compression and backup compression can alleviate some of these issues, of course.
With 2005, you can put data into read-only files and put those on compressed NTFS partitions, if that'll help. Read-only files, compressed or not, can also often speed up selects from that data, since SQL will bypass the whole locking issue. That can be a big deal on tables with lots of rows.
Compression on the disk, either through SQL 2008's features or through compressed NTFS partitions, can speed up reads significantly. IO is usually more of a bottleneck than a few extra clock cycles for the CPU and a little extra RAM. (This doesn't just apply to databases.)
There are some caveats on using compressed partitions with SQL 2005. Books Online has the details. The main one is that the data has to be read-only, but it'll be worth some research and a little time in Google/Bing/Yahoo/Dogpile/whatever, to make sure you know what you're doing before you go that way.
(I haven't used a compressed partition for a database in quite a while, so I may be misremembering a few details. That's another reason to do some research.)
Does any of that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks very much for your thoughts on woring with VLDBs.
Its truly apprerciated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply