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

You’re not delivering DevOps to the database

I’ve read through a number of the industry thought leaders to get an understanding of how DevOps is being communicated out there. As with so much else in life, you can start at Wikipedia to get a ge...

Also in Redgate products

The Louis Davidson custom style for SQL Prompt

My previous article in this series explained why it's important for a development team to adopt a common standard for formatting SQL code. It also gave a broad overview of the styles and actions withi...

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...

  • Tim Ververs

    Cool! But only 50% of my table is static data, where can I add a where clause on linking my static data to source control?

  • not the river I stand in

    One big limitation of these tools is that they don’t script the creation of the database and its properties. That is, there is no script created by SQL Compare that includes the CREATE DATABASE statement or sets its properties. This is a semi-big deal for us since all our tables are partitioned. If a dev wants to create a new database, somewhere that dev needs to create the database and the file groups. Not to mention other database-level attributes (the obvious one is recovery mode but there are many others).

    SQL Compare really needs to take this to the next level — literally! I hope that in some imminent version we see the database creation itself being scripted and source controlled.

  • Matt Hilbert

    Nice post, and very informative.