I just deployed multiple databases for a new application the other day. Like a lot of software deployments these days they are done with installation packages. While this is nice of those that do the installation I personally don’t like them unless I am advised (in advance preferably) what they are doing besides installing databases. Call me a control freak and I will not deny it especially when it comes to the databases I am responsible for, but I like to know what is happening on my servers.
Anyway, after running the installation being the control freak I am I started to look at the database that were installed. While reviewing the initial size, the names, if they had additional file groups for indexes or used the primary and other basic information about them. One of the things that I noticed right off the bat was several of the databases had additional file groups and data files associated with them. That’s a good practice and something I like to do as an added layer for disaster preparedness, especially if the projection is for them to grow to be very large databases and according to the project these are.
What I noticed was there were three files like so.
You might think that to be a major problem causing issues right away. Since mdf files are usually associated as the primary data file and the primary file group the contains the system information (objects) . The industry standard for secondary data files and additional file groups is usually ndf extension and ldf is for the transaction logs of the database.
While it is not a best practice or industry standard SQL Server does not care one single bit. The file extensions are just naming conventions. If you really wanted to you could have your data file with an extension of .ldf or the transaction log with an mdf file extension.
The bigger issue this can cause is when other DBAs on the team have to perform maintenance or trouble shoot an issue and I am not around. It has the potential to be confusing
As I mentioned this is not a show stopper and the database will hum along just fine but I will be mentioning this issue during our next meeting and provide options on how to fix this sooner rather than later and hopefully avoiding any issues when not using industry standards and best practices is confusion.