Bringing Continuous Delivery to the Database
Bringing Continuous Delivery to the Database
Rather than adding to your existing infrastructure with unfamiliar processes and enforced policies, CD for the database can be implemented alongside the systems already in place.
Join the DZone community and get the full member experience.Join For Free
Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate
Databases are often seen as the poor relative in software development. The clunky bits at the backend that store the data while the front-end applications do their magic. The grey boxes that are updated at the conclusion of the software development process and often cause problems.
It’s irritating to admit it, but also true. I’ve even heard of a Database Administrator (DBA) being presented with a pile of change scripts at the last minute and having to wade through them, line by line, while the developers fume impatiently for their latest feature to be pushed live.
It doesn’t have to be that way.
Companies and organizations are realizing that this siloed approach, where application development and database development are two separate processes, is hindering their ability to stay competitive. Instead, they need to be considered as two sides of the same coin: separate but also connected.
It’s beginning to happen, too. The 2017 State of Database DevOps report from Redgate revealed that in 75% of the 1,000 companies and organizations which took part, developers were responsible for both application and database development. Perhaps surprisingly, developers also built the database deployment scripts in 60% of respondents.
So, database development is moving from the dark silo of the DBA into the application development environment. When Visual Studio 2017 was launched, the Enterprise Edition also included, for the first time, third-party database development tools in the installer, so application developers can write database code in the IDE they’re already comfortable working with.
When it comes to Continuous Delivery, however, a slightly different picture emerges. The Database DevOps report showed that 81% of developers use version control for their application code, 39% use Continuous Integration, and 33% have automated deployments to ease the process. It also disclosed that 47% of respondents have already adopted elements of Continuous Delivery across some or all of their IT projects, and a further 33% plan to do so in the next two years, all of which looks promising. Continuous Delivery enables a process to be established that speeds up development, reduces errors, and allows new features to be released faster:
Version controlling code means one source of truth is maintained in development, preventing conflicts later on. Each time changes are committed to version control, a Continuous Integration process is triggered that builds and tests the changes, so errors are picked up immediately. Automated deployment tools then compile the code and create a release artifact that can be reviewed before it’s deployed.
This kind of workflow is increasingly being adopted, and version control systems like Team Foundation Server, Subversion, and Git are commonplace, as are Continuous Integration tools like Jenkins, TeamCity, and Bamboo.
There’s good reason, too. As the 2017 State of DevOps Report from DORA and Puppet shows, companies and organizations which embrace DevOps practices like Continuous Delivery can typically deploy changes, updates, and improvements 46 times more frequently. Their change failure rate is also 5 times lower, and they can recover from failures when they do occur 96 times faster.
But — and this is a big but — the Database DevOps report goes on to reveal that database development is lagging behind in Continuous Delivery. Here, 58% of developers use version control, and it falls to 20% and 21% for Continuous Integration and automated deployments, respectively. So, a lot of companies and organizations are missing out on the advantages of Continuous Delivery for the database.
And the advantages are there to be gained. Including the database makes it possible to move from big, infrequent, and often troublesome releases to smaller, regular releases that are virtually error-free. Importantly, if you introduce it in the right way, it can make both the application and database development process smoother:
As can be seen, rather than adding to your existing infrastructure with unfamiliar processes and enforced policies, Continuous Delivery for the database can be implemented alongside the systems already in place.
That said, what are the steps you need to take, and what do you need to watch out for in order to introduce Continuous Delivery to database development?
Include Your Database in Version Control
42% of companies out there still aren’t version controlling their database. Some probably rely on a backup of the production database when they need the latest version of the database, which is cumbersome at best. Others may have a folder of handwritten change scripts which they present to the DBA at deployment time (remember the poor DBA I mentioned earlier).
With the database in version control, it’s a different story. The development team can reconstruct any version of the database, and migrate from one version to another, from the source-controlled database object scripts. That’s a big plus-point because changes can be reverted at any time, and errors can be traced back to specific changes.
Remember that everything required to recreate a working version of the database should be in version control. That’s the database creation scripts, configuration scripts, schema objects like tables and stored procedures, reference data, and migration scripts.
There are many proven database version control tools available, but it’s advisable to choose one which integrates with and works alongside the infrastructure you already have in place. Introducing database version control will mean developers have to change the way they work. Limiting that change and letting them work with the tools they’re already accustomed to and like will make it easier to implement.
Include Your Database in Continuous Integration
In application development, it’s becoming increasingly common for developers to commit changes to version control, at which point a Continuous Integration server will build it and run automated unit tests to see if anything is broken. Developers see immediately if there are any errors and, because they’ve just been working on the code and know what changed, it’s easier to fix.
With the database in version control, you’re able to include it in the build process as well. When changes are committed to version control, the build process can create a new database, create its objects, load any reference data, and check that the SQL syntax is valid and dependencies are in order. This not only highlights problems earlier, it also stops breaking database changes from ever reaching production where the impact can cause far bigger problems.
There are, again, mature development tools available to introduce Continuous Integration for the database that plugs into the systems used in application development. This makes it easier to introduce and implement and, importantly, lets you run automated tests across both the application and database, ensuring the tests are working against the latest version of each.
Include Your Database in Release Management
Release management tools like TeamCity, Visual Studio Team Services, and Octopus Deploy are becoming more and more popular in application development because they automate even the most complicated deployments.
There are similar tools available for the database which automatically generate deployment scripts to update the database, and include static data and migration scripts that specify how to deploy complex database changes, such as table splits.
Choose one that works with your existing build and release management tools and you’ll complete your Continuous Delivery journey by making deployments repeatable and reliable rather than error-prone and uncertain.
Opinions expressed by DZone contributors are their own.