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:
Please share your thoughts and recommendations.
---------- 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
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: