Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe
Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution
Automate Database Build Part Three: Create a Database Publish Profile
Continuing with my automated database deployment series, this post touches on the build.publish.xml file that is used to script and publish a SQL Server 2012 project in Visual Studio. This step is necessary as we will need this file in our automated build.
Within a build.publish.xml file there is a “profile”. These profiles are essentially an xml file that define the settings when deploying a database. The examples below show how to create one, what the xml looks like, and some of the settings you can configure through the advanced settings.
Open up you database solution, right click the database project you want to publish and click “Publish”
Let’s go ahead and save the file before we edit it. The pictures below show that when we click on create profile and it will automatically save it to the solution. You don’t have to create the profile in order to use it, so if there are any ad-hoc publishing you want to do, or save more than one profile.
Now we edit the target database connection. Fill in whatever is appropriate for your database.
Click ok to take you back to the Publish Database window. At this point you can either script or publish. Click publish and any changes in the solution will be applied to the target database.
Even though we did not click “script” a script is still generated. You can click on the “view script” and you will see the sql script hat was deployed to your solution.
But what of the publish file? To see what the xml looks like right click the file in Solution Explorer and select “open with” and choose the “XML Text Editor”
<?xml version="1.0" encoding="utf-8"?> <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <IncludeCompositeObjects>True</IncludeCompositeObjects> <TargetDatabaseName>AdventureWorks2012DW</TargetDatabaseName> <DeployScriptFileName>AdventureWorks2012DW.sql</DeployScriptFileName> <ProfileVersionNumber>1</ProfileVersionNumber> <TargetConnectionString>Data Source=ALTEREDGIRATINA;Integrated Security=True;Pooling=False</TargetConnectionString> </PropertyGroup> </Project>
So pretty straightforward, it’s an MSBuild file! it contains the target database and target instance, and we can see that it simply deploys the script. But all this is pretty straightforward, and database deployment is anything but. If we open up our publish file to get the default view and click on “Advanced” we see that we can get all sorts of options:
One setting I like to enable is dropping permissions that are not in the source. Once I have added that and saved the profile, when I open it up in XML Editor again the changes I made will be stored.
<?xml version="1.0" encoding="utf-8"?> <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <IncludeCompositeObjects>True</IncludeCompositeObjects> <TargetDatabaseName>AdventureWorks2012DW</TargetDatabaseName> <DeployScriptFileName>AdventureWorks2012DW.sql</DeployScriptFileName> <ProfileVersionNumber>1</ProfileVersionNumber> <TargetConnectionString>Data Source=ALTEREDGIRATINA;Integrated Security=True;Pooling=False</TargetConnectionString> <ScriptDatabaseOptions>True</ScriptDatabaseOptions> <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss> <DropPermissionsNotInSource>True</DropPermissionsNotInSource> </PropertyGroup> </Project>
Include Composite Objects in build.publish.xml
You can combine multiple database projects or dacpacs to create a single composite database schema. Using a composite database project enables reuse of database object definitions across different databases.
Composite projects are created by adding Database References to projects or dacpacs. There are a few conditions by using references:
- they must be strictly hierarchical
- they must not contain circular dependencies.
w/r/t to using dacpacs as references, you can either build from a project or extract from a database. The hierarchy of references can be multi-layered, although you need to add references in each project to all its lower-level projects or dacpacs, whether directly or indirectly created.
To deploy a composite project you must set the Include composite objects option on the project you’re deploying from. This option is on the Advanced options in Publish and on the Debug properties tab. It is also available as a general option in Schema Compare. Note that in Schema Compare the option only affects publishing to a target database. This is important: if you do not set this option source project will be deployed without the referenced projects or dacpacs. If you deploy to an existing database and forget to set this option but have set options to delete objects in the target that are not in the source, you will drop objects from the database.
If you save the build.publish.xml file, it sets the key value pair to “true”. By default it is implicitly set to false (that is, it won’t be in the xml set to “false”.
Publish files for database solution are essentially MSBuild files that use key value pairs declared at the project group level to define the settings for database deployment. It uses the script generated to deploy the solution. We create more than one publish file per database project for either different instances or different advanced settings, or we can use them without saving the profile through Visual Studio. If we wish to deploy via an automated build it makes sense to create a build.publish.xml file with the settings we want.