That Conference 2017, Kalahari Resort, Lake Delton, WI
Continuous Database Deployment – Mike Acord

Day 2, 8 Aug 2017

Disclaimer: This post contains my own thoughts and notes based on attending That Conference 2017 presentations. Some content maps directly to what was originally presented. Other content is paraphrased or represents my own thoughts and opinions and should not be construed as reflecting the opinion of the speakers.

Executive Summary

• Important to have database assets under source control
• Several different strategies, each with pros/cons
• Most likely approaches–schema-based or script-based

Continuous Deployments

• We continuously deploy our code
  • Automated deployments
  • Integration tests
  • Build pipeline
• But we’re not doing this with databases
  • More difficult
  • Harder to avoid losing data
  • More risky

Databases – Instead

• Manual change scripts
• Schema comparisons (SQL Compare)
• Create change script, run against database
• Problems
  • Error prone
  • Risky
  • Slow

What Is Continuous Database Deployment

• Automated deployments
• Integration tests
• Build pipeline
• Diagram
  • Database should be in source control
  • Trigger from changes in CM leads to Continuous Integration
  • Leads to Release Management
  • Staged releases–QA, Test

Benefits

• Less error prone, more repeatable
• Anybody can do this
• Testable–deploying to different environments
  • At each phase, you’re re-testing the deployment script
• Faster to release
• Easier to merge branches
  • Work item has unique code and unique database changes
• Easier to refresh environments
  • And easier to refresh to different environments
  • Stuff already in DEV, ready to go

Methods

• Schema-based
  • Current schema is always in source control
  • Script in CM for every object in your database
• Script-based
  • Change scripts checked in and applied
  • You just create a delta script
  • Never have full history
• Code-based
  • Apply database changes from code
  • E.g. Migrations in .NET

Schema Based

• Provides history of schema
• Can generate change scripts to reverse changes
• Less likely to screw up change scripts
• Compare and sync schema to deploy changes
• Can make to database and sync back to model
  • Can generate blank database at an point
• Challenges
  • Merging can be challenging
  • Black voodoo magic leads to nasty change scripts
  • Deployment process is more complex
  • Forget to check in changes and lose them

SQL Source Control

• Shows changes
• Create migration scripts
• Deals with situation of adding non-nullable columns

Visual Studio Database Project

• SQLCompare utility to apply schema changes

Example

• Octopus Deploy used to deploy changes
• Creates Powershell scripts (or uses scripts)
  • Applying .dacpac files using SQL Package cmd line tools
• NB: RedGate has plugins to SQL Source Control for various CI tools

Script Based

• Create change scripts to apply
  • Script for every change
• Simple to understand
• Easy to implement
• No black magic
• Easy to test scripts
• Challenges
  • May need to review change scripts
  • No ‘current’ database that you can deploy
    • Backup/restore to get copy of current

Example – DBUP + Octopus Deploy

• Storing migration scripts in DB project
• DBUP uses LOG in database to see what needs to be run; applies migration in order
• Also then applies environment-specific scripts

Code Based

• Quick setup for new developers
• Allows for seed data
• Simple to use
• Can generate change scripts
• Challenges
  • Testing changes can be awkward
    • Hard to test because migration only happens in the context of running your app
  • May feel unnatural
  • Rolling deployments more challenging
    • One app does the update, but other apps aren’t yet up to date

Example – Entity Framework Migrations

Database Refresh – Keep Data Current

• Quickly refresh environments from production
• Can be scheduled to happen daily
• Allows better diagnosis of data issues
• Daily testing of deployment scripts
  • By virtue of pushing between DEV / STAGE / PRODUCTIOn
• Challenges
  • Clustered servers
  • Large databases
    • Can be painful, >1TB

Example – Database Refresh

• Using Octopus Deploy

Best Practices

• Create backups or snapshots before doing anything else
• Avoid data loss
• Automated integration tests
• Deploy breaking changes in steps
• Example (field rename)
  • Add new field with synchronization trigger
  • Modify application to use new column
  • Remove old column and synchronization trigger
• Do this in stages when you can’t take time window to do everything at once
• Lots of patterns for applying changes in non-breaking patterns like this
  • Agiledata.org/essays/renameColumn.html
  • Book – Refactoring Databases

Questions

• Problem with database project – comparing two schemas, scripts that it generates just don’t work
  • Turn on setting to catch errors on project build