The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of companies already have developers in their team who work across both applications and databases, and the biggest driver for including the database in DevOps is to increase the speed of delivery of database changes.
While there’s a big interest in integrating database changes into a DevOps process, the greatest challenge is synchronizing application and database changes, and overcoming different development approaches.
In a typical application automated deployment pipeline, for example, deployments are linked to source controlled versions of the application code so the changes made in development can be associated to what has been deployed:
Databases are more problematic
While it would be great to simply regard the SQL code that builds, maintains and updates the database schema as just that, code, and deploy changes through source control, continuous integration, and release management, four issues arise.
- Deployments must preserve the existing data
- Static data vs production data
- DBAs aren’t always part of DevOps
- Databases can drift
Application code is pretty easy to change, but with a database the data has to persist. You can’t simply drop and replace the database, like you would an application. Instead, you have to create some sort of upgrade script, preferably from source control, and preferably in an automated way to simplify the process.
While the database contains customer and perhaps transaction data, there is other data too, outside the realm of production. Reference data, lookup data, static data – all the data that makes your system work – needs to be deployed alongside schema changes. And you also need to think about how to transfer data in the other direction for testing. How do you test the latest build in development with production or production-like data, for example?
The word DevOps refers to the problems associated with Dev and Ops teams working in siloes. Nowhere is this more apparent than in the land of the database. Is there anyone who has never heard of any problems between Dev and DBA teams?
But it goes further than that. With application source code we use source control, we invented distributed source control systems and we debate about the optimal branching strategies and strategies for implementing continuous integration.
We’re now beginning to have these conversations about databases. We need strategies for how to provision individual developers with their own copies of databases to use in sandbox environments. Different developers work in different ways, some working off scripts and others working directly on the database, which calls for a better way of working together.
When we talk about DevOps and Continuous Delivery, the term cycle time often comes up. How long does it take to make a one line change, run it through the normal testing process and get it to production?
If the cycle time for your database is measured in days, weeks or months, then when you hit a production issue, you don’t have time to go back to your source code. The business is hemorrhaging money and the DBA will often make a decision to perform a hot fix on the production database.
This drift causes problems. Environment inconsistencies undermine tests and can cause failed deployments, either because code clashes or because important fixes are accidentally rolled back. Drift and poor DevOps processes are a vicious circle that needs to be broken.
The deployment pipeline starts with source control
Everyone is already doing source control for application code, and any deployment, whether for the application or database, needs to be linked to a source controlled version so we can associate the changes made in development to what has been deployed.
But it also begs the question, what database artifact should be in source control? The database code or the upgrade scripts? There is no right or wrong answer because both can be the right answer at different times.
In state-based deployments, the deployment script is generated dynamically at deployment time using a tool like SQL Source Control. A plug-in for SQL Server Management Studio (SSMS), it aims to enable a single step between the current database state and the desired database state.
The current state of each object in the database is versioned as a CREATE script. SQL Source Control compares the source to the target and auto-generates a script to synchronize the two states using the industry-standard SQL Compare engine:
ReadyRoll auto-generates numerically ordered migration scripts, again using the SQL Compare engine. Changes (often ALTER commands) are then organized in SQL scripts and run in order to migrate a database from one version to the next:
There’s no right way – and there’s no wrong way
People often talk about state- or migrations-based deployments as if it’s a hard choice. It isn’t. Sometimes, you’ll need the per-object history and rollback advantages of the state-based approach. Other times, you’ll want the closer control over the deployment code or the dependency ordering that the migrations-based approach offers.
That’s why SQL Source Control and ReadyRoll have been developed to offer a flavor of both worlds with a hybrid approach.
SQL Source Control is referred to as a state-first tool. It’s perfect for state-based deployments, but it also provides the ability to add custom SQL scripts to deployment scripts to avoid losing data during complex deployments.
Similarly, ReadyRoll is referred to as a migrations-first tool. It generates numerically ordered SQL migration scripts that sit inside Visual Studio projects and take database schemas from one version to the next. It also, however, enables stored procedures, triggers, views, and functions to be source controlled as well, avoiding conflicts at deployment time.
So whichever IDE you prefer – SSMS or Visual Studio – and whether you like state-based or migrations-based deployments, you can version control database changes alongside application changes confidently.
And after that? Once your database is under source control, it opens the door to including it in continuous integration and automated release management as well. Making true DevOps for the database possible.
And finally …
This post is based on a presentation I gave at the Computing DevOps Summit in London on March 22, 2017. In the Q&A session afterwards, I was asked what the biggest hurdle to implementing database DevOps was.
The answer is simple: people. The right people have to talk to each other, work with each other, and collaborate with each other. As Donovan Brown, Senior DevOps Program Manager at Microsoft, puts it: “DevOps is the union of people, process, and products to enable continuous delivery of value to our end users.” Note that he puts people first.
Also in Hub
Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to...
Also in Product learning
Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for p...
Also in SQL Source Control
SQL Server 2017 went on general release a couple of weeks ago and the latest version of SQL Source Control offers full support, as well as support for Team Foundation Server (TFS) 2018.
This brings ...
Also about Database DevOps
If you were asked what the benefits of DevOps are, you could probably name two or three straight away. Maybe four or five. But – and here’s the thing – what if the person down the corridor was a...
Also about ReadyRoll
Deploying schema changes to SQL Server databases can be tricky when you’d like to automate parts of your workflow. For instance, how do you go about version controlling your schema changes? In appli...
Also about SQL Source Control
In our series of blog posts about Version Control and the Database, we've explored the history of version control, why databases have some unique requirements, and why it's important to include them...