Continuous Deployment Database Project VSTS

Question

Looking for continuous deployment strategies regarding SQL server database projects targeting Azure SQL using VSTS. My scenario...

• Using VSTS for CI/CD
• Using a SQL Server Database Project to define my database schema
• Using Dapper for my ORM (so no migrations through EF)
• Using an Azure SQL Database
• Some of the tables have seed data, that will most likely be added to over time (assume no seed data will be deleted for the moment)
• Using integration tests that target a separate database, as the tests will wipe each table's data
• Using Git flow

I see three scenarios that I need to handle

1. Non-destructive database changes are made to the development branch, that may or may not include data changes
2. Destructive database changes are made to the development branch
3. Multiple commits with a combination of scenarios 1 and 2 need to be merged into the master branch

The first scenario can easily be managed using VSTS's built in Azure SQL Deployment task and DACPACs. Seed data will be added using pre/post deployment scripts

The second scenario is a little tougher, however it should also follow the same approach as scenario one with the pre-deployment script being much more important as it would be responsible for removing constraints, deleting data, etc...

The third approach I'm at a complete loss on. What will happen is that I have a variety of commits that need to be merged into a single branch, all with pre/post deployment scripts that need to be executed in a specific order. I'm not aware of a strategy or tool that can handle this, and I'm looking for suggestions here.

Finally, and this isn't extremely important, but I also have integration tests that target a test database that I'd like to run during the build (CI part) as opposed to the release (CD) part. Is anyone aware of a guide or how-to on implementing integration tests against a test database as part of a CI process?

-Tim

Answer 1

Here's how we do it...

• Any simple changes that can be made via DACPAC deployment are handled in that manner
• For any other changes, an individual SQL script is created which makes the change and handles any migrations
  • The scripts are each named a specific, incrementing version (i.e. 5.1.4.sql, 5.1.5.sql)
  • When a script is run, it is tracked in the database using a DeploymentScripts table
  • The pre-deployment script calls each of the scripts, first checking DeploymentScripts to verify that they haven't been called yet
• When it comes to merging multiple commits into master, all you need to do is confirm that every required script has been included in the pre-deployment script and that they are in the correct order

If you'd like to run integration tests during the build process, I don't know of a built-in way to do this, but you could probably add a PowerShell task which triggers a release and then triggers any test you might want to run. The trick would be returning an exit code to the build task which fails it.