Every DBA knows that a test platform is required to properly enable application development. Indeed, testing has always been crucial, but it has become more complicated with the advent of new technologies, platforms, and devices, not to mention the impact of regulatory compliance. One of the biggest challenges faced by DBAs and developers is managing test data.
Posted April 11, 2024
Database administration is a very technical discipline, but it is also a discipline in which the practitioner is very visible politically within the organization. DBAs should be armed with a proper attitude and sufficient technical knowledge before attempting to practice database administration. This column usually covers the many diverse technology issues with which DBAs must deal. But for this month, we are going to take a short diversion and discuss some high-level guidance for DBAs to follow that can make their job easier to deal with.
Posted March 14, 2024
Before talking about its impact, it makes sense to first define what is meant by the term "regulatory compliance." There are two components: regulatory and compliance. Regulatory refers to regulations, which are governmental, and business rules and laws. Regulations may exist for specific industries, countries, jurisdictions, and practices. Compliance refers to following the directives of the regulations as they apply to your business operations.
Posted February 08, 2024
Earlier in 2023, we discussed The Importance of Metadata here in the DBA Corner column. And, indeed, metadata is more important than ever before because it helps us to understand our data. Data without metadata lacks meaning, making it difficult to be used. Metadata gives data its form and makes it usable by your organization.
Posted January 11, 2024
Database performance and scalability are both important aspects of managing and optimizing databases, but they refer to different characteristics and considerations. Database performance refers to how efficiently a database system can respond to and process queries, transactions, and other operations. It is focused on the speed, responsiveness, and overall efficiency of database operations.
Posted December 14, 2023
An age-old question when it comes to staffing a DBA group is how to determine the right number of DBAs required to keep your organization's databases online and operating efficiently. Many organizations try to operate with the minimal number of DBAs that they can. The general idea here is that the fewer staff members you hire, the lower overall cost will be. However, that assumption is probably not entirely true. An overworked DBA staff can make mistakes that cause downtime and operational problems far in excess of the salary requirements of an additional DBA.
Posted November 09, 2023
Database performance can be a complex and difficult task, but there are some high-level maxims that can help to simplify optimizing the performance of your databases and applications. Let's look at some of these guiding rules of thumb. You should always keep in mind the business objectives of the databases and applications you manage. It is wise to manage performance based on the expectations and budget of the business users.
Posted October 12, 2023
The role of the database administrator (DBA) has always been crucial to ensuring that databases are properly organized, secured, and optimized. But recent years have seen many significant changes in the field of database administration. Before talking about how it is changing, let's start by defining it. Database administration (or DBA) refers to the set of tasks and processes involved in managing and maintaining database systems (and the applications and users that access them). This includes everything from designing and creating databases to deploying, monitoring, and securing data across the enterprise.
Posted September 14, 2023
Cloud computing has revolutionized the way businesses operate and has fundamentally changed the role of database administrators (DBAs). Traditionally, DBAs have been responsible for the installation, maintenance, and management of on-prem databases. However, in the age of cloud computing, DBAs are required to adapt to new technologies, methodologies, and tools.
Posted August 10, 2023
As a database administrator, you know that the field is constantly evolving. New technologies are emerging all the time, and it can be difficult to keep up with the latest trends. And almost every new technical trend and advancement tends to interact with data. So, DBAs need to be "in the know" and regularly update their knowledge of what's new. That's where industry conferences come in.
Posted July 13, 2023
Artificial Intelligence, or AI, is having a significant impact on most industries and job roles these days, and it will only increase as AI techniques and algorithms improve over time. I'm sure you've heard about things like natural language processing, machine learning, and large language models such as those used by ChatGPT and Google Bard. These are all forms of AI, and they are influencing and impacting the way we work.
Posted June 08, 2023
The end goal of database design is to be able to transform a logical data model into an actual physical database. A logical data model is required before you can even begin to design a physical database. Assuming that the logical data model is complete, though, what must be done to implement a physical database?
Posted May 11, 2023
Organizations often force the DBA to take on the job of data modeling. That does not mean that DBAs are well-trained in data modeling, nor does it mean that DBAs are best suited to take on this task. The data administration (DA) team is best suited for modeling data. This is because data modeling requires knowledge of the business aspects of data resource management.
Posted April 13, 2023
This month, I want to take a higher-level view of database performance concepts and introduce a few themes that will likely follow you throughout your career working with database systems and applications. These themes should be viewed as guiding principles to keep in mind when confronted with database performance issues.
Posted March 09, 2023
Today, nobody argues about the importance of data. It is a given that data must be collected, managed, and analyzed to conduct business successfully in the modern era. But all too often the meaning of the data is not being preserved. This can result in large stores of data that are difficult to interpret and use in any meaningful manner. Situations that create unusable data can be minimized with appropriate metadata management. But this requires effort and time, and therefore metadata is frequently ignored (much like documentation).
Posted February 09, 2023
Most database implementations are anything but static. Once deployed, databases are queried, updated, loaded, unloaded, reorganized, and data is deleted and inserted from them on an ongoing basis. Data is also moved around and replicated all over the place. All of these activities resulting in database changes cause storage requirements to change as well.
Posted January 12, 2023
Readers of this column sometimes ask me questions about databases and database administration, which I welcome. And at times I will take the opportunity to answer particularly intriguing questions in print. One intriguing question I have been asked more than once is: "What metrics and measurements are useful for managing how effective your DBA group is?"
Posted December 08, 2022
Last month we looked at various types of database recovery, how they work, and how DBAs need to prepare for recovery scenarios. This month, let's delve a little deeper into the issues and decisions that DBAs need to be prepared to address as they work on database recovery. The first thing that DBAs need to be aware of is the recovery time objectives, or RTOs, for the database objects in question. In an ideal world, RTOs would have been established for each object and the backup procedures would be in place to establish sufficient time for recovering to those objectives.
Posted November 10, 2022
Things break and the DBA must be prepared for situations where a failure impacts the availability, integrity, or usability of a database. Reacting to failures and service disruptions is a key component of the DBA's job. This means ensuring that appropriate backups are taken for all your critical database objects. Assuming backups exist, when an inevitable database recovery is required, the next decision is how to get the data you need back as quickly as possible. But there are several different types and methods of performing recovery.
Posted October 06, 2022
Establishing agreed upon services levels for database applications is of the utmost importance for assuring that performance meets required criteria. Without pre-determined, negotiated service level agreements in place, database and application performance can become a never-ending game of blind man's bluff, where DBAs blindly and constantly seek an unspoken goal. Instead, active service level management should be the standard.
Posted September 08, 2022
Assuring data quality should be one of the most important goals for IT professionals. This is truer today than it has ever been as organizations rely on data to power their AI and machine learning algorithms. Accurate, quality data is required if you want to make accurate decisions. And that is true, whether the data is being analyzed by a human or a computer algorithm.
Posted August 11, 2022
Optimizing the performance of operational databases and the applications that access them is a constant battle for DBAs. Of course, writing efficient SQL is the most important aspect of ensuring optimal database performance but no amount of SQL-tweaking or system-tuning can optimize the performance of queries run against a poorly designed or disorganized database. Therefore, time must be spent on tuning and optimizing the design, parameters, and physical construction of database objects, specifically tables and indexes, and the files in which their data is stored. The actual composition and structure of database objects must be monitored continually and changed accordingly if the database becomes inefficient.
Posted July 07, 2022
Data archiving is an important aspect of data governance and data management. Not only does archiving help to reduce hardware and storage costs, but it is also an important aspect of long-term data retention and a key participant in regulatory compliance efforts. When long-term data retention is imposed on your data—anything more than a couple of years—then archiving it can be the most optimal solution.
Posted June 02, 2022
Operational databases continue to expand, with database sizes growing in most organizations. In terms of performance, the more data in the operational database, the less efficient transactions running against that database tend to be. The other impact, database administration complexity, causes longer processing time and outages to perform traditional DBA tasks. But as important as operational performance and administration issues are, frequently they are ancillary to the regulatory issue of preserving authentic data over time.
Posted May 04, 2022
Data privacy regulations—coupled with the desire to protect sensitive data—impose requirements on organizations to protect production data. Since many organizations rely on production data as a source for test data, techniques are needed to mask sensitive data elements from unauthorized viewing.
Posted April 07, 2022
Standard SQL defines four isolation levels that can be set using the SET TRANSACTION ISOLATION LEVEL statement: Serializable, Repeatable Read, Read Committed, and Read Uncommitted. Although all of the popular relational DBMSs support isolation level, the exact naming and implementation will vary.
Posted March 11, 2022
It can get complicated trying to decipher what is actually offered by DBMSs that market themselves as multi-model. Which models? Delivered how? But using a DBMS that supports more than one model can provide a simpler, easier-to-support environment.
Posted February 08, 2022
The DBA should be an advisor to the business in terms of the types of database security that can be enabled. At a high level, this boils down to being able to answer four questions.
Posted January 03, 2022
Being a successful database administrator requires more than just deep technical knowledge of database systems. Gaining the technical know-how is important, yes, but there are many sources that offer technical guidance for DBAs. The non-technical aspects of database administration are just as challenging, yet there are fewer guiding resources to help. With this in mind, let's look at the 10 "rules of thumb" for DBAs to follow.
Posted December 08, 2021
Once data has been created, it gets moved around to support many different purposes. There are many techniques that can be used to facilitate data movement. One of the simplest ways for the DBA to move data from one place to another is to use the LOAD and UNLOAD utilities that come with the DBMS. The LOAD utility is used to populate tables with new data (or to add to existing data), and the UNLOAD utility is used to read data from a table and put it into a data file. Each DBMS may call the actual utilities by different names, but the functionality is the same or similar from product to product.
Posted November 01, 2021
You might think the title of this article is somewhat controversial, but you should wait to render judgment until you've read to the end. There are several important shifts impacting data management and database administration that cause manual practices and procedures to be ineffective. Let's examine several of these trends.
Posted October 05, 2021
Designing and implementing a proper database for application development is a complex and time-consuming task. The choices made during database and application design will impact the usefulness of the final, delivered application. One of the most important design considerations is the impact of database locking on concurrent processes.
Posted September 16, 2021
If you've been paying attention at all, I'm sure you've heard the term "phishing." It is used to describe the fraudulent practice of sending emails purporting to be from a reputable source to induce individuals to reveal personal information, such as passwords and credit card numbers. But maybe you haven't heard the term "vishing" yet.
Posted August 02, 2021
Picture this scene: It is a little after 5 p.m. on a Friday and a chat message pops up from my "favorite" application programmer. Something isn't working properly. Yes, that is the message. "Something" isn't working properly. That's all. This is no exaggeration of the types of situations that routinely pop up. Sure, these may not be the exact words, but I'd wager that most DBAs have been on the receiving end of this type of request. So, what should be done in this type of situation?
Posted July 15, 2021
With new "data models" and types of database systems purported to be better than relational, it seems that the industry is all too ready to stick a fork in relational. So, is relational technology outdated? There are all sorts of pretenders to the throne for data management including Hadoop, XML, and the various types of NoSQL database products. But why? Are there truly any shortcomings in the relational way that require its wholesale replacement?
Posted June 10, 2021
Even in today's modern IT environment, performance management is often conducted reactively instead of proactively. You know the drill. A client calls with a response time problem. A table space maxes out on extents. A program is running without taking commits causing all kinds of locking problems. Somebody changed an application package without checking on the new access paths and transactions are slower than before. And then somebody submitted that "query from hell" again that just won't stop running. Sound familiar?
Posted April 29, 2021
You would think that with the towering importance of data in today's modern organization that data modeling would be viewed as extremely important by management and IT professionals, so it is somewhat ironic that the age of big data has coincided with a long-term slide in data administration and modeling in many organizations. This is not a situation that should continue to be tolerated.
Posted April 06, 2021
There are many new and intriguing possibilities for analytics that require an investment in learning and new technology. But the return on the investment can be sizable in terms of gaining insight into your business, and in better servicing your customers.
Posted March 01, 2021
If you happen to be a fan of Monty Python's Flying Circus, then you probably recognize the line "None shall pass" in the title of this month's column. It is the unchanging exclamation of the Black Knight in the movie Monty Python and the Holy Grail. He just stands there trying to block everyone who attempts to go past him—even after a better swordsman has cut off his arms and legs. I'm sure that some of the application developers reading this can relate to this story, replacing the knight with their "favorite" DBA.
Posted February 10, 2021
Today, more than ever, it is important to break down the cultural barriers and foster an environment of teamwork and communication for the entire IT organization, and indeed, for the entire company. Perhaps one of the most important cultural hurdles to overcome these days is the need for speed. A study done by Forrester Research showed that Dev teams are accustomed to new releases on a quarterly basis or faster, and out on the edge there are teams that deploy multiple times a day! Then we have Ops teams, where the expectation is to have new releases twice a year or even slower.
Posted January 07, 2021
Due to the advent of client/server computing in the 1990s, then internet and web-connectivity driving development, and the more recent growth of NoSQL databases, we are now managing more types of data on more platforms than we ever have before. And that means that managing data is a lot more complex. This also means that DBAs need to possess expertise in cross-platform management and administration.
Posted December 10, 2020
Data gravity is the concept that data remains where it is and applications and services (and even more data) are attracted to and use that data. Moving data can be a tricky, time-consuming, and complex process, even on-prem. Now add in the movement of large quantities of enterprise data through the internet to a cloud service provider. Anyone who has managed or used big data, or even moderately large data, will immediately understand the problem.
Posted November 04, 2020
Database administration includes responsibility for ensuring the ongoing operational functionality and efficiency of an organization's databases and the applications that access those databases. Traditionally, for the DBA, this has meant becoming an expert about "all things" related to a particular database management system (DBMS), such as Microsoft SQL Server, Oracle, IBM Db2, and others, and that the DBMS was installed on-premise on physical servers.
Posted October 08, 2020
Good data quality starts with metadata—and the importance of quality data cannot be overstated. Poor data quality costs the typical company between 10% and 20% of their revenue. In addition, high quality data is crucial for complying with regulations.
Posted September 09, 2020
The database world is in tumult these days. There are new requirements and new capabilities that organizations are adopting and integrating into their data persistence infrastructure all the time. The world is no longer relational/SQL-only. Organizations are adopting NoSQL database systems to support specific use cases and types of workloads. This is increasing the complexity of how data is managed. But it is not just NoSQL that is driving organizations to run multiple DBMSs. Many organizations have more than one relational DBMS. They may run Db2 on the mainframe and Linux, Oracle on UNIX, and SQL Server on Windows, and perhaps have a few MySQL instances, too. And DBAs are managing a lot of different database instances.
Posted August 11, 2020
Both development and production database administration are required to support database applications. However, it is not usually necessary to have different DBA staffs to perform the different roles. Indeed, intimate knowledge of how a database application was developed can make it easier to support that application once it becomes operational in the production world. But the bottom line is this: You will need to define, plan for, and staff both development and production DBA roles in order to create useful database applications.
Posted July 01, 2020
When non-DBAs think about what it is that a DBA does, performance monitoring and tuning are usually the first tasks that come to mind. This should not be surprising. Almost anyone who has come in contact with a computer has experienced some type of performance problem. Moreover, relational database systems have a notorious reputation (much of it undeserved) for poor performance.
Posted June 10, 2020
As a database consultant, one of the things I am most frequently asked about is dealing with database locking issues. Every DBMS has different settings and parameters to control locking at the system and database levels. And, yes, it is important to set these up correctly, but they are rarely the core cause of performance problems caused by locking. So let's focus on the universal issue that is almost always the culprit: poor coding techniques and inappropriate oversight before code is moved to production.
Posted May 13, 2020
Modern software development teams have adopted a continuous delivery approach based upon DevOps and agile development techniques. The small and frequent code changes that result from such methodologies can deliver significant benefit in terms of reduced lead time for changes, a lower failure rate, and a reduced mean time to recovery when errors are encountered. Indeed, today's developers migrate more frequent changes into the production environment than ever before.
Posted April 08, 2020
Ensuring the integrity of the organization's databases is a key component of the DBA's job. A database is of little use if the data it contains is inaccurate or if it cannot be accessed due to integrity problems. The DBA has many tools at his disposal to ensure data integrity. At a high-level, there are two aspects to data integrity.
Posted March 05, 2020