Spreading Data Files

[fuller772]

I'm a bit new to database administration, but here's my problem. I have a 25GB db (soon to be 100GB after a data import). Right now all data is in one large mdf file.

I have heard for performance reasons it is wise to create one data file for each number of physical drives. In my case 5 drives.
If all of my data is in one file, once I create the other 4 files, do I need to spread the data across the files? If so how?

Should I just make 4 more 20GB files.

Your help is very much appreciated.

 

[JayKusch]

You need to read in Books OnLine(BOL) the section(s) on File Groups. Its a bit more complicated than just spreading them out.

In fact, you will want to create these filegroups BEFORE your import. WHat you need to decide is what table(s) will be accessed the most, size estimates for the future as well as a host of other items needing concideration. There are MANY ways and reasons to create additional file groups, which by the way are "usually" refered to as NDFs. I beleive you are permitted 16 NDF files in SS2K.

Thanks

J. Kusch

 

[Catadmin]

Jay's right. It's not so simple an answer.

There are two things you should look at: Files and FileGroups. These are 2 different things and they both relate to your question.

Additionally, if you have that many disks at your disposal, you should check into the various RAID (I'd recommend RAID1 or RAID5. But that's an opinion) options unless you already have something like this set up. RAID is a great way to protect that soon-to-be 100GB of information from being completely lost if something crashes or gets fried.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[fuller772]

The drives are already setup in a RAID 5. I will do some looking into Files and FileGroups, thanks for the help.