This page provides and overview of the cmdlets for deploying your database.

 When you're deploying the latest development version of your database to an environment such as production, you could use the Sync-DlmDatabaseSchema cmdlet (for more information, see Sync). However, the SQL Release PowerShell module contains a set of cmdlets specifically designed for deployment. These offer a number of advantages:

• They create deployment resources that allow you to review the changes that will happen to the target database if you proceed with the deployment.
• They run pre- and post-deployment checks to make sure your target schema hasn't changed unexpectedly.

New-DlmDatabaseRelease

This cmdlet compares two database schemas and creates deployment resources.

$DB1 = New-DlmDatabaseConnection -ServerInstance "your-server\sql2014" -Database "WidgetStaging" -Username "sa" -Password "p@ssw0rd"
$DB2 = New-DlmDatabaseConnection -ServerInstance "your-server\sql2014" -Database "WidgetProduction" -Username "sa" -Password "p@ssw0rd"
$dbRelease = New-DlmDatabaseRelease -Source $DB1 -Target $DB2 

In the example above, we've used the New-DlmDatabaseRelease cmdlet to compare two database schemas:

• The source schema - the latest version you've been working on (typically your development database)
• The target schema - the database you want to update (for example, your production database)

If you've created a database (see Package and publish), you can use this as the source schema:

 $production = New-DlmDatabaseConnection -ServerInstance "prod01\sql2014" -Database "WidgetProduction" -Username "sa" -Password "p@ssw0rd"
 $dbRelease = New-DlmDatabaseRelease -Source "C:\packages\MyDatabase.1.0.0.nupkg" -Target $production 

After running the cmdlet, you can either use:

• Export-DlmDatabaseRelease - to review the deployment resources, including the update script

• Use-DlmDatabaseRelease - to run the update script against the target database 

Export-DlmDatabaseRelease

Use this cmdlet to export the output of the New-DlmDatabaseRelease cmdlet to disk, so you can review the deployment resources.

Export-DlmDatabaseRelease $dbRelease -Path "C:\SQLReleaseUpdates\"

After running the cmdlet, open C:\SQLReleaseUpdates to see the database deployment resources. These include:

• an Update.sql file - the SQL script that will update the target database schema to match the source schema
• a Reports folder that lets you review a summary of changes between the two databases and check warnings
• a States folder that contains scripts folder representations of both the source and target schemas

Use-DlmDatabaseRelease

This cmdlet completes the deployment set up by New-DlmDatabaseRelease. It does this by running the update script in the deployment resources against the target database.

Use-DlmDatabaseRelease $dbRelease -DeployTo $production 

As part of the deployment, the Use-DlmDatabaseRelease cmdlet runs two checks:

• pre-deployment check
  Before running the update script, this checks the schema of the database you're deploying to hasn't changed since the creation of the database deployment resources. If the schema has changed, the cmdlet will raise an error.
• post-deployment check
  After running the update script, this checks the schema of the database you're deploying to matches the source database. If they don't match, the cmdlet will raise an error.

An example deployment script

Let's now combine the cmdlets we've looked at on this page to make a PowerShell script for a full deployment script:

# Create a database Release
$production = New-DlmDatabaseConnection -ServerInstance "prod01\sql2014" -Database "WidgetProduction" -Username "sa" -Password "p@ssw0rd"
$dbRelease = New-DlmDatabaseRelease -Source "C:\packages\MyDatabase.1.0.0.nupkg" -Target $production
Export-DlmDatabaseRelease $dbRelease -Path "C:\SQLReleaseUpdates\"
 
# Manually review the deployment resources
 
# Use the database Release to deploy to Production
Use-DlmDatabaseRelease $dbRelease -DeployTo $production

What next?

You can find out more about using the SQL Release cmdlets with build servers and release management tools, from our worked examples.