View on GitHub

SqlBuildManager

SQL Build Manager is an all in one tool for building and maintaining a package of SQL scripts to manage builds and updates across your fleet of SQL Server databases.

SQL Build Manager

SQL Build Manager is a multi-faceted tool to allow you to manage the life-cycle of your databases. It provides a comprehensive set of command line options for the management of one to many thousands of databases.

.NET Core Build

Contents

Key Features

The Basics

Build Package meta-data

At the core of the process is the “SQL Build Manager Package” file (.sbm extension). Under the hood, this file is a Zip file that contains the scripts that constitute your “release” along with a configuration file (SqlSyncBuildProject.xml) that contains meta data on the scripts and execution parameters:

If you are using a DACPAC deployment, this all gets generated for you based on your command line parameters and defaults

Example SqlSyncBuildProject.xml file. You can build this by hand to create your own .sbm file or leverage the options below (recommended).

<?xml version="1.0" standalone="yes"?>
<SqlSyncBuildData xmlns="http://schemas.mckechney.com/SqlSyncBuildProject.xsd">
  <SqlSyncBuildProject ProjectName="" ScriptTagRequired="false">
    <Scripts>
      <Script FileName="select.sql" BuildOrder="1" Description="Testing select script" RollBackOnError="true" CausesBuildFailure="true" DateAdded="2019-04-11T19:45:05.081043-04:00" ScriptId="14f775d2-d026-426b-bece-7faa323e0e14" Database="Client" StripTransactionText="true" AllowMultipleRuns="true" AddedBy="mimcke" ScriptTimeOut="500" DateModified="0001-01-01T00:00:00-05:00" ModifiedBy="" Tag="default" />
    </Scripts>
    <Builds />
  </SqlSyncBuildProject>
</SqlSyncBuildData>

Creating a Build Package

Forms UI

While the focus of the app has changed to command line automation, the forms GUI is fully functional. If you are looking for a visual tool, check out SqlBuildManager.exe. There is documentation on the GUI that you can find here that will walk through the creation of build packages (PDF version).

Command line

There are several ways to create a build package from the command line. Which you choose depends on your starting point:

Command line reference

  1. From a DACPAC file using the sbm scriptextract command. This method leverages a DACPAC that was created against your “Platinum Database” (why platinum? because it’s even more precious than gold!). The Platinum database should have the schema that you want all of your other databases to look like. (don’t have a DACPAC created, don’t worry, you can create one with the sbm dacpac command)

  2. From various sources using sbm create. There are four sub-commands to help create an SBM package:

    • fromscripts - Creates an SBM package or SBX project file from a list of scripts (type is determined by file extension: .sbm or .sbx)
    • fromdiff - Creates an SBM package from a calculated diff between two databases (you provide the server and database names, and it connects them them and generates the diff scripts)
    • fromdacpacs - Creates an SBM package from differences between two DACPAC files (use DACPACs you have created elsewhere or use sbm dacpac to create them)
    • fromdacpacdiff- This method leverages a DACPAC that was created against your “Platinum Database” (why platinum? because it’s even more precious than gold!). The Platinum database should have the schema that you want all of your other databases to look like.

    Learn more about DACPACs and data-tier applications method.

  3. From an SBX file. What is this? An SBX file is an XML file in the format of the SqlSyncBuildProject.xml file (see above) that has an .sbx extension. When you use the sbm package command, it will read the .sbx file and create the .sbm file with the referenced scripts.
  4. An SBM package file can be created indirectly as well, using the sbm threaded run and sbm batch run commands along with the --platinumdbsource="<database name>" and --platinumserversource="<server name>" the app will generate a DACPAC from the source database which will then be used to generate an SBM at run time to build directly on your target(s).
  5. You can also add new scripts to an existing SBM package or SBX project file using sbm add

NOTE: The sbm scriptextract method is being deprecated in favor of sbm create fromdacpacdiff and will be removed in a future release

Targeting multiple databases

You define your database update targets leveraging an --override file or using Azure Service Bus Topics (only with Azure Batch). The details of database targeting can be found here

Running Builds (command line)

There are 4 ways to run your database update builds each with their target use case

Local

Leveraging the sbm build command, this runs the build on the current local machine. If you are targeting more than one database, the execution will be serial, only updating one database at a time and any transaction rollback will occur to all databases in the build.

Threaded

Using the sbm threaded run command will allow for updating multiple databases in parallel, but still executed from the local machine. Any transaction rollbacks will occur per-database - meaning if 5 of 6 databases succeed, the build will be committed on the 5 and rolled back only on the 6th

Batch

Using the sbm batch run command leverages Azure Batch to permit massively parallel updates across thousands of databases. To leverage Azure Batch, you will first need to set up your Batch account. The instructions for this can be found here. An excellent tool for viewing and monitoring your Azure batch accounts and jobs can be found here https://azure.github.io/BatchExplorer/

Kubernetes

Using the sbm container commands leverages Kubernetes to permit massively parallel updates across thousands of databases. To leverage Kubernetes, you will first need to set up a Kubernetes Cluster. The instructions for this can be found here.

Querying across databases (command line)

In addition to using SQL Build Manager to perform database updates, you can also run SELECT queries across all of your databases to collect data. In the case of both threaded and batch a consolidated results file is saved to the location of your choice

Threaded

Using the sbm threaded query command will allow for querying multiple databases in parallel, but still executed from the local machine.

Batch

Using the sbm batch query command leverages Azure Batch to permit massively parallel queries across thousands of databases. (For information on how to get started with Azure Batch, go here)