While the main focus of DevOps efforts has been placed on application-side development, the database plays an important role in ensuring smooth sailing.
It is crucial to have a well-configured, fully-tested, and operable database in order to deliver an appropriate end-user experience with the developed system.
Mistakes and Database Design Best Practices: Some Context
It’s crucial to follow database design best practices from the set up phase throughout to avoid costly, serious mistakes. Most of the issues and database bottlenecks related to the database can be foreseen during development, and these must be dealt with before actual delivery or putting the system into production.
Some problems, however, are only seen when the system is deployed with an actual user base, and this can result in costly downtime that can last from minutes to weeks in some worst-case scenarios. Here we will scratch the surface of some of the most common concerns faced when a database is put into production and how to resolve them for releases that are both fast and efficient.
1. Inconsistencies Between Environments
One of the most common issues that is often difficult to detect is misconfiguration, or having two environments which are not configured in the same way or operating the same version. Having a development environment and a production environment where the databases don’t share the same configuration can cause serious headaches.
A development environment evolves fast. It is the first environment to be set up when a new project is started, and sees constant and frequent changes at the hands of the developers. While all new changes and updates should technically occur on the development environment and be pushed forward, this isn’t always the case; oftentimes, an undocumented emergency hotfix is put into place directly in the production environment (and not updated to lower environments), causing them to be inconsistent.
These changes in the production environment are often mistakenly overridden when new changes are pushed upwards from development, resurrecting the awful issue that was tackled in the first place. Configuration drift, as it’s called, can cause a lot of confusion and panic as the cause of the error is likely undocumented and forgotten, rolling back the new version can be tricky (if not impossible), and panic ensues.
Solution: The best way to deal with configuration drift is to prevent it from happening in the first place. When attempting to deploy code from one environment to another, a fail-proof safety net can automatically detect configuration drift, determine which code is safe to deploy, protect the target environment when necessary, and identify when changes must be merged (and when human intervention may be needed) to ensure a safe and speedy deployment. Database design best practices call for drift prevention in the design stage, not as an after-thought when it’s too late.
Want to know more? Check out our webinar recap on drift security.
2. Lack of Communication Between DBAs & Developers
Good communication is key to any healthy relationship, and it is no different in the development world. A recent survey shows that around 60% of organizations have not made the DBA part of the DevOps team; this siloed system can cause serious errors when important policies, standard changes, or maintenance changes aren’t openly discussed and planned. When all team members are made aware that a certain policy on the database is altered (e.g. a revoked drop table grant or the disabling of some deprecated extension), significant issues can largely be avoided.
Figure 2: Integration of DBAs with DevOps Teams, DBmaestro Database DevOps Survey 2018
If DB administrators are the only ones who are allowed to make changes on the databases (which is true 59% of the time, according to the survey), these changes need to be communicated to all other teams. If a DBA maintains multiple databases and projects, this can create delays in delivery but can also significantly reduce errors.
Another key issue with poor communication is that it can cause a lack of performance and a weaker end-user experience of the system being developed. Database administrators will understandably have more knowledge and experience on a database that they maintain, and sharing this knowledge with developers can significantly improve system performance.
Solution: As Pramod Sadalage points out, in order for communication to be improved, the DBA has to be available, easily approachable, and open to interaction. This can happen via a coffee break, casual in-office chat, or online communication medium such as Skype or Slack—whatever makes it easiest for developers to drop a question and get the feedback they need. Organizing daily stand-up meetings and brainstorming sessions between DBAs and developers to discuss any possible bottlenecks can aid in overcoming concerns faster if they do arise as well.
3. Tuning on Dev?
DEV and TEST environments are just starting points in the development process. And although some major issues can be detected during this testing phase, the real indicator of how an application is going to actually perform comes from putting the application into the production environment. An increased number of requests, slow queries due to incorrect indexing, over-indexing, storage, availability, and data consistency are just some of the issues that pop up in the PROD environment that are not easily spotted in DEV or TEST environments.
Some examples of tuning that takes place in the DEV environment include adding indexes on columns, tweaking query buffers, and not having such changes replicated in the production environment.
Solution: TEST environment should be configured and tuned to represent the production system, allowing the QA team to reproduce high load or other conditions that might occur in the real production system. By having this additional layer of testing, we can avoid issues caused by only tuning in development prior to deployment.
4. Disregarding Other Apps
In a perfect world with unlimited resources, we could afford to have the best hardware for both our application and database. In such a case, we wouldn’t have any reason to think about sharing resources with other apps and could abuse hardware as much as we pleased. Unfortunately, since all projects have some resource restrictions, hardware and software may need to be shared with other teams. It wouldn’t be fair if one app is hogging all the CPU to run some aggregation queries while locking up certain tables and preventing other apps—as well as the database itself—from performing normally.
In most cases, a database is going to run inside some kind of virtualized environment where most of the resources are shared among other VMs. Whichever virtualization method you use to run your database on AWS in your own cloud, you are going to add additional overhead, and VMs running on the same server also need to be taken into account.
In some cases, a database is being run on the same (virtual) machine as the app itself, which is pretty common for small projects. As a database grows, queries and incorrectly indexed data add additional load, which will affect both database and app performance since they are running on the same hardware.
Solution: Plan for this in advance. Be wary of the resources you’ll need in regards to the rest of the team in the planning stage, meet to map out the predicted needs in terms of resources, hardware and software.
5. Poor App Development Design
It isn’t possible to take every possible scenario into consideration during the design of an application; there are always some unforeseen issues that are detected during release or when the system is put under pressure. Therefore, it should at least be possible to predict and plan for some of the basic requirements when it comes to database delivery, such as for security, logging, scaling, auditing, and QA-mode options.
App design should always take into consideration security policies that may change and behave accordingly. One example is when an application only has “read/write” privileges and not the “create or drop” privileges; the design of the app should respect this. Also, designers should take into account that logging and auditing may be required. If it is expected for an application to be scalable, that should also be taken into consideration from the very beginning.
Solution: Configuration should allow for new instances to be added as slaves, or, if you have a NoSQL database, support for adding new nodes into the database. Besides configuration, database schema should support scaling of the system as well. Plan for security needs in advance, making sure all teams are aligned.
6. Reviews and Checks Way Too Late in the Game
Having a code review process is tremendously important for database delivery, and defining and following proper guidelines can significantly reduce common issues in later stages. Some of these issues include migrations that are not correctly applied on the database, incorrect recommendations for the database engines, database configurations that are not performed correctly, etc.
Solution: A review process, for everything from the ER diagrams created by developers to queries being executed, should be put into place from the moment a project is started. This process helps identify possible conflicts that can occur between developers as well as with the DB configuration conducted by the database administrators. On top of this, DB admins can also help recognize when standards are not being followed and warn developers of possible consequences.
Small and frequent releases can help you identify potential issues that can occur in production. The reason for this is that you receive feedback much faster, making any required changes to the codebase smaller, which in turn makes it is easier to spot any areas of concern.
7. Manual Reviews
Manual code inspections can be tedious and are prone to human error. When you need to be agile and support the team in frequent releases, it is far easier to always perform all checks required automatically. There is also the time component, which can have a significant impact on the project itself.
Automating reviews can save a lot of time and hassle. Some tasks worth automating include checks for data integrity, accuracy of business logic, mirroring, security, and regression testing. Data-integrity checks can help detect whether data that is updated with new requirements is keeping the database in a consistent state. Business checks can help validate if the implemented business logic is correctly reflected on the database level, etc. If a database administrator always has to perform these and other checks manually, there can be a significant increase in operational costs.
Solution: Automating these tasks is possible with many tools available or via custom implementation. According to the 2018 Database DevOps Survey, database administrators spend most of their time performing security checks, making database changes, and optimizing a system. Automation can reduce the time required for all of these functions, and admins will not have to manually re-evaluate changes they’ve made.
Always Be Prepared
As with many things in life, proper preparation and planning can help prevent the most significant database mistakes. Make sure you’re using the best tools and platforms out there to ensure as smooth a ride as possible, from development to production.
Enjoyed this article? You might want to read next what are the best practices for database management.
