This example shows you how to automate the deployment of your database changes to a preproduction environment before releasing to a production environment, using DLM Automation and Atlassian Bamboo.

Overview

Your preproduction environment should contain a database that can be used for testing deployments, and have access to any external references your database requires.

You'll use a Bamboo deployment project to deploy a database to preproduction and get your database package ready for release to production. All stages beyond the first are optional, although you'll need to do one of 4 or 5 to use your package.

1. Build your database package from your SQL Source Control database folder, validating the contents by temporary deployment to either LocalDB or a server of your choice.
2. Run your tSQLt unit tests against a temporary copy of the database, if you have them.
3. Deploy the package to preproduction ready to run your integration tests.
4. Publish your package to your NuGet server, if you have one, ready for deployment to Production with a release management tool like Octopus Deploy.
5. Expose your package as an artifact for other Bamboo plans.

If you want to deploy to production from a Bamboo deployment project, you will currently need to use the Powershell cmdlets - see Deploy a database package manually for further details.

Before you start

1. Install Bamboo

2. Install DLM Automation on the machine running the Bamboo agent. For more information, see Installing (DLM Automation documentation).
3. Install the DLM Automation Build Plugin.
4. Link the development database with the changes you want to deploy in SQL Source Control, and commit it to a version control system that can be used with Bamboo

1. Set up database package build with DLM Automation

Select 'Create' from the top bar, and 'Create a New Plan'.

Fill in the Configure Plan screen, either attaching it to your application build project or creating a new project for your database build, and enter a plan name as usual.

Link the repository which contains your development database folder created by SQL Source Control.

Press 'Configure plan' to continue.

Press the 'Add Task' button and search for 'Redgate'. Select the 'RedGate DLM Automation Build' task - this task must be run before any of the other tasks.

Fill in the fields presented. If your database has features not supported by LocalDB, like full text indexing, you will need to specify a SQL Server instance to perform validation on - this will create a temporary database, which it will clean up before the end of the build task.

The names of any SQL Compare Options you require can be found in the SQL Compare documentation: Options used in the command line - and should be specified as a comma-separated list, with a minus sign in front of any default options you wish to negate.

Press 'Save' and then 'Create' to finish creating your build plan with a default job.

2. Set up tSQLt database unit tests

If you have tSQLt unit tests to run on your database (which you can make via SQL Test in SSMS), now add a task for this.

Edit the job you just created, press 'Add task' and search for 'redgate' again - this time, select Redgate DLM Automation Test.

Use the same Package ID that you used in the Build task. Fill in the other options according to your environment; you can have new test data generated for each run by including a SQL Data Generator project.

If you want a test report other than in the build log, you can set up an artifact to save the test report. Select the 'Artifacts' tab, press 'Create Definition', and use the copy pattern *.${bamboo.buildNumber}reports.junit.xml to save the XML report.

3. Update pre-production environment ready for integration testing

If you want to deploy to pre-production, you can add another task to the same job, this time selecting Redgate DLM Automation Sync.

You will need to specify the server name and database name to deploy to pre-production, and any Compare options you want to apply to the deployment (using the same syntax as in the Build step above).

4. Publish your database package to NuGet for use in release management tools

Once you have satisfied yourself that the above build is working, you may want to add a publish to NuGet step.

To publish to NuGet, add the Redgate DLM Automation Publish task after your other tasks. As long as you use the same package name throughout, when the other stages have run successfully it will pick up the package used in the other stages and publish it to the specified NuGet server. If you need to publish the package while the tests are still failing, you will need to put it in a separate build plan as for the Sync task above.

5. Expose your database package as an artifact for other Bamboo plans

You may also want to define the artifact that will expose your database package for other jobs (or a release using the Powershell cmdlets - see Deploy a database package manually).

Click on the name of the job you just created, and select the 'Artifacts' tab.

Press 'Create Definition', give the artifact a name, and use the copy pattern *.nupkg 

Share the artifact so that other plans can use it.