Deploying static data with SQL Release
Published 16 January 2015
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.
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:
| Id | Name |
|---|---|
| 1 | United States of America |
| 2 | India |
| 3 | Germany |
The NuGet package contains the same table but with different data:
| Id | Name |
|---|---|
| 1 | Brazil |
| 2 | India |
| 4 | UK |
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 toBrazil Germanywill be removedUKwill 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.
