SQL Express User Instance with multiple ASP.NET Sites

Question

How can I get two separate ASP.NET sites, in this case a Web Site using Entity Framework and Web Service, to use the same local database mdf & ldf?

I looked around and I think that this kind of local DB is called a User Instance - at least they way I'm currently using it. I wasn't sure if it would work or not until I found the below info from the MSDN Library and now I'm really not sure if it will work.

User instance scenarios include:

...

Dedicated ASP.NET hosting using Windows Authentication. A single SQL Server Express instance can be hosted on an intranet. The application connects using the ASPNET Windows account, not by using impersonation. User instances should not be used for third-party or shared hosting scenarios where all applications would share the same user instance and would no longer remain isolated from each other.

Will this work right away without any extra configuration? When I try to add a DB to every successive application, I get a file in use error if anything else is using the DB which leads me to believe that I can't do it this way. However, if I stop the DB from being used, then add it, and then start it up, it seems to work fine. Does anyone have any reassurance for me that it won't be corrupted or experience any errors by using it this way?

Preferably, the mdf & ldf would be stored in the Web Site's App_Data directory (|DataDirectory|). If I do it this way, I don't think I can reliably use relative addressing for the Service unless I put it in a sub directory for the Site, is this correct?

edit: I'm using SQL Server Express because this is an assignment for school, so I pretty much have to use it. If there is a way without User Instance I'll try to do without it.

Answer 1

You can't use User Instance for this. This allows just one client at a time to connect to your database. When your two applications use the same credentials, you get the error you describe. When your two applications use different credentials, you essentially get two separate instances (also not what you want).

Besides, user instances are only supported on SQL Server Express. I don't know what your production environment looks like, but it probably does not run on SQL Server Express?

You should use AttachDBFilename in the connection string of your web application to point to the local database: AttachDBFilename=|DataDirectory|\MyDB.mdf.

In your web service you also use AttachDBFilename but it depends on the deployment location of the web service where to point it to.