Managing multiple customer databases in ASP.NET MVC application

Posted by Robert Harvey on Stack Overflow See other posts from Stack Overflow or by Robert Harvey
Published on 2010-04-20T22:59:23Z Indexed on 2010/04/20 23:03 UTC
Read the original article Hit count: 273

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about database