SQL Release

Deploying static data with SQL Release

If you use SQL Source Control, you can flag tables in your database that contain reference or static data. Typically, these tables contain data that doesn't change often, for example, a list of countries. To find out more, see Static data (SQL Source Control documentation).

By default, SQL Release will synchronize the data in these tables between the source schema and target database. 

SQL Release can only synchronize static data if you specify both the following as values for New-DlmDatabaseRelease:

-Source: a path to a scripts folder or NuGet package file containing a scripts folder

-Target: a database connection string or object created by New-DlmDatabaseConnection

You can specify more than one database as the value of -Target; SQL Release will check that the static data is the same between them.


Create a connection to the Staging database we want to update, and specify the location of a NuGet package containing the latest version of the database:

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2012" -Database "Staging"
$package = "C:\NuGetFeed\myDatabase.2.1.nupkg"

The Staging database contains a static data table, Countries, with the following data:

1United States of America

The NuGet package contains the same table but with different data:


Create the database deployment resources:

$release = New-DlmDatabaseRelease -Target $staging -Source $package

If we deploy this update to the Staging database:

  • the name of country with id 1 will be changed to Brazil
  • Germany will be removed
  • UK will be added

Make these changes so that static data between the package and the Staging database is identical:

Use-DlmDatabaseRelease $release -DeployTo $staging

SQL Release runs two schema checks against static data - one before and one after running the update script:

  • the pre-deploy schema check makes sure static data in the Staging database hasn't changed since the New-DlmDatabaseRelease cmdlet was run
  • the post-deploy schema check makes sure static data in the Staging database has updated to the source schema in the package

Excluding static data changes

To exclude static data changes, add -IgnoreStaticData to the New-DlmDatabaseRelease cmdlet. This is useful if you do not want to deploy test data to a production environment.

Didn't find what you were looking for?