Leveraging the Visual Studio 2010 Database Deployment API

I often get questions from developers wanting to modify the deployment engine’s behavior in some way. These questions are typically motivated by the need to accommodate a specific requirement for a given environment or to support automation in some fashion. In GDR there has not been much you could do to change the behavior of the deployment engine aside from existing deployment options and pre/post deployment scripts. In 2010 you now have access to some really powerful APIs to leverage the database project’s build engine and database deployment engine.


There are 3 new areas of the API now public that you can code against and influence the database deployment engine’s behavior or extend it in some fashion.


Database Deployment Contributors

· Plan Modifiers - Deployment Plan Modifiers allow the developer to modify the deployment plan before execution. Jamie has a good sample on MSDN which makes scripts produced by the database project re-runnable.

· Plan Executors - Deployment Plan Executors provide the developer read only access to the deployment to process the deployment plan in some way. Jamie also has a good sample on MSDN about how to create new artifacts to analyze a deployment plan.

Database Deployment Engine API

· The Deployment API gives you low-level access to the deployment engine. VSDBCMD is only a shell which leverages the deployment engine and provides simplified access to the deployment engines knobs and buttons.

Here is sample code for using the deployment engine to deploy a local model on disk. You can see how simple it is to load the model up and then walkthrough the deployment plan. You can analyze the deployment plan and add and remove steps if you like. You can register customer loggers or even integrate it even tighter with your own deployment frameworks.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Diagnostics;

using System.IO;

using Microsoft.Data.Schema;

using Microsoft.Data.Schema.Sql;

using Microsoft.Data.Schema.Extensibility;

using Microsoft.Data.Schema.Build;

using Microsoft.Data.Schema.SchemaModel;

using Microsoft.Data.Schema.Sql.SchemaModel;

namespace DeployLocalModel


    class Program


        static void Main(string[] args)


            const string conxString

                = @"Server=.\SQLEXPRESS;Integrated Security=true;Pooling=false";

            const string modelFilePath

                = @"..\..\..\LocalModel\sql\debug\LocalModel.dbschema";

            const string targetDatabaseName = @"CarDealership";

            ISchemaDeploymentController schemaDeploymentController = null;

            bool hasChanges = false;



            //Create a new error manager so we can capture the error messages

            ErrorManager errorManager = new ErrorManager();

            /*Create a extension manager so we can load the SQL100 DSP and

            utilize its implementation of the deployment engine*/

            ExtensionManager extensionManager =

                new ExtensionManager(typeof(Sql100DatabaseSchemaProvider).FullName);

            // Create the deployment controller

            SchemaDeploymentConstructor schemaDeploymentConstructor =



            schemaDeploymentConstructor.Setup(new FileInfo(modelFilePath), conxString);

            schemaDeploymentConstructor.Errors = errorManager;

            SchemaDeployment schemaDeployment

                = schemaDeploymentConstructor.ConstructService();

            //Set deployment controller options

            schemaDeployment.Options.TargetDatabaseName = targetDatabaseName;

            //Create a controller becase we will be working with a deployment plan

            schemaDeploymentController = schemaDeployment.CreateController();

            // Setup our event handler so we can listen to events from deployment engine


                += delegate(object sender, DeploymentContributorEventArgs deployArg)


                Console.WriteLine("{0}", deployArg.Message.Message);


            // Get access to the plan so we call walk through it

            DeploymentPlan deploymentPlan = schemaDeploymentController.CreatePlan();

            DeploymentStep deploymentStep = deploymentPlan.Head;

            while (deploymentStep != null)


                if (deploymentStep.Action() != "")


     //Send deployment step contents to output window

                    Debug.Print("Deploy Step {0} contains:\n\r {1}",



                    /*Test to see if we are deploying any changes,

                    if not we will short circuit later on.*/

                    if (deploymentStep is DeploymentScriptDomStep){

                        hasChanges = true;




                deploymentStep = deploymentStep.Next;


            if (hasChanges)


                //Deploy the model to the target database using the plan





                Console.WriteLine("No deployment changes to make!");



        catch (Exception e)






            if (schemaDeploymentController != null)




            Console.WriteLine("Execution complete. Strike any key to exit.");







If you want to start out with the sample already coded up you can download it from my SkyDrive.