Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database versioning and deployment with RoundhousE

Database versioning and deployment with RoundhousE

How to simplify database management and deployment with RoundhousE migration utility

Avatar for Ivan Korneliuk

Ivan Korneliuk

September 24, 2015
Tweet

Other Decks in Programming

Transcript

  1. What’s the problem? The main goal is to have a

    safe, repeatable and predictable deployment process. And it should be painless That’s why we use the following techniques: ✤ Database deployment automation ✤ Continuous integration ✤ Deployment to many environments (Dev, Test, Staging, Production)
  2. Ways to upgrade database ✤ Ad hoc updates ✤ Single

    large sql script ✤ State model updates ✤ Database migrations using incremental updates
  3. Three Rules for Database Work 1. Never use a shared

    database server for development work 2. Always Have a Single, Authoritative Source For Your Schema 3. Always Version Your Database –K. Scott Allen http://odetocode.com/blogs/scott/archive/2008/01/30/three-rules-for-database-work.aspx
  4. State model deployments Scripts that represent a snapshot of the

    entire database state are stored for the each version. Deployment tool compares two states and generates a transformation script to upgrade database from one state to another. Tools: ✤ Red Gate Sql Compare ✤ Microsoft SQL Server Data Tools ✤ Other tools exist
  5. Database migrations using incremental updates Each script (or a migration

    for DSL based approach) represents a transition from one database state to the next one Deployment tool runs only new or changed scripts in a predefined order. Scripts execution history is tracked in database itself.
  6. DSL vs Plain Old SQL DSL based tools were originally

    inspired by Rails active record migrations // Entity Framework Code First Migrations example public partial class AddBlogUrl : DbMigration { public override void Up() { AddColumn("dbo.Blogs", "Url", c => c.String()); } public override void Down() { DropColumn("dbo.Blogs", "Url"); } }
  7. “SQL is the best DSL to deal with data” 


    and probably with schema changes –Someone had to say it
  8. Sql migration scripts ✤ Baseline - initial database schema ✤

    Update scripts - transforms database from one state to another ✤ Anytime scripts - stored procedures, views, functions, triggers ✤ Reference data
  9. Baseline The starting point of versioning database. Contains initial database

    schema. After baseline is published (e.g. deployed to customer) any changes to the schema require a schema change script. So baseline scripts are run exactly once to create initial database schema and are not executed during database migration. We don’t usually baseline a database just after a project started. It makes sense to wait a little until db schema becomes more stable. Hey, we don’t have customers yet!
  10. Update scripts Most important ones. Represent a schema transformations like

    adding a table or column, adding an index or a constraint, etc. Also data conversion scripts. Migration utility controls that only new updates are executed on certain database.
  11. DVCS analogy Commit in VCS – a record that represent

    a change to the repository. They are treated as read-only and append-only records. Once they are pushed to the public repository, they can't be changed or removed. You may however add another commit to fix mistakes or to change some behavior. Schema update scripts are similar ones – once they were executed against a public database, you can't change scripts anymore. The only way to fix something is to write another schema update script. Commits like the update scripts represent historical changes. You may apply subsequent commits to any previous state to get the current state of the repository (working copy). Similarly you may upgrade any old version of database to the current state by applying new update scripts. One thing that does not work like in VCS is rollback - after you've migrated database to a certain version, you can't actually revert it back to some previous version (unless we have rollback scripts).
  12. AnyTime scripts Stored procedures, views, functions, triggers are stored on

    a separate files per object. Engine executes them any time they have changed ✤ Easy to change ✤ Change history is available in source repository ✤ No merge conflicts
  13. Database migration tools ✤ RoundhousE ✤ DbUp (from OctopusDeploy creators)

    ✤ Flyway (supports both Sql and Java migrations) ✤ Entity Framework Code First Migrations ✤ FluentMigrator ✤ Many others
  14. RoundhousE Database Migration Utility for .NET using sql files and

    versioning based on source control Part of ChuckNorris tools First commit by Rob Reynolds (@ferventcoder) in 2009
  15. The SQL condition "LIKE '%Chuck Norris%'" can only return Chuck

    Norris himself. 
 Because nothing is like Chuck Norris.
  16. Roundhouse features ✤ Wide range of supported DB engines ✤

    Plain Old SQL Scripts ✤ Low script maintenance. Convenient scripts folder structure ✤ Conventions over configuration ✤ Baseline, OneTime, AnyTime, EveryTime scripts ✤ Runs Only Changes - useful for database with a lot of stored procs ✤ Supports Environment Aware scripts ✤ Token replacements ✤ Versioning ✤ Audit - tracks who applied changes, when and how ✤ Detailed logging ✤ Open Source
  17. Supported engines ✤ MS SQLServer starting from 2000. Works in

    Azure too ✤ Oracle ✤ MySql (Contributed by Alexey Diyan!) ✤ PostgreSQL ✤ SQLite
  18. Logging Change drop folder contains information about all the migrations

    that were ever executed ✤ Detailed log in roundhouse.changes.log ✤ Scripts that were executed
  19. Environment specific scripts LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are

    in the LOCAL environment. This will run based on this check. Running LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql on (local) - TestRoundhousE. TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will NOT run based on this check. Skipped TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql - No changes were found to run. If you name a sql file with .ENV., Roundhouse will start looking for the environment name somewhere on the file. If it doesn't find the proper environment, it will not run the file. In example, if you name a file LOCAL.permissionfile.env.sql and it is migrating the LOCAL environment, it will run the file. But if it is migrating the TEST environment, it will pass over this file.
  20. Token replacement Should be used VERY sparingly but was useful

    in my experience DECLARE @UpdatesAllowed INT = 1 IF '{{EnvironmentName}}' IN ('PRODUCTION', 'UAT') BEGIN PRINT 'Running on ''{{EnvironmentName}}'' environment. Updates are not allowed' SET @UpdatesAllowed = 0 END MERGE dbo.Config dest USING @Config src ON dest.[Key] = src.[Key] WHEN MATCHED AND dest.Value <> src.Value AND @UpdatesAllowed = 1 THEN UPDATE SET Value = src.Value WHEN NOT MATCHED THEN INSERT ([Key], Value) VALUES (src.[Key], src.Value);
  21. How to fix already “released” script? Possible solution is to

    rename script: 0035.Data_conversion.sql => 0035.1.Data_conversion.sql
  22. Keep your scripts idempotent Although RoundhousE runs migration scripts only

    once, it’s a good practice to keep them idempotent. So it’s safe to rename a script or reapply it manually. RoundhousE behaviour could be changed to re-run changed scripts with WarnOnOneTimeScriptChanges option
  23. Where to keep data scripts ✤ Reference, lookup data scripts

    could live in runAfterOtherAnyTimeScripts if written in idempotent way, e.g. using MERGE statements. ✤ Sample data could live in runAfterCreateDatabase so that it’s executed only once - after database is created. Environment specific script is another option. Or could be managed outside of the RondhousE.
  24. Setup migration validation early on Migration validation check should be

    part of the CI process. 1. Have a reference backup of released database version. 2. Have a step on CI build that restores database from that backup and runs migrations against it.
  25. Resources ✤ Roundhouse - https://github.com/chucknorris/roundhouse. See samples and documentation ✤

    Scott Allen - Versioning Database series (link to the latest post with all the links) ✤ Martin Fowler - Evolutionary Database Design article ✤ Scott J Ambler and Pramod J. Sadalage - Database refactoring: Evolutionary Database Design book ✤ Pramod J. Sadalage - Talk on “Database refactoring: Evoltionary Database Design” ✤ Enrico Campidoglio - “Databases: the elephant in the Continuous Delivery room” talk at NDC ✤ Alexey Diyan - “Database automated deployment and versioning …for smart people”
  26. Acknowledgment ✤ Rob Reynolds (@ferventcoder) for creating RounhousE ✤ Alexander

    Beletsky (@alexbeletsky) and Kiev Alt Net group for introducing RoundhousE for me for the first time ✤ Denis Reznik (@DenisReznik) for forcing me to make this talk