Visual Studio 2010 Database Deployment API – Part 2


Since posting the first code sample on how to leverage the Visual Studio Database Deployment API, I have had a number of folks contact me and ask how to do the same, but also comprehend the database references, options set in the project, refactor log, pre and post deployment scripts, etc.  The short answer is that you include the deployment manifest in your use of the API.  Including the manifest will use all the settings from your project and also load up referenced database models and artifacts.

Below I have updated the sample to use the manifest file to drive the options for the deployments.  It assumes you are using integrated security so it can also pull the connection string from the manifest file.  The sample basically drives the database project deployment engine from a console app. 

using System;
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.Build.Evaluation;

namespace DeployLocalModel
{
    class Program
    {
        static void Main(string[] args)
        {
            string manifestFilePath = null;
            ISchemaDeploymentController sdController = null;
            Project projectManifest = null;
            FileInfo manifestFile = null;
            SchemaDeployment sdEngine = null;
            SchemaDeploymentConstructor sdConstructor = null;
            bool hasDeploymentChanges = false;
            bool pastPredeploymentPayload = false;
 
        try
        {
            manifestFilePath = @".\LocalModel.deploymanifest";

            if (args.Length > 0)
            {
                manifestFilePath = args[0];
            }

            manifestFile = new FileInfo(manifestFilePath);
            if (!manifestFile.Exists)
                throw new Exception("Manfiest file does not exist!");
            
            //Load manifest into MSBUILD Project so we can pull values from it
            projectManifest = new Project(manifestFile.ToString());

            /*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
            sdConstructor = 
                extensionManager.DatabaseSchemaProvider.GetServiceConstructor<SchemaDeploymentConstructor>();

            //Create a new error manager so we can capture the error messages
            ErrorManager errorManager = new ErrorManager();

            /*Attach error manager to the deployment constructor. 
            This will allow us to get errors from the deployment contributor later on */
            sdConstructor.Errors = errorManager;

            //Set up the deployment engine constructor of what we plan to deploy
            sdConstructor.Setup(
                new FileInfo(projectManifest.GetPropertyValue("SourceModel")), 
                projectManifest.GetPropertyValue("TargetConnectionString"));

            //Create an instance of the deployment engine
            sdEngine = sdConstructor.ConstructService();

            //Load manifest so other artifacts and options are considerred
            sdEngine.Configure(projectManifest, manifestFile.Directory);

            //Do not deploy to dataabase, yet
            sdEngine.SetDeployToDatabase(false);
            //Execute the engine to create our deployment plan
            sdEngine.Execute();

             // Get access to the plan so we call walk through it and later execute it
            DeploymentPlan deploymentPlan = sdEngine.Plan;

            #region WalkTheDeploymentPlan
            //Start with the first step in the plan and start iterating through it
            DeploymentStep deploymentStep = deploymentPlan.Head;
            while (deploymentStep != null)
            {
#if DEBUG
                //Send deployment step contents to output window
                Debug.Print("Deploy Step {0} contains:\n\r {1}",
                                deploymentStep.GetType().FullName,
                                deploymentStep.Action());
#endif
                /*Check and see if we are past the predeployment script so were can 
                  determine if there are any incremental deployment changes*/
                if (deploymentStep is EndPreDeploymentScriptStep)
                    pastPredeploymentPayload = true;

                if (deploymentStep.Action() != "")
                {
                    /*Test to see if we are deploying any changes, 
                      if not we will short circuit later on.*/
                    if (deploymentStep is DeploymentScriptDomStep && pastPredeploymentPayload)
                    {
                        hasDeploymentChanges = true;
                        break;
                    }

                }
                deploymentStep = deploymentStep.Next;
            }

            #endregion WalkTheDeploymentPlan

            if (hasDeploymentChanges)
            {
                //Create a controller se we can work with the deployment plan
                sdController = sdEngine.CreateController();


                // Setup our event handler so we can listen to events from deployment engine
                sdController.DeploymentMessage
                    += delegate(object sender, DeploymentContributorEventArgs deployArg)
                    {
                        Console.WriteLine("{0}", deployArg.Message.Message);
                    };

                //Deploy the model to the target database using the plan
                sdController.UpdateDatabase(deploymentPlan);
            }
            else
            {
                Console.WriteLine("No deployment changes to make!");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            if (sdController != null)
            {
                sdController.Dispose();
            }
            Console.WriteLine("Execution complete. Strike any key to exit.");
            Console.ReadKey();
        }
    
        }
    }
}

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

Comments (8)

  1. Anonymous says:

    Hello,

    I am really interested in using this API as part of a Visual Studio setup project. I ran the block without any issues in a console app but I have been running into an exception when running it inside of a custom action. I have been unable to solve this problem and was wondering if you could point me in the right direction. Below is the exception and the stack trace:

    The target database schema provider could not be determined. Deployment cannot continue.

    Microsoft.Data.Schema.Build.DeploymentFailedException

    Stack Trace:

    at Microsoft.Data.Schema.Build.SchemaDeployment.FinishInitialize(String targetConnectionString, String targetDatabaseName)

    at Microsoft.Data.Schema.Build.SchemaDeployment.Initialize (FileInfo sourceDbSchemaFile, ErrorManager errors, String targetConnectionString, String targetDatabaseName)

