Managing multiple customer databases in ASP.NET MVC application

Question

I am building an application that requires separate SQL Server databases for each customer.

To achieve this, I need to be able to create a new customer folder, put a copy of a prototype database in the folder, change the name of the database, and attach it as a new "database instance" to SQL Server. The prototype database contains all of the required table, field and index definitions, but no data records. I will be using SMO to manage attaching, detaching and renaming the databases.

In the process of creating the prototype database, I tried attaching a copy of the database (companion .MDF, .LDF pair) to SQL Server, using Sql Server Management Studio, and discovered that SSMS expects the database to reside in

c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabaseName.MDF

Is this a "feature" of SQL Server? Is there a way to manage individual databases in separate directories? Or am I going to have to put all of the customer databases in the same directory? (I was hoping for a little better control than this).

NOTE: I am currently using SQL Server Express, but for testing purposes only. The production database will be SQL Server 2008, Enterprise version. So "User Instances" are not an option.

Answer 1

The MDF has in it the table containing the physical path of all the database files, as they were on the instance it was detached from. You can overwrite the location(s) during the attach operation:

CREATE DATABASE <dbname>
 ON (name=dbfilelogicalname, filename='c:\myNewPath\dbfilename.mdf'),
 (name=dbfile2logicalname, filename='c:\myNewPath\dbfilename2.ndf'),
 (name=dbloglogicalname, filename='c:\myNewPath\dblogfilename.ldf')
 FOR ATTACH;