Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Deploy a Database Plus Static Data With SQL Compare and SQL Data Compare

DZone's Guide to

Deploy a Database Plus Static Data With SQL Compare and SQL Data Compare

The SQL Compare and SQL Data Compare GUIs can get current version of a database schema and its static data into version control and automate database deployments from the VCS.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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 represent 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 INSERTINTO...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 INSERTscripts.

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

$SQLCompare="${env:ProgramFiles(x86)}\Red Gate\SQL Compare 12\sqlcompare.exe"# full path
$MyServerInstance='tony-sql2016.testnet.red-gate.com' #The SQL Server instance
$MyDatabase='BigPubs' #The name of the database
$MyDatabasePath =
"$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase"
$MyBuildFile = "$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Build"

if (-not (Test-Path -PathType Container $MyBuildFile))
    {
        # we create the build directory
        New-Item -ItemType Directory -Force -Path $MyBuildFile;
    }

$AllArgs = @("/scripts1:$MyDatabasePath", "/server2:$MyServerInstance",
      '/quiet', "/database2:model", "/include:StaticData", "/scriptfile:$MyBuildFile\$MyDatabase.sql")
if (Test-Path "$MyBuildFile\$MyDatabase.sql" ) {Remove-item "$MyBuildFile\$MyDatabase.sql" }
&$SQLCompare $AllArgs
if ($?) {'Script generated successfully'}
   else {"we had an error! (code $LASTEXITCODE)"}

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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
sql ,database ,tutorial ,static data ,sql compare ,sql data compare

Published at DZone with permission of Tony Davis, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}