How to deploy static data from SQL source control to database

Prelude

As described in the article “Understanding SQL Server database static data and how it fits into Database lifecycle management”, the reasons for version controlling static data were reviewed.

Static data can be linked and committed to source control using various tools and approaches. The goal for this article is to cover deploying database static data from SQL source control to database using ApexSQL Build.

Challenge

In this article, we will show how to deploy database static data from SQL source control and use it to update a database.

Solution

For the purpose of deploying database static data from SQL source control and updating a database, we will use ApexSQL Build. This is a tool for building a new SQL database using various inputs as a source, such as SQL script file or a folder with multiple SQL scripts. In addition to this, ApexSQL Build can connect to a source control repository and pull SQL database schema as well as any static data that is previously linked. Besides building a new database, ApexSQL Build can update an existing SQL database, consolidate scripts, and execute scripts on multiple databases.

We will use a Mercurial repository hosted on Bitbucket assuming that a SQL database schema is already pushed to source control, as well as data from tables that are classified as static data. An overview of the repository is as follows:

By checking the Tables folder, we can confirm that the Static data folder exists:

Checking the content of the Static data folder, we can confirm that the static data is properly committed to the repository, and that it can be pulled and applied against a database.

In order to deploy database static data from SQL source control using ApexSQL Build, we will go through the following steps:

Choose the Update an existing database option, to start the wizard:

In the Input source step, select the Source control option, and click the Edit button to configure:

In the Source control wizard, choose Mercurial from the list, and click the Next button:

In the System login step, specify credentials for the mercurial repository, and click the Finish button:

Once source control setup is finished, click the Next button in the Input source step, to advance to the next step:

In the next step, specify SQL Server instance and a database to be updated. We have selected PubsQA database as the one to be updated with the static data, as it is a database previously built from source control and it has identical structure as Pubs database:

In the Build objects step, we will uncheck all objects since we don’t want any DDL updates, but only to pull the static data from the repository and apply it against a database. To include static data, we will click the Options button, in the lower right section of the Build objects step:

Check the Insert static data option:

After this is set, we can advance through the wizard to the latest step, in order to choose if the script, that will be executed against a database, will be saved as a SQL file, or if a SQL database backup should be created before executing the script:

For the purpose of repeating and later automating the process, we will use the Save/Save as button in the lower left section of the Output options step, to save all the above described steps in the project file. For the purposes of our automation script, we’ll call this project file, PullStaticData.axdd

After the project file is saved, we can click the Finish button in order to initiate the process of pulling the static data from source control

After the project is done, we can check any table previously selected as the one that contains the static data. For instance, performing the SELECT statement against the jobs table, we can confirm that the static data has been applied against the PubsQA database:

Using the above described steps, deploying database static data from SQL source control can be performed and static data applied against a database.

Automation

To automate the process, we’ll create a PowerShell script that contain ApexSQL Build CLI command along with additional switches in order to automate the process. PowerShell script includes the output summary with a datestamp:

$appLocation = “ApexSQLBuild” Variable that stores the path to ApexSQL Build CLI executable file
$timeStamp = (Get-Date -Format “MMddyyyy_HHmmss”) Variable that stores the value for the time stamp. This value is used as part of the name for the output file
$appCLIcommand = “/pr:””PullStaticData.axdd”” /v /f /out:””Output_$timeStamp.txt””” Variable that keeps the CLI command to call the previously created project file. In addition to this, date stamped file which includes the execution summary will be created
(Invoke-Expression (“& `”” + $appLocation +”`” ” +$appCLICommand)) A command used to call the CLI executable along with the additional switches
“ApexSQL Build return code: $LASTEXITCODE” >> “Output_$timeStamp.txt” This will include the return code in the above mentioned execution summary

When the script is ready, save it as PullStaticData.ps1

In the PullStaticData.ps1 script, the previously saved project file contains encrypted credentials for the Mercurial repository and for SQL Server login credentials.

Once the PowerShell script is executed, it will return code at the end of the output file:

Build operation completed

ApexSQL Build return code: 0

For the purpose of handling credentials in various ways, here are 4 ways of handling database/login credentials during automated execution via the CLI

For scheduling the PowerShell script, refer the Four ways of scheduling ApexSQL tools article

For a full list of common return codes including a description for each one, check the Common return error code section part of the General usage and common CLI switches article.

 

February 8, 2017