Not extending your CI/CD pipelines to the databases can lead to a wide range of technical issues and delivery bottlenecks that eventually affect the quality and time-to-market. If you are ready to bridge the gap between your DevOps processes and the database, follow these 11 best practices for the best results.
Detect and Prevent Configuration Drifts in Advance
DevOps is all about frequent and iterative updates, but when it comes to databases, the process is still dependent on manual workflows, resulting in configuration drifts and errors.
Configuration drifts are a common cause for release-related issues when it comes to databases. These drifts can present themselves as new schema configurations due to out-of-process changes, conflicting changes from different teams, or production hotfixes altered by SQL scripts. These configuration drifts are a recipe for disaster.
Bringing the database into the CI/CD loop can help developers avoid those problems.
By verifying all code updates in the database before the release via a centralized dashboard where every change is tracked, documented, and monitored, you can detect and react to all database configuration and application code conflicts as they happen. Before the release.
When it comes to applying company policies and standards in the application code, developers have plenty of tools to enforce those standards. But with databases, developers often work in the dark, running into issues only after the release has been pushed to production. At the same time, DBAs rely on manual workflows to integrate latest changes into the database. As a result, the database becomes the bottleneck that significantly slows down your releases.
It is a no-brainer that database release and delivery must also be automated to comply with policies and standards. Just like with application code, this involves blacklisting of specific database activities, while determining desired code naming conventions and acceptable times for deployments to production.
Once you have automated compliance with company policies and standards in the database, you can quickly verify all release packages that are in your release pipeline and kick out all bad code before it creates downtime, bottlenecks, and other issues.
Besides being extremely time and resource consuming, traditional tracking and monitoring methods are proving to be ineffective in managing database version releases and changes. What we need today is a release automation process that involves the database, giving you the ability to move fast.
Enforce Database Change Policy, Inside the Database
Hoping that there will be no human errors in your DevOps pipeline when the database is involved is a gamble you will always lose, especially in today’s dynamic coding environment. The only way to get the job done is by creating and enforcing a robust database change policy for all teams involved.
Once your policies have been established, use an automated solution to validate all code changes that include your database.
Keep a Detailed History of all Changes Made
Keeping track of versions and changes and having a detailed history of all database changes that have been made by all stakeholders at any given time is invaluable.
Having an audit trail of all changes made to the database is another pain point that many DBAs have to waste countless hours of manual work to achieve.
Automatically documenting all actions that have been made is a key to proper compliance in the database. When the history of each and every code change is automatically documented and can be accessed with just a few clicks, your job becomes that much easier.
Minimize Errors and Time-Consuming Manual Fixes
To minimize errors, dry-run the code before release in order to detect errors before they happen.
Check for inconsistencies. You’ll want to make sure the release will be successful in real-life scenarios before you push your code to production. The key is to recognize potential drifts and errors and configuration drifts before they happen, not after.
Make sure to perform Integration tests. A developer might have developed a great change that ticks all the boxes, but it might create conflict with some other code in the system which you won’t know until release. That is why integration testing, and dry-running your integrations is critical.
|ING Bank Slaski had been encountering database deployment errors on a nightly basis, until DBmaestro entered the picture .|
|“DBmaestro was a clear winner for our needs. The version control inside the database was what our team required for the smooth deployment of database packages and substantial decrease of nightly interventions.”
Mariusz Narewski, Head of Management and Reporting Apps Team
Define Who Can do What. And Where
Roles and permissions management for databases is a must. Least privilege policy mindset is key when it comes to end-to-end compliance, and leaving the database out of this practice is simply dangerous.
If you write an unsecure code that involves your database you need to get an alert for that. Maybe actions hackable in run time or you grant permissions that are too wide or too deep – leaving potential for exploitation.
Just like with code, a solution that can monitor insecure actions in the database and provide alerts in real-time is key.
In short, having a centralized roles and permissions management solution with customizable activity alerts and audit trail significantly simplifies the database compliance.
Customize Database Activity Policies
Different organizations will have different role management requirements, but from a security perspective, you want to limit the access to the database as much as possible without making the process difficult.
Your developers might not need to deploy to production. Define who can deploy database changes and go as granular as possible. Set permissions for specific changes to the database code and control access to the database.
Comply with Federal and International Regulations
This one is no brainer, but often easier said than done. General Data Protection Regulation (GDPR), Health Insurance Portability and Accountability Act (HIPAA), and Sarbanes-Oxley Act (SOX) are just a few regulations that have become mandatory in recent years. Achieving these by managing database releases manually has become almost impossible.
Regardless of the exact sector your company belongs to, you will need to ensure that regulations are being met at all times.
Keep a Complete Audit History
Lack of a complete audit history can become a huge roadblock when it comes to establishing compliance, or troubleshooting.
With multiple developers working on projects and thousands of changes to the code before every release, creating any sort of documentation ahead of your audit is a nightmare.
Manual audit management is not effective. Creating a clear audit trail is best automated. Not only are all database changes automatically documented (exportable for offline scrutiny), but all access patterns and identities are fully transparent.
Monitoring is a must to track KPIs that you can act on – how many releases, what they achieve – so that you can improve your processes.
Measure your KPIs so that you can improve your metrics
Everything that you can measure you can improve. If you don’t know what is happening you can’t improve on it.
You don’t know what you don’t know. When you have a process that is running and you are not measuring it – you can’t see the bigger picture. You might miss repeatable errors, for example one team can run more errors, maybe you have delays at certain points.
There is no magic bullet to maximizing your release pipelines. But measuring is the first step towards improving your process. So as a first step, you need to determine the right performance KPIs for your specific environment. These metrics have to be established early on.
We all use multiple tools in our CI/CD toolchains. From Jenkins to Jira and all the way to multiple databases – Oracle, MySQL, RDS, to mention just a few. The key is to ensure that all these tools work nicely with each other.
Always consider integration pains when choosing your tools
The database needs to be treated as an integral part of your DevOps release pipeline. Unfortunately, it is still often left out in the cold and worked upon in silos, creating a bottleneck that routinely slows down releases.
Make sure you are automating your database with a solution that is flexible and compatible with the tools you already use. Fragmentation leads to more work. More work leads to more coding. More code requires more maintenance (and updating). A vicious cycle you must avoid.
Continuous Integration and Continuous Delivery (CI/CD) for databases is no longer optional. Just like application release automation before it, the database needs to be integrated into the overall DevOps process. Siloed approach might have worked 5 years ago. But it is no longer sustainable.
By bridging the gap between the database and your code you can achieve faster release cycles, which is at the core of DevOps, without sacrificing code quality and without increasing the stress on your testing/QA teams. Simply put, you will be able to do more with less, while also achieving elevated (and sustainable) compliance and security standards.
Then there is ROI. if you have a 30-member team, you can enjoy a productivity gain of 15% along with savings of around 2000 team member hours a year ( 130,000 USD). Check out our DevOps for Database ROI Framework infographic to learn more about the monetary benefits of automating your database.
Automating your database release and delivery is no longer a luxury. It is a best practice.