asp.net DB: How to handle when publishing on webserver?

Question

Should the ASPNETDB be left in default app folder created by Visual Studio when deploying on webserver? I have another DB that is in the mssql data folder, should that be placed in same directory as well? Im not clear on whether it matters one way or the other.

Answer 1

It depends what environment you are deploying your application to. Specifically, what database servers are available to you or what you are able to deploy.

If you have, or can install, SQL Server and have full administration rights over the instance then the easiest option is to attach both databases to that instance and change your connection strings appropriately. In this case both files should reside outside wwwroot, in a SQL Server specific directory.

I presume this is how the database in your 'mssql' data folder is being accessed. The connection string SHOULD NOT contain 'AttachDbFilename'.

The other option involves SQL Server Express Edition and User Instances. User Instances are created on demand to host a database when the application user does not have the right to attach it to a setup created instance. User Instances run in the context of the application user, ASPNET in your case, and lack advanced security features.

Visual Studio uses this same setup. ASPNETDB is likely using this method. The connection string SHOULD contain 'AttachDbFilename'.

A database that has been created in a user instance can be migrated to a setup instance. However, a database from a setup instance cannot be attached to a user instance because the security information is unavailable.

See Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005 and SQL Server 2005 Express Edition User Instances for further information.