Best Way to Add Disk Space to a Large DB

Question

Best Way to Add Disk Space to a Large DB

markenash

Mr or Mrs. 500

Points: 507
More actions
January 23, 2008 at 1:41 pm

#185845

We have a relatively large, heavily used database on a SAN that consists of five 100 GB Primary datafiles on five seperate Drives. The log files are on seperate Drives. We want to add 200 GBs to the Data Protion of this database. We have assumed it would be better to add 2 more 100 GB Primary datafiles on 2 more new Drives, rather that simply increase the existing five 100 GB files to 140 GBs. More queues on the Windows Server.
Any thoughts on this plan.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

bnordberg SSCertifiable Points: 5379 More actions · Answer 1

That all depends on your HBA and switches and disk config on the SAN. If all the other areas are underutilized then sure it will help. But if your HBA is overloaded then you've gained nothing except more complexity in your setup and a more complex DRP.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

If you cannot afford downtime, you may have to consider adding new drives.

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

I remeber dynamic volume expansion should add extra space without downtime. This may apply to your case if needed.

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 4

Our HBA', switches and SAN are not overutilized and not limiting factors. We will continue with our original plan and add 2 new drives. Thanks to everyone for the information.

humbleDBA Hall of Fame Points: 3490 More actions · Answer 5

Hi markenash

Just so I understand you right...are you placing the data file for a specific DB that is 100 GB in size on just one pair of drives (via a LUN to present the volume to your server)?

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 6

We plan to allocate two new drives, about @108 GBs each.

We will the create 2 new datafiles of 100 GBs and place 1

new datafile on each of the 2 new drives.

humbleDBA Hall of Fame Points: 3490 More actions · Answer 7

Hi Markenash,

Are you not having performance problems with this setup? Usually, with such heavily used DBs of such size I would have thought you'd need multiple drives on RAID 10 to cover the IO throughput, which would be presented as volumes to your server for your data files (with the possibility of separate volumes for non-clustered indexes) and RAID 1 or RAID 10 for your TLog files.

Have I misundestood what you are saying?

[edit] Also, meant to say, to have the drives more than 80 - 85% full is going to degrade performance significantly, too ( based on 100GB/108GB).

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 8

We just need to expand this database as it is growing.

All of the existing SAN LUNs are RAID1.

humbleDBA Hall of Fame Points: 3490 More actions · Answer 9

OK. Guess I'm at a loss on what to suggest. If my understanding is correct, this is all for one DB and you've filled up a number of drives already, and are about to expand the space for this DB by adding another couple of RAID 1 drives that will have more than 85% of their capacity taken up by the data files. Also, any increase in DB space will pretty much be going just to that drive.

This is nothing like I've seen before (don't know if anyone else has?). Usually, I set drives and space up based on estimated IO throughput, space required, archiving policy, etc. From there I design the drive subsystem, be it SAN or DAS, based on the requirements - which more often than not are dictated by IO's alone. This is often, for a read/write DB, a RAID 10 LUN for Data, and RAID 1 for TLogs. If the need is there, then tempdb data and tlog on their own LUN sets, and again if performance requirements dictate, we may even seperate nonclustered indexes onto their own LUN set. Where money is an issue, and data recovery is not so critical, then we may go for RAID 5 for the data files, especially if the DB or sections of it are Read-Only. Striping data files over a number of spindles ensures we get the performance efficiencies of multi-spindle reads and writes. We also make use of multi-data files based on cpu numbers for thread efficiency.

Don't know if this helps any?

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 10

Well thanks for the info. I wanted input from other DBA's.

We have the ability to increase the size of existing Drives (SAN LUNs) along with adding new Drives. In discussions with the Product Software Development Team (Microsoft Partners) they felt either approach was OK. This Product has gone thru multiple upgrades with the addition of a wide array of new functionality as it has matured, and the Hospital has grown significantly thru mergers since it was installed 6 years ago. Growth / IO have been and will continue to be difficult to project.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 11

Correct me if I'm wrong but it sounds like the

Product Software Development Team

are software folks - if I was looking for SAN configuration advice I'd be talking straight to the SAN folks, not to software developers.

The best answer to your questions is going to depend on your SAN fabric, the brand & model of SAN in question, etc. there is no such thing as a generic SAN and each and everyone has different capabilities - an HP EVA != Hitachi Lightning != EMC Clariion != EMC Symmetrix != 3Par != NetApp...

Many/most enterprise storage vendors offer/like/prefer to come in and perform periodic configuration reviews/audits and most aren't shy about offering advice on how to make the best use of their products.

Joe