This tutorial shows how to use the DLM Automation PowerShell cmdlets to performs continuous integration tasks on the script folder in your source control repository. This can be done from the PowerShell console or another PowerShell runner (for example your build server). In this tutorial we will use the console but this can easily be applied to your build server or similar.

The cmdlets that we'll use will perform the following tasks:

1. Validate: Validate the schema in the source control scripts folder by checking the database can be built from scratch successfully. 
2. Test: Run tSQLt tests on the database schema.
3. Sync: Update a test database to match the database schema.

Before you start

1. Install DLM Automation. For more information, see Installing.
   
2. Get a copy of your database scripts folder, you can either:
   1. checkout a copy from your database source controlled using SQL Source Control
   2. generate a folder from an existing database using SQL Compare
   3. download and unzip WidgetShop.zip
3. Create a blank database that we can update.
   

Performing Continuous Integration tasks

In this step, you run a PowerShell script to perform all the continuous integration tasks:

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:

   Validating the scripts folder

   $errorActionPreference = "stop"
    
   # Validate the scripts folder
   $scriptsFolder = "C:\Work\scripts" # The scripts folder to validate, test and sync
   $validatedScriptsFolder = Invoke-DlmDatabaseSchemaValidation $scriptsFolder

3. Update $scriptsFolder to the location of the scripts folder you created earlier.

4. This will check that your scripts folder is valid and can be used to produce a database.

5. In top pane of PowerShell ISE add the following to the end of the script:

   Test the scripts folder

   # Run tSQLt tests
   $testResults = Invoke-DlmDatabaseTests $validatedScriptsFolder
   Export-DlmDatabaseTestResults $testResults -OutputFile "C:\Work\TestResults\scripts.junit.xml"

6. This will run any tests in your database and store them in the $testResults variable and the file "C:\Work\TestResults\scripts.junit.xml" for review.

7. Add these lines to the end of your script:

   Syncing to a test database

   # Sync a test database
   $testdb = New-DlmDatabaseConnection -ServerInstance ".\sql2012" -Database "TestDatabase" # Update this to use the blank database you created earlier
   Test-DlmDatabaseConnection $testdb
   Sync-DlmDatabaseSchema -Source $validatedScriptsFolder -Target $testdb

8. Update the connection details to the blank database you created earlier. If using SQL Server Authentication, add the desired credentials using the -Username and -Password parameters.

9. This will update the test database to match your scripts folder.
10. Press F5 to run the script.

What next?

To automate continuous integration, run the same PowerShell script featured in this tutorial from a step in your build server that's triggered every time there's a change your development schema. To learn how to do this using TeamCity, see Set up continuous integration with TeamCity.