Microsoft Web Deployment Team Blog : PDC

The MSDeploy SQL Database Provider (dbFullSql)

yaminij — Mon, 10 Nov 2008 20:34:00 GMT

Overview

The MSDeploy SQL Database provider, also called dbFullSql is desgined for first-time installation or deployment of SQL databases to a specified destination database. This blog will serve more as a first step towards using the dbFullSql provider that recently shipped with the Beta 2 version of MSDeploy. The provider can be used in the following ways

1) Syncing a single source database to an empty or nonexistent destination database

2) Syncing a source database to an editable .sql file

3) Sync from a .sql file to an existing destination database.

Implementation

The dbFullSql provider uses SMO internally to generate the source database scripts and apply them to the target database. The following SMO Scripting Options are set to true by default inside the dbFullSql provider

a) ScriptSchema

b) ScriptData

c) DriAll

d) Triggers

e) Indexes

f) NoFileGroup

We support passing all SMO options, that take boolean values, as parameters to the dbFullSql provider. For more examples on how to use the SMO options with the dbFullSql provider, please check the command-line usage below. Since the script is only generated from the database specified as the source, it is not required to specify the SMO option as a parameter on the destination as well.

The dbFullSql provider will also create the target database if it does not exist. If you would like to drop the destination database and create a new one on sync, you can use the dropDestinationDatabase=true parameter on the destination

The dbFullSql provider also has transactional support during the execution of the SQL scripts on the destination database. This means, if there is an error in the middle of the sync, all the changes made to the destination database until then will be rolled back.

Command-line Usage

1) To sync a single source database (eg. SourceDatabase), to an empty or nonexistent destination database, you should run the following command

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<source db connection string>" -dest:dbFullSql="<dest db connection string>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

2) To sync a source database (eg. SourceDatabase), to an editable .sql file

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<source db connection string>" -dest:dbFullSql="<absolutePath to a .sql file>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:dbFullSql="d:\SourceDb.sql"

3) To sync a .sql file to an existing destination database

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<absolutePath to a .sql file>" -dest:dbFullSql="<dest db connection string>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="d:\SourceDb.sql"

-dest:dbFullSql="Data Source=SqlMachine\SQLEXPRESS;Initial Catalog=DestDatabase;User Id=user1;password=blah"

4) To specify SMO options as parameters to the dbFullSql provider

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<absolutePath to a .sql file>",smoOptionName=value -dest:dbFullSql="<dest db connection string>"

Example:

In order to skip scripting data on the source database and to sync only schema, you can run the following command

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true", ScriptData=false

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

Example:

In order to include schema-qualified table references for foreign key constraints into the generated script, you can pass SchemaQualifyForeignKeysReferences=true to the dbFullSql provider

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true", SchemaQualifyForeignKeysReferences=false

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

5) You could also sync a database to a package or an archive

Example:

a) msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:archivedir="c:\archive"

b) msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:package="c:\package.zip"

6) To sync multiple databases and database scripts to multiple databases, you can use a manifest file with the MSDeploy manifest provider.

Limitations

The dbFullSql provider cannot be used for incremental publishing to a target database. You can incrementally publish to a target database, only if there is no collision between existing objects on the source database and the target database.

The dbFullSql provider operates at the database level only and not at the SQL Server level. Hence any objects at the server level, like logins, on which objects at the database level depend on (like users) will not be scripted.

We currently do not support passing any non-boolean SMO option to the dbFullSql provider.

References

For more information about SMO scripting options, you can refer this link

For information on how to download and install SMO and MSDeploy in general, you can refer the MSDeploy Help File

The Web Deployment Tool Beta 2 is now available!

faith_a — Wed, 29 Oct 2008 21:31:00 GMT

The IIS team is proud to announce the latest release of the Web Deployment Tool Beta 2, which includes significant new features for both IT professionals and developers. In addition to our existing functionality of server synchronization and migration, but we’ve added web application packaging and deployment with integration with IIS Manager and the upcoming Visual Studio 2010!

Because the Beta 2 release has brand-new functionality around application packaging not previously available, this release is not a production-ready or Go Live release. We will be changing and adding more functionality in the next milestone after Beta 2.

Easily deploy, store and redeploy Web applications.
The Web Deployment Tool enables you to package configuration and content of your installed Web applications, including SQL databases, and use the packages for storage or redeployment. These packages, which may include certificates, can be deployed using the IIS Manager interface without requiring administrative privileges. This tool also integrates in Visual Studio 2010, which helps developers streamline the development and deployment of Web applications.

Efficiently synchronize your server farm.
The Web Deployment Tool allows you to efficiently synchronize sites, applications or servers across your IIS 7.0 server farm by transferring only those changes which need synchronization. The tool simplifies the process by automatically determining the configuration, content, databases and certificates to be synchronized for a specific site. In addition to the default behavior, you still have the option to specify additional “providers” for the synchronization to modify COM, GAC and registry settings.

Migrate Web applications between IIS 6.0 and IIS 7.0 with ease.
Simplify the planning of your IIS 6.0 to IIS 7.0 migrations by determining incompatibilities and previewing the proposed changes before starting the process. Learning about any potential issues in advance gives you the chance to take corrective measures considerably improving your chances of having a smooth execution of your migration.

We have some new walkthroughs to help you understand and try out the application deployment process in the IIS Manager. Please try out the tool and give us feedback in the forums. We look forward to hearing about your experiences and what else you’d like to see us add.

Downloads

x86 version: http://go.microsoft.com/fwlink/?LinkId=109365
x64 version: http://go.microsoft.com/fwlink/?LinkId=109366

Learn the Basics (Quick Guides)
1. Web Deployment Tool Overview (overview of the features)
2. Installing the Web Deployment Tool (installation and remote service options)

Learn How to Package and Deploy (Quick Guides)
1. Create a Package (creating a package including content, databases and more)
2. Install a Package (deploying the package to a local or remote machine)
3. Configure the Web Deployment Handler (enable deployment to an IIS 7.0 server)

More quick guides can be found at the Web Deployment Tool main page.

Get Help or Interact with the Community
Web Deployment Tool forum: http://forums.iis.net/1144.aspx
Web Deployment Team blog: http://blogs.iis.net/msdeploy/

Happy deployments!

The Web Deployment Tool is @ PDC!

faith_a — Mon, 27 Oct 2008 20:49:00 GMT

Just wanted to let everyone know that a bunch of us from the IIS and MS Deploy team are here at PDC 2008 in sunny Los Angeles! If you're here for PDC, please stop by the Windows Server 2008 Web Platform booth or check out our great talks on web deployment. We have some great news being announced and we'd love to meet folks that are currently using or interested in using the Web Deployment Tool.

Happy deployments!

Faith Allington, and the entire Web Deployment Tool team