ASP.Net MVC SQLExpress Production Environment Update Scenario

Question

New to actually deploying a Asp.Net MVC web application with sqlexpress in the App_Data folder, how would I handle database updates after it is live and in use.

Here's my scenario and a few questions:

I have an Asp.Net MVC 2 Web app w/ sqlexpress databases: [myappData.mdf, aspnetdb.mdf]. I was planning on publishing this to the production server with the databases included in the App_Data folder.

Question 1

When my site is in use and users have added new data into the database and I have added some new features which require some structural database additions how would I handle publishing the new database files while keeping all the data that users have added to the database?

Question 2

Also once I start debugging my app locally I can't connect to it in sql management studio, I get a file in use error. So once it goes live on the production server and a user is having a problem can I not browse through the database using sql management studio?

Question 3

Right now I'm connecting to the db with the following web.config string, are there any issues with this in a production environment that will have sqlexpress installed?:

<add name="myAppDataEntities" connectionString="metadata=res://*/Models.MyData_DB.csdl|res://*/Models.MyData_DB.ssdl|res://*/Models.MyData_DB.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myappData.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Thanks for any help in getting the hang of this.

Answer 1

These links may help you in this regard http://www.asp.net/hosting/tutorials/strategies-for-database-development-and-deployment-cs

http://stackoverflow.com/questions/1085783/sql-server-2005-syncing-development-production-databases

http://www.asp.net/hosting/tutorials/configuring-the-production-web-application-to-use-the-production-database-cs

This is paid solution to sync databses http://www.red-gate.com/products/SQL_Comparison_SDK/case_studies/synchronizing_databases.htm