How to deploy a database plus static data

We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if we want to include static data in the build?

This article describes how to the use the SQL Compare and SQL Data Compare GUIs to get the current version of a database schema, and its static data, into version control, and then automate database deployments from the VCS using SQL Compare command line and PowerShell.

What static data is required?

A database build will need to include any ‘immutable’ data required for dependent applications to function, as well as standard data sets for testing, all of which you’d want to be retained under version control:

  • reference or static data – relatively unchanging data such as information about countries, currencies or measurements
  • enumerations – short narrow tables that represents sets of unchanging entities
  • seed data – data that is required to initialize a hierarchical table with the root of the hierarchy
  • domain data – data that defines the business domain and will be static for the lifetime of a database version
  • error explanations for ‘business’ or ‘domain’ errors – such as for bad data in ETL jobs or failed business processes.
  • test data – standard data sets that contain ‘production-like’ data for each of the major areas of functionality supported by the database

One way to include small volumes of static data into a build is simply to add an INSERT INTO...VALUES statement to the end of the individual DDL script for any table that requires static data, but many build systems will prevent you doing this.

However, SQL Data Compare will compare a source database, backup or scripts directory to the target scripts directory in the VCS, and generate static data insert scripts for each table that you specify as containing static data.

This means we can keep the version control directory up-to-date with the latest static data, and deploy any database version, complete with its static data, from the VCS.

Low volume, static data

SQL Data Compare generates the static data in the form of individual INSERT statements for each row. When comparing a data source, say a script folder containing static data insert scripts, to a target database, SQL Data Compare must retrieve the data from the two data sources and copy it to a temporary location on your local machine, potentially across a slow network. It then does a line by line comparison between the scripts folders that contains your static data files, and the data in the target database.

For anything other than small volumes of static data, this means that SQL Data Compare can take a long time to compare and deploy static data, although there are ways to improve its performance. It’s also a technique best used in cases where the design of the static data table is static. Any schema change to a static data table will change every line in the associated static data script, since the data is stored as individual row inserts.

If you need to version larger volumes of static data, such as for test data sets, you may need to investigate alternative ways of loading the data, such as using bulk insert techniques.

Getting the database schema and static data into version control

There are several ways to do this, depending on the tools you have available. If you use SQL Source Control then the obvious way is to simply to link the database version control directory to the database, using SQL Source Control, and it will generate CREATE scripts for all schema objects. You can then use the Link Static data… functionality to create and maintain the static data INSERT scripts.

Alternatively, you can also use a combination of SQL Compare Pro and SQL Data Compare to do the job.

Scripting out database objects using SQL Compare

To create a new scripts folder in VCS, as a one-off task, simply open the SQL Compare Pro GUI, navigate File | Create scripts folder… and define the source from which the scripts should be generated. In this example, the source is the BigPubs database, but it could equally be a database backup, SQL Compare snapshot, or even another scripts folder.

static data 1

Figure 1

The SQL Compare engine will generate the scripts for every schema object in the target scripts folder, with the scripts automatically sorted into sub-folders for each object type, but it won’t include any static data.

Alternatively, we could create a project, in the GUI, which will create the initial scripts directory, and that we can rerun to update it with the latest changes to the source.

Scripting out static data using SQL Data Compare

To deploy static data to the scripts folder, we need SQL Data Compare Pro. Open the GUI, and set up the comparison. Here, I’m simply using the BigPubs database, which contains my static data, as the source, and the previously created scripts folder as the target.

static data 2

Figure 2

In this example, the static data we want to include in our build scripts is in the Jobs table. Using the Tables & views menu, deselect all other tables. Notice that SQL Data Compare uses the PRIMARY KEY as the comparison key.

static data

Figure 3

On running the deployment project, we should see a new Data folder created in the BigPubs scripts folder, and in it a TableName_Data.sql script containing individual INSERT statements to generate each row of static data.

static data 4

Figure 4

For each table, SQL Data Compare also saves to the scripts folder a TableName_Meta.sdcs file, containing index information that enables SQL Data Compare to compare static data.

Creating a database build script that includes static data

With our database schema and static data version controlled, we can build any version of the database, complete with its data.

We can do a two-stage build, first deploying the schema changes with SQL Compare, and the static data changes with SQL Data Compare. Alternatively, we can use the “/include:StaticData” switch in the SQL Compare Pro command line. Listing 1 adapts the PowerShell from Phil Factor’s article to include this switch in the call to SQL Compare. Currently, there is no way of including static data in a deployment script using the SQL Compare Pro GUI.

Listing 1

Run the script, and in the designated Build folder, we find a BigPubs.sql build file that includes the static data changes in the same transaction as the schema changes.

Summary

When you build a database from just the object-level build scripts, there is a chance your database will not function unless certain tables are stocked with static data or enumerations. It might be something as small and simple as a list of countries within which your organization can trade. SQL Compare and SQL Data Compare allow you to do this in most cases, where the quantity of data is insufficient to warrant a more complicated bulk insert operation.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

SQL Data Compare

Compare and deploy SQL Server database contents.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

How to document multiple SQL Server databases using SQL Doc and PowerShell

You can use SQL Doc's command-line parameters to automate database documentation, but when you try to automate the process of documenting a group of databases on a server, they sometimes don't give yo...

Also in Redgate products

Why people flocked to the SQL Clone live stream event

Live streaming events is something that’s rather new to Redgate, the first one being SQL in the City back in December, which was a great success. So with the launch of our new tool SQL Clone looming...

Also about SQL Compare

Automating database deployments to and from source control using SQL Compare and PowerShell

Our goal is to show how to use SQL Compare Pro command line with PowerShell to automate the following processes:

Getting a new database into version control, or updating the object scripts in th...