DevOps for Databases -The Beginning
Traditionally, changes to database begin with the developers building the changes that they will use to write the files in a SQL format. The changes that had been made are then reviewed by a database administrator (DBA). This sounds like a solid, crucial process, right? But the main issue is that the database administrator usually gets introduced just before deploying to production when it’s already too late as well as it turns out to be really costly while making the proper changes. This is a typical scenario faced by big companies. So, it is simply shifting this mechanism to left, and automation makes the process run more evenly. But DevOps for databases is not just about the automation process.
Automation is a Part of Equation
There may be some occasions where some tasks are so complex that the automation may not be enough. But assuming you have already defined how the database is going to be used and you are not re-architecting, I doubt you will surely need to make some complex changes very often. Automation helps you in implementing future changes in a predictable and repeatable way, so long as they are not distinctive every time. To be honest, automating the variation of a table to add some new column is not that difficult. The real problem in databases is we need to take care of the state. If the database contains a lot of data, making a certain type of change may take a lot of time and block the upcoming changes like inserts, updates, deletes.
Why DevOps for Databases?
DevOps is just not about cracking the silo among developers and operations. That is why all the manual operation in the delivery pipeline demands to be evaluated to figure out if it can be automated. Database changes are certainly a tedious process and therefore it deserves to be expressed in the DevOps implementation. Developers generally wait for database changes (done by DBA’s) to be complete so that they can 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, validate database scripts, run the tests and sync a database with 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 are being 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 actually 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
The storing of all the database code, from initial schema creation of scripts to each of the iterative modification, allows for a well-known state of a database in a certain environment at an assertive point in time. This provides 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 the changes that had been made whether to meet the requirements or not and does it break or cause problems to deployed environment. Tests should not be a reconsideration of a change and should run the changes that were based on particular requirements.
Repeatable Deployments
The practice of being able to deploy small changes with the help of the same process, again and again, means that the similar outcome can be accomplished reliably and confidently.
Continuous Delivery
The process of being able to apply a change over the various environments, utilizing unit tests and crowning in being deployed to the production is, with the database DevOps, broadly automated. Largely, with the process in the place, ‘major fixes’ are no longer going to be made to the Production environment, avoiding all the 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 which controls business logic like lookup tables should also be there in source control.
- Developers need a way out to easily create local databases.
- The shared database need to be updated through a build server only.
- Tables or Collections
- Constraints
- Indexes
- Views
- Stored Procedures, Functions, and triggers
- Database configuration
Whole-Schema Source Control
It is where the database in the source code looks like the way we prefer it to be. When this pattern is being used, all the views and tables get sorted in the most favourable way, which makes it easier to understand the database without requiring to deploy it. For SQL Server, SQL Server Data Tools (SSDT) is an example of whole-schema source control. In this tool, all of the database objects are declared in terms of CREATE scripts. Another example under whole-schema source control is Entity Framework Migrations. In this tool, the database is represented 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 in seeing the results. Sometimes the tooling is not enough, even besides pre- and post-deployment scripts. In these cases, the migration scripted that has been generated needs to be hand-modified by the database administrator or database developer, which may break the continuous deployment scheme. This generally happens when there considerable changes to a table’s structure, as generated migration scripts may be inefficient in 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 compile error. This catches a lot of errors and prevents you from pushing to deploy the clearly 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 like. 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 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, it's necessary to read all change scripts that touch 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
Mostly, we always face problem in the databases because of the tightly-coupled architecture. This issue can be eliminated with the help of the Microservices. These services interact among 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 before, leaving the audit process at end of workflow is surely a sign of very poor communication among teams.
Persisting Data During Upgrades
It is easy to change the application code but along with a database the data must continue. Like in the application code, we cannot drop and replace the database easily.
Loading Static Data and Test Data
While the database is constructed to accumulate customer and transaction data, it is not going to function until and unless particular tables are “pre-stocked” along with several immutable data which are required for the dependent applications to function.
Database Drift
Delivery time for any important new functionalities are 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 deviation that undermines tests and may cause failed deployments, either because of code clashes or may be due to 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. It’s essential for teams to adopt true database automation and to treat database code just like application code in order to drop the database bottleneck.
All the DevOps processes might sound complicated when you first start thinking about database Devops. But the journey could be smooth with collaboration between developers and DBAs, choosing the right tools and strategies. Are you looking out 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 about 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
