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
- Non-destructive database changes are made to the development branch, that may or may not include data changes
- Destructive database changes are made to the development branch
- 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?