For many web applications developed using Visual Studio, developers uses SQL express with mdf database file under app_data folder.  If deploying data host requires SQL server, developers have to find ways to deploy the schema and data from mdf file to the host SQL server.

Dev10 Beta1 provides an easy solution to publish the mdf file to the SQL Server host along with other application files.  Here’s a walkthrough.

1. Create a new web application, create a SQL Server Database under app_data folder.  Add some tables to the database, drag a table to the default.aspx designer.  Ctrl-F5 to test it making sure it works.

2. Check the web.config file, note it has a connection string such as following:

<connectionStrings>
    <add name="test_EmployeeInfo_SKConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|test_EmployeeInfo_SK.mdf;Integrated Security=True;User Instance=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

3. In web application’s property page, Package/Publish tab, check Exclude files from the app_data folder” so that when packaged, the mdf file itself will not be packaged.

4. In Deploy-SQL tab, add a new connection, select it and paste the mdf connection string to the “Connection string for the source database” edit box.  Also input the destination’s SQL server connection string, and decide to package schema only or “complete database” by checking the corresponding checkboxes.

5. Refere to Vishal’s post for web.config connection string transformation so that packaged web.config file will contain the corresponding deploy time connection string.

6. Save, right click solution explorer project item, choose Package->“Create package” context menu item.  Wait for the process end.

7. Now, you’ve created a package contains mdf’s file’s sql script, and without the mdf file under app_data directory.  For debug configuration, default package location is at <project root>objdebugpackageprojectName.zip.

8. You can use the generated <project root>objdebugpackageprojectName_deploy.cmd to test deploy the application and database.

Note:

1.  |DataDirectory| inside connection name can only be used inside VS environment.  Msdeploy dbFullSql provider does not recognize |DataDirectory| in its commandline behavior.

2. In Dev10 Beta1, scripting mdf file has some limitations which may require workaround.  For example, in the connection string, if the expanded AttachDBFilename length is longer than 100 characters, the generated scripts may be in wrong order, and if it is longer than 127 characters, it does not generate at all.  In such time, please add “Initial Catalog=UniqueShortName” to the connection string as a short database logical name.

3. If User Instance is not used in the connection string, if mdf file is opened inside VS, it might be locked to prevent packaging.  In such case, adding User Instance=”true” to the connection string, or close the mdf data connection from VS server explorer can do the trick.

4. The above procedures and screen shots are based on Dev10 Beta1, which may be changed in future versions.

Thanks.

Xinyang Qiu
SDETII

Visual Studio Web Tools