VS2010 Beta1 Web Application Project Database package and SMO options

VS2010 Beta1 Web Application Project Database package and SMO options

  • Comments 6

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.

image

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="obj\Debug\AutoScripts\NewConnection1_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

  • Have you guys thought about or explored how the Data Dude (DB Pro) part of Visual Studio might be leveraged for this - I realize that you probably don't want to take a hard dependency since this is Professional SKU functionality and DB Pro is a Team Edition SKU - but having an integration story there (that wasn't entirely) manual would be terrific.

  • To learn more about DB Deployment in detail visit http://tinyurl.com/dbdeploy

    -Vishal

  • Hi Bryan,

       We do have loosly coupled integration with Data Dude, in future posts we will cover the details on how to set it up...

    -Vishal

  • Very interesting, but I hope the deployment will be more easy and support multiple database version(2000/2005/2008/..)

  • Yes Jack it certainly does support all SQl Server versions stArting SQL Server 2005

  • Nice post.It generate new discussion.So people will learn more and more.

Page 1 of 1 (6 items)