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. 

Example

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:

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.