Database with Multiple Files and Filegroups Created by Application

Hi,

Yesterday I was asked by the sysadmins to enter SQL Login while they are in the middle of installin a new application with vendors so that the installer will automatically create a Database and login/user that will support the application. But the installer give me the option to type and choose one folder(usually other applications will ask where I want to put the data and log files). I left it at default hoping to change it later. Once we are done i went on and check the database and there sure is a database created but with MULTIPLE files and filegroups: 1 mdf file, 18 ndf file and 1 log file. The mdf file and the log file are in the default database path which I configured on the databasse settings before, but the other 18 secondary files are placed in some other location(SQL Servers Default).

Here are my questions:

Is it healthy to have this many files/filegroups without even knowing on what kind of drives and how many drives we put this files on?
From the naming I see that 9 of the file groups seems like they are index. Is partitioning an index a good idea?
What would be a best practice in this situation? Anything I should do after the application automatically creates what ever it wants during installation? I am planning to move the files to the default location using Create Database/For Attach (I cant use sp_attach since I have more than 16 files)
Does this all make any sense From DR perspective?

Please share your thoughts and recommendations.

Thanks.

---------- EDIT:

Since I meant file/filegroups, I have replaced the orginal post which says 'partition' with file/filegroups after @WilliamD pointed out in his answers. thanks

asked Jul 27 '10 at 06:51 AM in Default

DaniSQL
4.8k ● 30 ● 35 ● 39

Strewth. The most I've seen to date was 7 NDFs each with initial size of 500MB. I wouldn't have minded so much had that database ever had more than about 300MB in it!

Jul 27 '10 at 08:50 AM ThomasRushton ♦

(comments are locked)

1 answer:

Just to clarify, you are talking about secondary data files for your database and not partitions. These data files are used to physically separate tables/indexes or even partitions of tables/indexes from one another.

The advantage of splitting a database across multiple files is that I/O can then be split across different disks/arrays, allowing better performance and targeted tuning of the I/O layer. Some parts of a database may only be accessed rarely and can be stored on slow/cheap storage (archives), other parts may require blazing fast storage (objects that are queried/updated often).

As you described, all 18 secondary files have been dumped on the same storage. This will give you no real benefit over consolidating the 18 into 1 ndf as long as their location remains as stated.

So to answer each of your questions:

This depends upon how the system behaves when loaded with data and in full use. Do you/the software vendor have usage patterns to support the decision to split the DB into so many files? I would be against blindly leaving the 18 ndfs as they are. Talk to your software vendor, they made this decision for a reason.
Partitioning an index can be helpful (but is not really part of this situation). As mentioned before, storage tuning possibilities open up when the filegroup/file split has been done. Partitioning of indexes/tables is yet another technique, but requires table design changes and SQL 2005 Enterprise Edition or higher.
You can move the files around, but you must be sure what affects the move will have. E.g. If you move a highly updated file from a 10 disk RAID1 to a 3 disk RAID5 you will suffer. Again, talk to your vendor and find out how their software behaves before doing anything. If they have tricked out the storage design as noted, then they should have some answers.
In terms of DR you have no greater advantage than if all 18 ndfs in one ndf, as they are all on the same disk(s) and same controller. However; depending upon SQL Server version and edition (min. 2005 Enterprise Edition), it is possible to do piece-meal restores when your database is in multiple filegroups and files. This would allow you to restore a large database quicker, but is a quite advanced technique that requires a lot of preparation and testing.

answered Jul 27 '10 at 07:24 AM

WilliamD
25.3k ● 16 ● 18 ● 41

Thank @WilliamsD for you detailed answer. I just moved all datafiles to a single place and log to another. We cram everydata file of all databases in this server into a single drive, logs and Backups also have their own separate drive. I have no idea how the system will behave as I dont have knowledge of the application yet. But i might be able to optimize the IO once i get the idea of whats happning and also when we migrate to our new SAN in August. I have a question though: for piecemeal restore , isn't it a good idea not to partition the indexes in the first place since by the time you restore one table/filegroup your index might not be restored and hence users can not efficiently access the table?

Jul 27 '10 at 09:29 AM DaniSQL

@DaniSql, I am not too sure on the piecemeal restore and partitioned indexes. I think that the restored part would be accessible, as that is how it should work (the way I understand the description and how I played with it back then). I never tried it with partitioned data, but imagine it would work. Time to fire up the quattro and give it a go!

Jul 27 '10 at 11:54 AM WilliamD

@WilliamD: I havent used piecemeal restore either, but I'm just guessing it would be hard for end users to effectively use your restored table without restoring the indexes(which will take a while if they are partitioned all over the place). As you said there its better to test it to find out for sure.

Jul 27 '10 at 01:59 PM DaniSQL

(comments are locked)