SQL Server Continuous Database Delivery with Flyway

Delivering database changes pose a unique set of challenges that force many to treat database changes as a second class citizen when releasing software. You'll learn how to use Flyway to version changes to a SQL Server database, and how to leverage versioned changes from your code repository to get early feedback about potential problems, including how to simulate a production database update every time someone checks in a database change.
Course info
Rating
(56)
Level
Intermediate
Updated
May 13, 2015
Duration
6h 25m
Table of contents
Continuous Database Delivery
Tracking Changes
Development Workflow
Reverse Engineering Dev DB Changes
Pulling Changes
Delivery Workflow
Transition Existing Databases
Rethinking How We Develop
Description
Course info
Rating
(56)
Level
Intermediate
Updated
May 13, 2015
Duration
6h 25m
Description

Continuous Delivery can dramatically decrease turn-around time between customer need and delivered software to fulfill that need. Combining discipline with the right practices and tools leads to early feedback about potential problems, a system of safety nets, the highest level of accountability, and an overall boost in confidence when releasing software. Delivering database changes poses a unique set of challenges that force many to treat database changes as a second class citizen when releasing software. This leads to delays and a lack of realizing the full potential of Continuous Delivery. In this course, you’ll learn the fundamentals to make managing database change a breeze and how to make it a first class citizen in your Continuous Delivery pipeline. You'll learn how to use Flyway to version changes to a SQL Server database. We will also cover how to leverage versioned changes from your code repository to get early feedback about potential problems, including how to simulate a production database update every time someone checks in a database change.

About the author
About the author

Wes Higbee is passionate about helping companies achieve remarkable results with technology and software. He’s had extensive experience developing software and working with teams to improve how software is developed to meet business objectives. Wes launched Full City Tech to leverage his expertise to help companies delight customers.

More from the author
What Is DevOps: Executive Briefing
Beginner
22m
Nov 2, 2018
Implementing a Self-hosted Docker Registry
Intermediate
4h 8m
Jun 5, 2018
Webpack: Transpiling and Bundling JavaScript
Intermediate
4h 55m
Jan 30, 2018
More courses by Wes Higbee
Section Introduction Transcripts
Section Introduction Transcripts

Tracking Changes
In this new wholesome approach, the first thing we are going to look at is how we can track changes. Why? Because as you move to a model where each developer has their own dedicated development database, they'll need a means to track their changes so they can share them with others. And of course this begs the question, how do we go from a model where the golden version of the truth was a shared development database to a model where the files in version control represent the current state of our database. First, we're going to discuss a conceptual approach to how we can track changes. Then we'll look at a tool called Flyway to help us track changes and apply them automatically. Then we'll use Flyway throughout the rest of the module to start building out an actual database. By the end of the module, you'll see how easy it is to not only track changes but to apply them automatically, to create new databases on the fly, and to share your changes with everybody else through version control such that every single step in the path those changes took is captured.

Development Workflow
Now that we know how to track changes to a database and apply them automatically with Flyway, it would be a good idea to talk about how we can fit this into our development workflow, how on a day-to-day basis you can go about creating changes, testing them, and then sharing them with other people, and what happens when things go wrong in this process? For example, what happens when we create a change script that fails to apply to a database, and what happens when we forget to put something into a SQL change script after we've shared the script with other people. In this module we'll focus on making changes to our local SQL Server database with the tools we've been using in the past, and keeping some notes in the process so we can create a SQL change script when we're done, and in the next module we'll take a look at how we can go about reverse engineering our changes if we don't have notes that are accurate enough to help us create the change script.

Pulling Changes
In the last couple of modules, we've been focusing on how we can develop our own changes in our own local dedicated development database, and specifically how we can track changes with Flyway and store them in version control so that we can share them with other people and they can apply them automatically to their development database. In this module, we're going to turn things around and look at how other people can share changes through version control with us, and specifically how we go about finding out when other people have checked in changes.

Transition Existing Databases
Thus far we've been working with a brand-new payroll database, but the reality is we all have existing databases that we might like to bring under this wholesome approach. Wouldn't it be nice to have a strategy to take an existing database and bring it under this wholesome approach, in other words a step-by-step process to help you bring it into version control such that you can start tracking and applying new changes to that database, sharing them with other people, helping people set up dedicated development databases, and then eventually migrating your release process to incorporate all these fantastic processes? By the end of this module, that's what you'll have, a step-by-step process to help you take an existing database and bring it under this wholesome approach.

Rethinking How We Develop
Now that we have a process that allows us to not treat database changes as second-class citizens, we have the opportunity to rethink how we actually develop. In this module I want to talk about a couple of aspects of this. First we'll discuss the opportunity to deliver more frequently and why we would want to do this. We'll also talk about how we get buy-in with this process in general, regardless of how we develop. I'll take a minute to squeeze in a little discussion about code-based migrations versus SQL-based migrations, because it's a great example of a situation where we can incentivize other people's participation based on the choices we make. We'll talk about what happens with database changes and branching, how we can be creative with our database changes, and how this process will allow you to start rethinking failure in terms of releases and challenge the traditional rollback model.