    at Microsoft.Data.Schema.Build.SchemaDeploymentConstructor.ConstructServiceImplementation()

    at Microsoft.Data.Schema.SchemaDeploymentConstructor`1.ConstructService()

    Thanks for your help!

    PS I am running SQL Server Express 2008

  2. DGDev says:

    Hi there Barclay,

    Myself and a co-worker have been working tirelessly to establish a Tfs database versioning and deployment solution.  Thus far we have managed to get our Database Project and Server Project working nicely together. Unfortunately, the MS deployment options available to us are not sufficient for our packaging needs.  (Mainly due to the fact that we must support incremental builds and deployment scripts.)

    Therefore we have built a Visual Studio AddIn and console application to support our needs, specifically giving us the ability to sync a database project to our localhost SqlServer and a Packaging feature for actual deployment releases.

    We have used this blog post as guidance in development, however have run into problems.  To test our application, we have simply deleted an existing table from the database project.  This creates SQL DROP statements in the generated deployed script as desired.  In testing your provided code, we noticed the table is never dropped from the database, and through further investigation we noticed our console app simply exits with a crash after issuing:

    sdController.UpdateDatabase(deploymentPlan);

    I can confirm the following:

    1. We do have the "Generate DROP statements for objects that are in the target database…" option checked for our deployment settings in the database project.
    2. The deployment script does contain all the necessary DROP statements for constraints and the table.

    3. The sdController has the correct TargetDatabase and ConnectionString with integrated auth credentials. All permissions are correct as I am an admin on my localhost.

    Unfortunately, it has been very hard to uncover more details since we are not sure what is causing the exception within the UpdateDatabase method.

    Is there anything you off the top of your head you could think why this would fail, and not gracefully?

    Excerpt:

    if (hasDeploymentChanges)

    {

     ISchemaDeploymentController sdController = sdEngine.CreateController();

     sdController.DeploymentMessage += delegate(object sender, DeploymentContributorEventArgs e) { Console.WriteLine(e.Message.Message); };

     sdController.UpdateDatabase(deployPlan);

    }

    Console.ReadLine(); <<< !!!! Never gets hit because UpdateDatabase crashes the application silently.

    Console.WriteLine("The deploy function is exiting…");

  3. Anonymous says:

    It would be very nice to have the "Generate DROP statements for objects that are in the target database…" option  checked but ignore any user logins or roles.  Is this possible at all?

  4. Anonymous says:

    Barclay, thanks for writing these two good articles!  I work on a team that has been using the VSDBCMD.exe to do the database deployments on our client installation.  This works well when it works.  The issue that we run into is that there are any number of reasons why the database won't deploy.  All of these reasons seem to come down to a memory issue.  If the user restarts the computer and retries the deployment a lot of times this fixes the issue.  However, we get enough of these errors that we have determined that we just cannot use the VSDBCMD anymore.  I believe our problem is in the post data scripts where we load some of our tables with data.  So, that has me looking into rolling my own deployment leveraging the api directly, which is why I have landed here at your blog post.  I am having a difficult time finding the documentation on how to bring all the parts of the API together.  One big piece that I am missing from your sample is where can I capture the logging information?  I want to see what is happening as it runs as if I was in Visual Studio and deployed from there.  What hooks do I need to put in place to get this information?  Any guidance on additional documentation would be helpful as well.  Thanks!

    -Patrick

  5. Barclay Hill says:

    @Patrick,  you can receive messages through a delegate/event receiver similar to the sample I provided here: blogs.msdn.com/…/leveraging-the-visual-studio-2010-database-deployment-api.aspx

    I’m not sure rolling your own client over the API will solve the memory issues you are encountering as VSDBCMD.exe is just a thin veneer over the API, but may afford more flexibility in dealing with data as you describe.

    If this does not help, you can contact me here on specifics: blogs.msdn.com/…/contact.aspx

  6. @Barclay,

    In order to deploy on a client machine running SQL Express 2005, what prerequisites must be installed in order for this approach to work?

    I am receiving the following error message:

    "Index (zero based) must be greater than or equal to zero and less than the size of the argument list".

    That error occurs at the following line:

    Dim extensionManager As New ExtensionManager(GetType(Sql100DatabaseSchemaProvider).FullName)

    The following DLLs are are located in the application directory which I am running:

    Microsoft.Build

    Microsoft.Data.Schema

    Microsoft.Data.Schema.ScriptDom

    Microsoft.Data.Schema.ScriptDom.Sql

    Microsoft.Data.Schema.Sql

    Microsoft.Data.Schema.Utilities

    Thanks in advance!

  7. @Barclay,

    Just following up on a previous post of mine.  This is great stuff!!!

    But I have struggling to get this to run without error on a client machine.  I have installed SQL Server 2008 Express along with Management Objects and the native client.

    I still get an error – "Index (zero based) must be greater than or equal to zero and less than the size of the argument list".

    Any ideas on where I should be looking to resolve?

    Thanks in advance!!

    The Doctor's In

  8. Anonymous says:

    Can we implement Custom code analysis rules for SSDT as well?

Skip to main content