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 to your SQL Server database.

Command line Usage

NOTE: For command line operations, you must use SqlBuildManager.Console.exe

Tip: If you don’t like typing the full name of the exe you can easily create an alias with the files in the Utility folder:

Now you can use shorthand sbm to run the command-line!

Getting started

Defining the Action

The basics of running the command line is the /Action argument. This is required for all executions (except for /? or/help - which I hope are self explanatory)

Action value options (/Action=<value>)

Build execution actions to update databases
Utility actions

General Authentication settings

Used to provide authentication to databases during a build or for script extraction/ DACPAC creation\ Applies to: /Action={Build|Threaded|Batch|Remote|GetDifference|Synchronize\ Note: Is using username/password authentication, you can also leverage the SettingsFile option

General Runtime settings

Arguments needed when performing a database update build\ Applies to: /Action={Build|Threaded|Batch|Remote}

Azure Batch Execution (/Action=Batch)

In addition to the authentication and runtime arguments above, these are arguments specifically needed for Azure Batch executions\ See detailed Batch documentation

Azure Batch Pre-Stage Batch nodes (/Action=BatchPreStage)

See detailed Batch documentation

Azure Batch Clean Up (delete) nodes (/Action=BatchCleanUp)

See detailed Batch documentation

Remote Execution settings (/Action=Remote)

Dacpac Execution

When using a source DACPAC file /PlatinumDacpac or creating one one at run time (/PlatinumDbSource, /PlatinumServerSource along with /Server and /Database).\ Applies to: /Action={Threaded|Batch|Remote}

Save Settings (/Action=SaveSettings)

This utility action will save a reusable Json file to make running the command line easier (especially for Batch processing). To save the settings, create the command line as you normally would, and set the /Action=SaveSettings and add a /SettingsFile="<file path>” argument.

The next time you run a build action, use the /SettingsFile="<file path> in place of the arguments below.

Note:

  1. the values for /UserName, /Password, /BatchAccountKey and /StorageAccountKey will be encrypted.
  2. If there are duplicate values in the /SettingsFile and the command line, the command line argument will take precedence.
  3. You can hand-craft the Json yourself in the format below but the password and keys will not be encrypted (which may be OK depending on where you save the files)

Script Extraction from Dacpac (/Action=ScriptExtract)

Database Synchronization (/Action={Synchronize|GetDifference})

SBX to SBM packaging (/Action=Package)

Policy checking (/Action=PolicyCheck)

Calculate hash/fingerprint (/Action=GetHash)

Creating backout packages (/Action=CreateBackout)

# Logging

For general logging, the SqlBuildManager.Console.exe has its own local messages. This log file is named SqlBuildManager.Console.log and can be found in the same folder as the executable. This file will be the first place to check for general execution errors or problems.

To accommodate the logging of the actual build, all of the output is saved to files and folders under the path specified in the /RootLoggingPath flag. For a simple threaded execution, this is a single root folder. For a remote server execution, this folder is created for each execution server.

Working folder

This folder is where the contents of the .SBM file are extracted. This file is extracted only once and loaded into memory for the duration of the run to efficiently use memory.

Commits.log

Contains a list of all databases that the build was committed on. This is a quick reference for each location that had a successful execution.

Errors.log

Contains a list of all databases that the build failed on and was rolled back. This is a quick reference for all locations that had failures.

Server/Database folders

For each server/database combination that was executed, a folder structure is created for each server and a subfolder in those for each database. Inside each database level folder will be three files:

Settings File Format

The format for the saved settings Json file is below. You can include or exclude any values that would like. Also as a reminder, for any duplicate keys found in the settings file and command line arguments, the command line argument’s value will be used.

{
  "AuthenticationArgs": {
    "UserName": "<database use name>",
    "Password": <database password>"
  },
  "BatchArgs": {
    "BatchNodeCount": <int value>,
    "BatchAccountName": "<batch account name>",
    "BatchAccountKey": "<key for batch account ",
    "BatchAccountUrl": "<https URL for batch account>",
    "StorageAccountName": "<storage account name>",
    "StorageAccountKey": "<storage account key>",
    "BatchVmSize": "<VM size designator>",
    "DeleteBatchPool": <true|false>,
    "DeleteBatchJob": <true|false>,
    "PollBatchPoolStatus": <true|false>
  },
  "RootLoggingPath": "<valid folder path>",
  "LogAsText": <true|false>, 
  "DefaultScriptTimeout" : <int>,
  "TimeoutRetryCount: <int>
}