Dealing with Databases in DevOps Scenarios

In the previous chapters, you have learned about the continuous integration and continuous deployment of your software. You also learned how the same principles can be applied to the delivery of configuration in infrastructure. Once you have adopted these principles and start increasing the flow of value delivery, you might run into another challenge: managing your database schema changes.

Applying DevOps to databases can feel like trying to change the tires on a running car. You must find some way of coordinating changes between database schema and application code without taking the system down for maintenance.

In this chapter, you will learn about different approaches for doing just that: managing these schema changes while avoiding downtime. With proper planning and a disciplined approach, this can be achieved in a way that manages...

Technical requirements

In order to practice the ideas that are laid out in this chapter, you will need to have the following tools installed:

• An application with the Entity Framework Core NuGet package installed
• Visual Studio with SQL Server Data Tools
• Access to Azure Pipelines
• An Azure subscription, for accessing Cosmos DB

Managing a database schema as code

For those of you who are familiar with working with relational databases from application code, it is very likely they have been working with an object-relational mapper (ORM). ORMs were introduced to fill the impedance mismatch between object-oriented programming languages and the relational database schema, which works with tables. Well-known examples are Entity Framework and NHibernate.

ORMs provide a layer of abstraction that allows for the storage and retrieval of objects from a database, without worrying about the underlying table structure when doing so. To perform automated mapping of objects to tables, or the other way around, ORMs often have built-in capabilities for describing a database schema, the corresponding object model, and the mappings between them in a markup language. Most of the time, neither of these have to be written...

Applying database schema changes

With the database schema, and optionally, a series of migrations defined in source control, it is time to start thinking about when to apply changes to the database schema. There are two methods to do so. Database schema changes can be applied prior to deployment of the new application version, or by the application code itself.

Upgrading as part of the release

The first approach to applying databases changes is as part of the release pipeline. When this is the case, the tool that is responsible for reading and executing the migration scripts is invoked using a step in the pipeline.

This invocation can be done using a custom script in PowerShell or another scripting language. However, this...

Going schema-less

In the previous sections, the focus was on relational databases, where strict schemas are applied to every table. A completely different approach to database schema management is to let go of having a database schema altogether. This can be done by using schema-less or document databases. A well-known example of a schema-less database is Azure Cosmos DB. These databases can store documents of different forms into the same table. Table is quoted here, since these types of databases often do not use the term "table", but call this a database, a container, or a collection.

Since these databases can store documents with a different schema in the same collection, schema changes no longer exist from a database's point of view. But of course, there will be changes to the structure of the corresponding objects in the application code over time. To see...

Other approaches and concerns

Besides the more common approaches that were discussed previously, the following tips and approaches might help with reducing the amount of work in dealing with databases, or help reduce the risk associated with making database changes.

Minimizing the influence of databases

A first step in dealing with databases can be to reduce the chance that a database change has to be made. In many databases, it is possible to write stored procedures—or some other code or script—that executes within the database engine. While stored procedures come with some benefits, changing them can also count as a database schema change, or at the least result in changes that can be difficult to test.

One...

Summary

In this chapter, you have learned how to manage your database schema and schema changes using source control. You know about both the migration-based and end state based storing of changes, and how to apply them to your production database in a safe manner.

Additionally, you have learned how schema-less databases can remove the burden of traditional schema management. However, this comes at the price of having to cope with schema differences when reading older versions of an object back from the database.

In the next chapter, you will learn about continuous testing. You will not only learn about testing techniques, but also about which to apply at what point, and how testing is a crucial part of DevOps and a critical enabler of a continuous flow of value to end users.

Questions

As we conclude, here is a list of questions for you to test your knowledge regarding this chapter's material. You will find the answers in the Assessments section of the Appendix:

1. True or false: When working with Entity Framework, schema management is built in using migrations-based support.
2. True or false: When working with a migrations-based approach for schema management, you do not need extra tracking tables in your database schema.
3. True or false: When working with an end state-based approach for schema management, you do not need extra tracking tables in your database schema.
4. What are the benefits of a full side-by-side approach to database schema changes? (Choose multiple answers):
   
   1. The risks are reduced to almost zero.
   2. You can measure the actual performance impact of changes in a production-like environment.
   3. Side-by-side migrations reduce cycle time.
5. True...

Further reading

• More information about Entity Framework and Entity Framework Migrations can be found at https://docs.microsoft.com/nl-nl/ef/ and https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/.
• More information about Redgate and its database tooling can be found at https://www.red-gate.com/.
• More information on SQL Server Data Tools can be found at https://docs.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-2017.
• The Azure Pipelines extension for deploying Entity Framework Core migrations from a DLL can be found at https://marketplace.visualstudio.com/items?itemName=bendayconsulting.build-task.