That Conference 2017 – Continuous Database Deployment

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
Advertisements

2 thoughts on “That Conference 2017 – Continuous Database Deployment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s