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;
try
{
//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 =
extensionManager.DatabaseSchemaProvider
.GetServiceConstructor<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
schemaDeploymentController.DeploymentMessage
+= 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}",
deploymentStep.GetType().FullName,
deploymentStep.Action());
/*Test to see if we are deploying any changes,
if not we will short circuit later on.*/
if (deploymentStep is DeploymentScriptDomStep){
hasChanges = true;
break;
}
}
deploymentStep = deploymentStep.Next;
}
if (hasChanges)
{
//Deploy the model to the target database using the plan
schemaDeploymentController.UpdateDatabase(deploymentPlan);
}
else
{
Console.WriteLine("No deployment changes to make!");
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
if (schemaDeploymentController != null)
{
schemaDeploymentController.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
- Anonymous
August 23, 2010
So where do the following namespaces come from? I've search both my dev pc and TFS server and cannot find them!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;I really with MS wout make it EASIER for us developers instead of adding layer upon layer of complexity!!!!!! - Anonymous
September 06, 2010
you might want to consultmsdn.microsoft.com/.../dd193281.aspx - Anonymous
September 11, 2010
I'm trying to use this approach to do a schema compare and generate an update script for my target DB. All seems good and I've gotten so far as to generate the script, but the script includes "ADD FILE" commands, which it really shouldn't...and that's causing the plan to throw exceptions on UpdateDatabase because the files are in use... Is there a way to suppress the ADD FILE statements from being generated? - Anonymous
October 17, 2010
Great script.I have a question "If I take useLegacyV2RuntimeActivationPolicy from the app.config filethe script won't create database.Could you please let me know how to solve this issue since I want to create a Setup class that uses this maifest to create database.Regards,Zainu - Anonymous
March 31, 2012
Now that the Database Projects are becoming obsolete, how do we utilize the API's for the SSDT projects? - Anonymous
April 02, 2012
SSDT also provides a public API to perform DACPAC operations. You can read more about it here: msdn.microsoft.com/.../microsoft.sqlserver.dac.aspx - Anonymous
September 24, 2012
@Barclay, great article!Having a problem during the UpdateDatabase call when I am trying to drop a column or table. Here is the message: "The schema update is terminating because data loss might occur.".I know in a database project, you can set an option to ignore this error. I don't see an option via code on the SchemaDeployment Class.Can you direct me please to the right place to resolve this?Thanks in advance! - Anonymous
September 24, 2012
Thanks Ron. Im glad you found it useful. Take a look at the subsequent post which describes how to load the deployment options via the manifest. blogs.msdn.com/.../visual-studio-2010-database-deployment-api-part-2.aspx - Anonymous
May 14, 2013
The comment has been removed - Anonymous
May 15, 2013
@ Vince is the target database the same? The delay you are noticing is probably in the connection to the target database. - Anonymous
January 28, 2015
Hello BarclayThis solution assumes we have a dbschema handyhow do we convivially generate the dbschema with VS 2013? can we bypass the use of dbschema completely, for ex by loading the schema from DB directly?