DevOps for Databases -The Beginning
Traditionally, changes to a database begin with the developers building the changes they will use to write the files in an SQL format. The changes made are then reviewed by a database administrator (DBA). This sounds like a solid, crucial process, right? However, the main issue is that the database administrator usually gets introduced just before deploying to production when it’s too late, and it turns out to be costly when making the proper changes. This is a typical scenario faced by big companies. So, it is simply shifting this mechanism to the left, and automation makes the process run more evenly. However, DevOps for databases is not just about the automation process.
Automation is a Part of the Equation
There may be occasions where some tasks are so complex that automation may not be enough. But assuming you have already defined how the database will be used and are not re-architecting, I doubt you will surely need to make some complex changes very often. Automation helps you implement future changes in a predictable and repeatable way, so long as they are not always distinctive. Automating the variation of a table to add some new columns is not that difficult. The real problem with databases is that we must care for the state. If the database contains a lot of data, making a certain type of change may take time and block upcoming changes like inserts, updates, and deletes.
Why DevOps for Databases?
DevOps is just not about cracking the silo among developers and operations. That is why all the manual operations in the delivery pipeline must be evaluated to determine if it can be automated. Database changes are a tedious process; therefore, it deserves to be expressed in the DevOps implementation. Developers generally wait for database changes (done by DBA’s) to be complete to continue with your work. DevOps practices lead to shorter iterations as well as faster releases. In bringing DevOps to the database, we can faster your development process.
A process in which, Modern software engineering Culture and Practices to develop software where the development and operation teams work hand in hand. Click to explore about, What is DevOps and it's Processes?
What are the best Database Automation Tools?
These tools can be used to build and validate database scripts, run tests and sync a database with a source-control version. These tools can be configured to publish a database package to the repository for deployment. Some of the most popular tools that have been used and used before are mentioned below.
- Jenkins
- TeamCity
- Liquibase (free)
- Datical (a paid version of Liquibase)
- Redgate (Microsoft Stack)
- Delphix (not just for database changes)
- DBmaestro (they sell as DevOps for databases)
What are the principles of Database DevOps?
Database DevOps utilizes the same principles that had already been extensively accepted in application development -
- Source Control
- Unit Testing
- Repeatable Deployments
- Continuous Delivery
Source Control
Storing all the database code, from the initial schema creation of scripts to each of the iterative modifications, allows for a well-known database state in a certain environment at an assertive point in time. This allows many database developers and database administrators to be courageous in the state of play about what is happening in the database environment.
Unit Testing
All the changes that were being created and deployed need tests to check whether they meet the requirements and whether they break or cause problems in the deployed environment. Tests should not reconsider a change but should run the changes based on particular requirements.
Repeatable Deployments
The practice of deploying small changes repeatedly using the same process means that a similar outcome can be accomplished reliably and confidently.
Continuous Delivery
With the database DevOps, the process of applying a change to various environments, utilizing unit tests, and then deploying it to production is broadly automated. Largely, with the process in place, ‘major fixes’ are no longer made to the Production environment, avoiding all unexpected issues or problems in the future.
A methodology or an operating model that establish an Agile relationship between growth and IT operations. Click to explore about, DevOps on Google Cloud Platform Benefits and Tools
How to Source Control Databases for DevOps?
- The database schema, including indexes, should be in source control.
- Data that controls business logic, like lookup tables, should also be in source control.
- Developers need a way out to create local databases easily.
- The shared database needs to be updated through a build server only.
- Tables or Collections
- Constraints
- Indexes
- Views
- Stored Procedures, Functions, and triggers
- Database configuration
As discussed above, we use a schemaless database but it doesn’t mean there is no need for source control. We still require an account for databases and overall database configuration settings. There are two types of source control for databases:
1. Whole-schema
2. Change script
Whole-Schema Source Control
It is where the database in the source code looks the way we prefer it to be. When this pattern is used, all the views and tables get sorted most favourably, making it easier to understand the database without requiring deployment. SQL Server Data Tools (SSDT) is an example of whole-schema source control for SQL Server. In this tool, all database objects are declared in terms of CREATE scripts. Another example under whole-schema source control is Entity Framework Migrations. This tool represents the database by C#/VB classes. While working with whole-schema source control, migration scripts are not written directly. The deployment tools sort out what kind of changes are required for you by analyzing the current state of the database alongside the idealized version in the source control.
All this allows rapid changes to the databases and thus helps see the results. Sometimes, the tooling is not enough, even besides pre- and post-deployment scripts. In these cases, the generated migration script must be hand-modified by the database administrator or developer, which may break the continuous deployment scheme. This generally happens when there are considerable changes to a table’s structure, as generated migration scripts may be inefficient in the above-discussed cases. Another advantage is it supports code analysis. For example, if we change the column name but forget to edit it in a view, SSDT will return a compile error. This catches a lot of errors and prevents you from pushing to deploy the broken scripts.
Change Script Source Control
The second option is “change script” source control. Rather than storing the database objects, we store a list of steps needed to create database objects. Liquibase is one of the examples of the change script source tool. The main advantage of its tool is that we have full control over how the final script file is going to look. This makes it far easier to make complex changes when tables are combined or split. Unfortunately, there are many disadvantages as well. First is the need to write change scripts. While giving more control, it turns out to be very time-consuming as well. The other is change script tools tend to be very blurred. For example, if we want to check the columns on a table without even deploying it, we must read all change scripts that touch the table. Because of this, we will surely miss something.
A sort of database service which is used to build, deployed and delivered through cloud platforms. Click to explore about, Cloud Native Databases on Docker and Kubernetes
What are the Challenges of DevOps Database?
The below mentioned are the challenges of DevOps Database:
Tightly-Coupled Architecture
We often face problems with databases because of the tightly coupled architecture. This issue can be eliminated with the help of Microservices. These services interact with one another through well-defined interfaces instead of directly connecting with the database.
Lack of Culture and Well-Established Processes
Another important aspect of database DevOps is the variation required in culture and processes. As discussed, leaving the audit process at the end of the workflow is surely a sign of poor communication among teams.
Persisting Data During Upgrades
It is easy to change the application code, but the data must continue along with the database. Like the application code, we cannot easily drop and replace the database.
Loading Static Data and Test Data
While the database is constructed to accumulate customer and transaction data, it will not function until and unless particular tables are “pre-stocked” along with several immutable data that are required for the dependent applications to function.
Database Drift
Delivery time for important new functionalities is slow. Secondly, when we are about to hit a production issue, it won’t be possible to go back to the source code, fix it, test it, and then redeploy. These drifts cause deviations that undermine tests and may cause failed deployments, either because of code clashes or the accidental rolling back of important fixes.
A Holistic Strategy to DevOps for Databases
As teams proceed to enhance the throughput and speed of application changes, the database is frequently the critical bottleneck. As long as database changes remain a manual process, no increase in DBA headcount can scale the manual method to keep up with application updates. To drop the database bottleneck, it’s essential for teams to adopt true database automation and treat database code just like application code.
All the DevOps processes might sound complicated when you start thinking about database DevOps. However, the journey could be smooth with collaboration between developers and DBAs and choosing the right tools and strategies. Are you looking for more assistance?
To stimulate connected improvement in your DevOps Data strategy as you evolve it for each technology and business initiative, we recommend taking the following steps:
- Read and explore Graph Database in Big Data Analytics
- Take a DevOps Strategy and Advisory Services to determine your Analytics IQ
- Learn more about XenonStack DevOps Consulting Services and Solutions or Contact us about DevOps strategy and advisory services, including review of your current efforts.
