One item that many people find unusual about my DotNetNuke installation guides is my insistence on NOT using the Database.mdf, dynamically attached database file for the creation of a DotNetNuke database. In my tutorials, I have quickly dismissed the topic by noting that "issues" come up when trying to use a dynamically attached database and I leave it at this. Well after seeing more questions on this topic recently in the DotNetNuke.com forums I wanted to take a moment to put my $0.02 out there as to WHY I don't like it. I will do this by discussing quickly what the differences are in the two approaches, then I'll discuss the "downfalls" of each.
Differences in Approach
Everyone is most likely familiar with the common process for creating a SQL Server database, during the creation process two files are added the .mdf which stores the actual data and the .ldf which stores the log. When the database is created the files are typically placed inside the "Program Files" folder, storing them with the rest of the SQL Server files, although this location can be changed by the Server Administrator.
The dynamic attachment method uses a predefined database.mdf file that is located in the App_Data folder of the DotNetNuke installation. When the system connects to the database server this file is attached to the server and operates like a database on the server would normally, however, all data is stored inside the DNN location.
Why I Think Its Bad
One of the most common arguments supporting Dynamically attached databases is that they "save time". Well, That might be true for the first connection, however, in my experience the time spent troubleshooting later makes it a much longer process. The first thing to remember is that the database is attached and it has a name associated with it, DotNetNuke when working with DNN if you go to install a second DotNetNuke installation on the same server with the dynamically attached file an error will appear noting that the database already exists. For beginners, this can be a very upsetting experience.
However, the most problematic downfall of dynamically attached databases in my experience is that you must "Attach" the database before you can manipulate it in SQL Server Management Studio or SSMS Express. This is a very common stumbling block as individuals will know that the database exists, however, SSMS will not show the database at all until it has been successfully attached.
Overall
I feel that overall there are no compelling reasons that would direct a person to use a dynamically attached database rather than a standard SQL Server database especially when it comes to databases that need to be administered on a regular basis. If working in a test environment it might be helpful to have a dynamically attached database, but that is a limited use case, and for the most part DotNetNuke databases at one point in their life will need to be administered, backed up, restored, or moved. For these actions, a standard database is a good way to go.
Please share any comments/feedback below.