Deploying static data with DLM Automation cmdlets
Published 10 March 2016
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
-Source: a path to a scripts folder or NuGet package containing a scripts folder
-Target: a database connection string or object created by the
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:
|1||United 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
1will be changed to
Germanywill be removed
UKwill be added
Make these changes so that static data between the package and the Staging database is identical:
Use-DlmDatabaseRelease $release -DeployTo $staging
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-DlmDatabaseReleasecmdlet 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.