In Visual Studio 2010 Beta1 release, SQL server database schema and data can be packaged for deployment along with the website.  It utilizes Msdeploy SQL Database provider functionality in IIS team’s msdeploy release. 

In Visual studio 2010 Beta1, user can set the database package options in web application project’s Deploy-SQL property page as following.

User can add a package connection by clicking “Add” button and make sure it’s checked.  To select the source database for package, one can check “Pull data from an existing database” check box, and select or enter the database connection string.  In Beta1, only three packaging choices are provided for the database, “Schema only”, “Complete database” and “Exclude objects with no schema information from the generated script” (SMO option SchemaQualify). 

User can also include their own SQL script file in the Database source Scripts list view as well.

Msdeploy SQL Database provider command line provides functionality to use many SMO options to package a database. In order to integrate this functionality to VS2010 Beta1, one can edit the project file directly.  The following sample shows the project settings for the default schema only database.  Note, the corresponding SMO options are set in PreSource tag of the corresponding connection list item.  Advanced user can specify additional SMO option in the project file to script their databases.  The additional SMO options will be preserved automatically even during property page operations.

<PublishDatabaseSettings>
  <Objects>
    <ObjectGroup Name="NewConnection1" Order="1">
      <Destination Path="Data Source=myServerName%3bInitial Catalog=myDeployDBName%3bIntegrated Security=True" />
      <Object Type="dbFullSql">
        <PreSource Path="Data Source=myServerName%3bInitial Catalog=myDBName%3bIntegated Security=True"
 ScriptSchema="True" ScriptData="False" SchemaQualify="True" />
        <Source Path="objDebugAutoScriptsNewConnection1_SchemaOnly.sql" />
      </Object>
    </ObjectGroup>
  </Objects>
</PublishDatabaseSettings>

In VS2010 Beta1, there are some known problems regarding the database packaging, including the following:

1. transacted=false SMO option does not function as expected. (For certain database objects, such as full text catalog, full text index, user roles, transacted=false SMO option is required in order to be able to deploy successfully.)
2. triggers using certain objects  maybe scripted before the objects is defined.  This is resolved with the April 2009’s SQL server 2008 feature pack’s management objects download.

3. SMO options supported is limited by Msdeploy SQL Database provider‘s capacity.  Currently, it only support SMO option’s Boolean values.

In addition, we are still modifying the feature based on user’s feedback.  Please contact us with your advices.  Thanks.

Xinyang Qiu
SDETII

Visual Studio Web Tools