This tutorial shows how to manually run PowerShell scripts to deploy a NuGet database package to a production database without using a release management tool.

You'll use DLM Automation cmdlets to create deployment resources, including the update script, which you can review before you continue with the deployment.

Before you start

1. Install DLM Automation. For more information, see Installing.
2. Download this NuGet package, Widget.1.0.0.nupkg, which you can use for this tutorial.
3. Download this SQL creation script and run in SQL Server Management Studio, to create a target database, WidgetProduction, which you can use in this tutorial.

1. Create the deployment resources

In this step, you run a PowerShell script to:

• create objects for the NuGet package and WidgetProduction database
• compare the schemas and create the deployment resources that includes the update script that will be used for deployment 
• export the deployment resources to a specified folder so you can review the update script

To create the deployment resources:

1. From the Start menu, open Windows PowerShell ISE.

   By default, PowerShell doesn't let you run saved scripts. If you need to do this, you may have to change your security policy.

   You only need to do this once.

2. In the top pane of PowerShell ISE, paste this PowerShell script:

   Create and export the deployment resources

   $errorActionPreference = "stop" 
    
   # creates objects that let DLM Automation cmdlets connect to the NuGet package and production database
   $version = "1.0.0"
   $source = "C:\packages\Widget.$version.nupkg"
   $production = New-DlmDatabaseConnection -ServerInstance "your-server\sql2014" -Database "WidgetProduction" -Username "sa" -Password "p@ssw0rd"
    
   # Create a database Release
   New-DlmDatabaseRelease -Source $source -Target $production | Export-DlmDatabaseRelease -Path "C:\Work\Export" -Force
   

3. On line 5 of the script, change the path of the NuGet package to where you've downloaded this.
4. On line 6 of the script, change the ServerInstance parameters to the SQL Server you installed the databases on.
5. On line 6 of the script, change the Username and Password parameters to the credentials of a SQL Server sa account.
   If you specify the Username and Password parameters, the DLM Automation cmdlets connect to the databases using SQL Server authentication. If you leave out these parameters, the cmdlets use Windows authentication, and connect using the account that runs PowerShell.
6. On line 9 of the script, change the Path parameter to an empty folder that can be exported to. The cmdlet will create the folder if it doesn't already exist.
7. Press F5 to run the script.

2. Review the deployment resources

Go to the location you specified in the Path parameter of the Export-DatabaseRelease cmdlet. In this example, the path is C:\Work\Export. The folder contains the database deployment resources:

Update.sql

This is the update script that you can review to see what will be deployed to WidgetProduction. 

Reports/Changes.html

This web page shows the differences between the schemas in Source Control and on WidgetProduction. Review this if you want to see the current schemas of the databases, and a summary of what changes the update script will make. This includes changes to static data. The report also shows warnings of any potential data loss and the SQL update script that will be run.

Other files

You don't need to look at these files. They're used by DLM Automation cmdlets during pre- and post-deployment checks. 

3. Run the deployment

In this step, you run a PowerShell script so the DLM Automation cmdlets can:

• import the deployment resources you exported in step 1: Create the deployment resources
• deploy the NuGet package schema to WidgetProduction

To deploy the changes from the NuGet package to WidgetProduction:

1. In PowerShell ISE, create a new PowerShell script under File > New.

2. In the top pane of PowerShell ISE, paste this PowerShell script:

   Import and deploy the deployment resources

   $errorActionPreference = "stop"
    
   # creates object that let DLM Automation cmdlets connect to the production database
   $production = New-DlmDatabaseConnection -ServerInstance "your-server\sql2014" -Database "WidgetProduction" -Username "sa" -Password "p@ssw0rd"
   
   # imports the deployment resources you reviewed, and runs the deployment
   Import-DlmDatabaseRelease "C:\Work\Export" | Use-DlmDatabaseRelease -DeployTo $production

3. On line 4 of the script, change the ServerInstance, Username, and Password parameters to match the PowerShell script from step 1: Create the deployment resources.

4. On line 7 of the script, change the Path parameter to match the PowerShell script from step 1: Create the deployment resources.
5. Press F5 to run the script.

In SQL Server Management Studio, look at the changes that have been made to WidgetProduction.

What next?

Now you've tried using the DLM Automation cmdlets to release a database, you can set up your release management tool to run the same PowerShell script. For instructions on how to do this using Octopus Deploy, see Deploy a database package to a single environment using Octopus Deploy

For details of each DLM Automation cmdlet, see Cmdlet reference.