The most used term for someone who is not a Database Administrator (DBA) but is tasked with looking after one or more database servers is “accidental DBA.” It can be a hard and thankless task, which is sometimes assigned to someone who has little to no experience managing a database server. This is in addition to other tasks that they regularly perform.
Administering any system can be difficult without the proper training. This issue is magnified if staff are doing so without the specific expertise and in addition to other competing priorities. Unfortunately, this arrangement often leads to missing multiple small, but relevant, issues. The challenge is that often, by the time issues are discovered, the small issues have grown into more substantial problems. Without proper training and experience, the results can be devastating. These now large problems will hinder access to data that your business relies on. It can, and will, lead to massive implications for your organization’s revenue.
The point of this blog post is not to scare anyone into thinking that they must hire a DBA, it is simply designed to ensure that the core responsibilities are being covered and executed on well by an individual or multiple people. These individuals might be accidental DBAs, a team of people with these responsibilities split between them, a remote DBA service, or even a consultant on retainer.
The 5 challenges covered in this blog post are in no specific order and are areas that DBAs across the globe will work on regularly. I hope you will read through the points raised carefully, keeping your organization and its processes in mind as you do. Do they raise any concerns you will need extra help and guidance with to ensure they do not affect your business?
1. Business Continuity
Without a DBA, there is no doubt that your data is at risk, not only from a security perspective but also from a disaster recovery standpoint. One of the main roles of a DBA is to be the guardian of the data. This means making sure that the data is highly available and accessible to people and applications that require access while excluding those that do not.
A seasoned DBA will be able to discuss Business Continuity requirements with relevant business stakeholders. They will then assess the Recovery Time Objectives (RTOs) and Recovery Point Objectives (RPOs), create a restore plan (not a backup plan) that meets these objectives, and design and test the High Availability features that meet the requirements while also taking into consideration technical debt from licensing and legacy applications.
Key business stakeholders for each application will stipulate the number of 9s the application must be measured against for availability. Organizations generally stipulate between two and five 9s of availability for most applications. The more 9s that are required, the less downtime is allowed and the more expensive the solution becomes.
For those who are not aware of this nomenclature, the 9s represent the percentage of uptime that must be achieved. Usually, this is over the course of the year; however, companies will also stipulate that this service-level agreement (SLA) or KPI is required only during business hours for applications that are not required to run 24/7.
Uptime in 9s
- 99% allows for 3 days, 15 hours, 39 minutes, and 29 seconds of downtime per year
- 9% allows for 8 hours, 45 minutes, and 56 seconds of downtime per year
- 99% allows for 52 minutes and 35 seconds of downtime per year
- 999% allows for just 5 minutes and 15 seconds of downtime per year
Business Continuity is a vast topic, which you can learn more about by viewing the on-demand webinar, “10 Steps to Unbreakable Business Continuity.”
2. Security
It seems that a news cycle does not go by without hearing about another security breach. A recent data breach study sponsored by IBM highlights just how much of a problem these breaches are across the globe:
- The reported global average total cost of a data breach was USD 3.92M
- The average size of a data breach was 25,575 records
- The time to identify and contain a breach was 279 days
- The country with the highest average cost was the United States
- The highest industry average cost belonged to the healthcare sector
Security is a team effort. A high percentage of breaches come through attack vectors such as spear phishing and social engineering, which can be mitigated through continuous security training. However, elements such as SQL injection are still prevalent and completely preventable when team members have the right knowledge and implement the appropriate testing strategies.
A knowledgeable DBA is going to be your last line of defense and will implement many security strategies, including but not limited to the following:
- Implementing a policy of least privilege
- Reducing surface area attacks by installing only what is necessary
- Creating patching windows to update machines to the latest security patches
- Ensuring the correct level of encryption is enabled to protect sensitive data
Data breaches are not always external in nature. Sometimes, issues with new features can mean that certain security protocols are regressed, and customers can access other users’ accounts. This scenario happened in the UK, where customers using a healthcare mobile app found that they had access to recordings of other patients’ consultations.
3. Database Maintenance
When referring to database maintenance, one is usually talking about ensuring index maintenance, statistics updates, and database consistency checks are taking place. This responsibility is the convergence of numerous adjacent tasks. If you picture an untitled Venn diagram in your mind, database maintenance would be the overlapping portion. It is essential that staff perform database maintenance regularly and in a stringent manner.
Due to my self-imposed limit of 5 responsibilities covered in this blog post (I was never, ever going to be able to cover all database management responsibilities in one blog post), I will also include regular checks and support tickets as part of the database maintenance responsibility.
Index maintenance is the process of ensuring that indexes are not heavily fragmented, as fragmentation can lead to degraded performance. True data professionals will not stop there; they will ensure that performance is not being sandbagged by continually having to maintain unused indexes and that any indexes are being used efficiently by performing “index seeks” rather than “index scans” wherever possible.
Updating statistics is critical for the RDBMS’s optimizer to work out how to take the logical request coming from the user, application, or service and change it into a physical way—or plan—to retrieve the required information. Up-to-date statistics are an essential part of this process. If statistics are stale and out of date, it will lead to the optimizer making a poor decision and implementing the wrong recommendations to retrieve or modify the data. This can cause poor performance and concurrency issues.
Running database consistency checks regularly is important and often neglected. It might not be possible for these checks to run overnight on very large databases, so the process will need to be broken down. These checks will look at the data stored in the database and look for consistency errors that might have been created by faulty memory or IO controllers. If these checks are not done, you might find that when you need to restore data, such as in the event of a disaster, you are unable to bring applications back online because your backups also contain corrupt data.
Daily, weekly, and monthly checks can and will cover all kinds of tasks, some of which will be technology related and some of which will be business related. Tasks will include, but will not be limited to, the following:
- Checking for job failures
- Capacity planning
- Reviewing and acting upon alerts from SQL Servers, your monitoring solution(s), and various error logs
4. Auditing and Documentation
In recent years, data governance has become another task that has fallen into the lap of the DBA when there is not a separate data steward role. The DBA needs to be able to prove to auditors that the data is secure and show how it is handled and by whom. Fines for not being able to prove this to a sufficient standard are becoming more well-known throughout the industry, with mainstream guidelines for data handling protocols such as HIPAA and General Data Protection Regulation (GDPR) being among the most notable data protection regulations. The cost of such fines would be so prohibitive that it would force many businesses to close their doors.
Business-savvy DBAs proactively use documentation and data lineage to prove to auditors that their company has followed the appropriate guidelines. They can also use this information in several other scenarios to improve overall productivity and/or performance.
For example, if the organization in question did not document their Business Continuity plan and the disaster affected the person who designed the Business Continuity plan, would the business be able to implement it without them?
Professionals use documentation to ensure that standards are adhered to. It is a practice that deserves more prominence, as you will soon discover. In some RDBMS platforms, developers who write the same logical code but with different formatting such as double spaces or extra carriage return line feeds will create code that results in multiple different plans for the same logical code. When replicated enough times, this code consumes significant memory consumption that should be used to house data more efficiently for your end users.
Documentation can also be used to provide information to developers about how they should join various data objects together to ensure that there are no transactionally inconsistent data problems by inserting, updating, and deleting data based on wrong table or view joins.
Documentation does not necessarily need to be static. Being able to see the difference between snapshots of automated documentation will drastically help data professionals reduce the meantime to resolution by identifying changes that have happened at the server, database, and database object level.
Likewise, data lineage will not only allow auditors to see if there is a problem but can also be used by developers to stop breaking changes from happening. This is achieved by seeing the dependencies between objects and ensuring that all objects that would be affected by a new project are appropriately updated. A DBA can use the same methodology to catch issues before they are implemented if he or she is deploying the changes.
5. Performance Tuning
In the current climate, the traditional role of the DBA is evolving. Now more than ever, DBAs are being asked to investigate performance issues, especially when an application has moved from on-premises to a cloud service, where consumption costs money. The ability to find and tune queries earns them the respect of not only users, but also their CFO, who must sign off on the monthly payments. Never has there been a clearer correlation between poor performance and profitability.
Performance tuning is not just about finding rogue queries; it is a lot more complicated than that. A data professional must start from the ground up to ensure that the hardware or cloud service can cope with the peak demand of the application(s) involved. To this end, they will speak to business stakeholders to understand the requirements and then provision the hardware or service with the right levels of capacity, both in terms of storage and performance. The next steps will be to perform tests, such as TPC and SOAK tests.
Once the correct server or service configuration has been assessed and accepted, the next layer that a data professional must ensure is correct is the database configuration. Misconfiguration here can have a substantial impact on the transactional throughput of the application.
Once the data professional has configured the database correctly, they will utilize baselines to identify what “normal” looks like. Once they have defined what constitutes normal behavior, it is easier to spot deviations. This understanding makes problem resolution much faster.
Most professionals will set up alerts to be more proactive than reactive (i.e., waiting for the phone to ring to inform them of an issue). Being proactive is key to managing a large data estate.
A skilled data professional will be able to find the inflight queries that are causing resource consumption issues. They will be able to rewrite the offending query or suggest a better indexing strategy to resolve the issue. This is typically done by reading the execution plan—the way the RDBMS plans to retrieve or modify the requested data. It is much harder to ascertain and fix the problem if you are not storing a history of these metrics and queries to analyze after the time they occurred.
What Actions Should You Take to Avoid These Challenges?
Have any of the topics covered in this blog post raised concerns that you will need extra help and guidance with to ensure they do not affect your business? What actions will you take to mitigate these concerns?
Although it is certainly possible to disregard the responsibilities that a DBA performs with statements such as “Our company is too small to warrant such an investment in staffing” or “We have come this far without one,” this perspective will not defend your business against the fallout from data loss due to a disaster or the resulting fine from a security breach.
Rather than thinking of the expense of the DBA role, think of it as an insurance policy against more radical expenses.
How does an organization take out such a policy?
- Hire one or more experienced DBAs to manage your database environment.
- Repurpose an individual or a group of individuals and either convert them into full-time DBAs or include the DBA duties in their current workload. Make sure they receive the appropriate training to fulfill their new duties.
- Use a remote DBA service to cover some or all of these duties.
- Host your data estate through a Managed Service Provider (MSP), who will be able to fulfill all or some of these duties for you.
- Hire a reputable consultancy firm on a retainer to provide database management services on an ad-hoc basis.
No matter the option you choose to protect your business, the person responsible for your database environment will need to rely on software solutions to help manage the data estate. For example, there could be performance issues while your staff is creating or testing Business Continuity plans. Without a database performance monitoring system running continuously, they will not be able to quickly find the root cause of the problem.
SentryOne provides award-winning database performance monitoring software for the Microsoft Data Platform called SQL Sentry. In addition, SentryOne has many partners across the globe. If you are looking for a reputable services provider, let us know and we can introduce you to one in your region.
Richard (@SQLRich) is a Principal Solutions Engineer at SentryOne, specializing in our SQL Server portfolio offering in EMEA. He has worked with SQL Server since version 7.0 in various developer and DBA roles and holds a number of Microsoft certifications. Richard is a keen member of the SQL Server community; previously he ran a PASS Chapter in the UK and served on the organizing committee for SQLRelay.
