SQL Change Automation 3

Deploying static data with SQL Change Automation PowerShell

This page shows how to deploy static data using the SQL Change Automation PowerShell for SQL Source Control projects.

Preparing the project for static data deployments

Before you can deploy static data changes with the PowerShell cmdlets, static data changes must be included in the SQL Change Automation compatible project to be deployed.

To see how to include static data in SQL Change Automation projects, see Static Data.

To see how to include static data in SQL Source Control projects, see Static data (SQL Source Control documentation).

Deployment

By default, the SQL Change 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-DatabaseReleaseArtifact 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-DatabaseConnection cmdlet

Static data is NOT supported when using system named constraints. To use static data you need to name your constraints.

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-DatabaseConnection -ServerInstance "staging01\sql2012" -Database "Staging"
$package = "C:\Work\buildArtifacts\DatabaseBuildArtifact.1.0.0.nupkg"

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

IdName
1United States of America
2India
3Germany

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

IdName
1Brazil
2India
4UK

Create the database deployment resources:

$release = New-DatabaseReleaseArtifact -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-DatabaseReleaseArtifact $release -DeployTo $staging

The Use-DatabaseReleaseArtifact 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-DatabaseReleaseArtifact 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-DatabaseReleaseARtifact 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?