DLM Automation 2

Deploying static data with DLM Automation cmdlets

This page shows how to deploy static data using the DLM Automation cmdlets.

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 rarely changes, such as a list of countries. To find out more, see Static data (SQL Source Control documentation).

By default, the DLM Automation cmdlets will synchronize the data in these tables between the source schema and target database. 

To synchronize static data you must specify both the following as values for the New-DlmDatabaseRelease cmdet:

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

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

You can specify more than one database as the value of -Target. During deployment, the New-DlmDatabaseRelease will check 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

The Use-DlmDatabaseRelease cmdlet 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 the -IgnoreStaticData parameter to the New-DlmDatabaseRelease cmdlet. This is useful if you don't want to deploy test data to a production environment.

Didn't find what you were looking for?