Managing Cross-Platform Database Environments

How many different databases are in your environment?

Not just individual instances, but how many different platforms (Oracle, Microsoft SQL Server, DB2, SAP, MySQL, PostgreSQL, MongoDB, Cassandra), structures (relational, non-relational), infrastructures (cloud, on-premise, hybrid, Database as a Service), editions (enterprise, standard) and IP models (proprietary, open source) do you maintain in your environment?

In organizations large and small, database administrators (DBAs) are under pressure to adjust to changes in database technology, to adopt new database flavors like open source and NoSQL, and to ensure the best performance and stability for their entire environment. A survey by Unisphere Research emphasizes those pressures on the DBA :

  • 72 percent of DBAs replied that the number of databases they're responsible for is increasing.
  • 69 percent of DBAs consider performance their most important responsibility.
  • 64 percent say that the cloud is having a major impact on their role.

This technical brief outlines the top five complications faced by DBAs amid the rush of new database technologies in recent years. For each challenge it provides background, context and the benefits Foglight for Databases brings in addressing the challenge. DBAs and managers will take away new perspectives on tools for managing their cross-platform environments.

Managing Multiple Platforms

As noted above and captured in the Figure 1 pie chart from Unisphere Research, DBAs are responsible for an increasing number of databases. Organizations are adopting more databases of various types because of new development projects, mergers/acquisitions and skunk-works efforts that suddenly catch on. They turn to corporate IT to own the databases and to DBAs to manage them.

Claims that "We're an Oracle shop," or "We run only SQL Server" are decreasing in number because Oracle, for instance, has no SQL offerings for big data while Microsoft has on-premises and cloud offerings alongside its offerings for big data.

How many database platforms (i.e., platforms from different vendors) is each DBA responsible for managing in your organization?

Figure 1. DBAs responsible for multiple database platforms

Managing Multiple Platforms

As noted above and captured in the Figure 1 pie chart from Unisphere Research, DBAs are responsible for an increasing number of databases. Organizations are adopting more databases of various types because of new development projects, mergers/acquisitions and skunk-works efforts that suddenly catch on. They turn to corporate IT to own the databases and to DBAs to manage them.

Claims that "We're an Oracle shop," or "We run only SQL Server" are decreasing in number because Oracle, for instance, has no SQL offerings for big data while Microsoft has on-premises and cloud offerings alongside its offerings for big data.

A sufficiently large company may be able to afford single-platform DBAs who can focus 90-plus percent of their time on one product or another. But in most cases, companies are asking their DBAs to become proficient in and manage multiple databases.

Consider a typical view of the database landscape from DB-Engines, as shown in Figure 2.

Figure 2. DB-Engines Ranking (db-engines.com/en/ranking)

DB-Engines measures the popularity of a system based on several formal and informal parameters, such as search results, online discussions and mentions in professional networks. Naturally, its results include the largest vendors of proprietary databases like Oracle, SQL Server and DB2, whose rank may fluctuate but whose absolute numbers are relatively stable. Also present are open source platforms that have been around for at least ten years, such as MySQL and PostgreSQL. Of note are the relatively new entrants like MongoDB, Redis and Elasticsearch, whose popularity is increasing.

The in-and-out, up-and-down nature of these rankings reflects the facts of life for about two-thirds of DBAs. Besides, supporting more platforms also means being nudged from the relational world of the DBMS into the non-relational world, as shown in Figure 3.

Are the DBAs who are responsible for managing relational database management systems also responsible for managing non-relational systems (such as NoSQL and Hadoop)?

Figure 3. Managing non-relational DBMS (Only respondents who had deployed Hadoop or NoSQL technology)

Obstacles and Recommendations

So, why is it difficult to manage multiple database platforms? Most DBAs consider themselves experts in one primary platform, the one with which they are most comfortable. When cutting their teeth on a new, secondary platform, they tend to think, "How do I do this on MongoDB and make it similar to the way I'm accustomed to doing it on SQL Server?" It's a valid point of departure, but before long they trip up on the differences between the platforms and on their own gaps in knowledge, training or experience. For some tasks, they rely on mature tools that are either unavailable or unready for prime time on new platforms.

Even with an unfamiliar database, every DBA knows the familiar, common questions to answer to keep any system properly supporting the application infrastructure and performing well:

  • "Are my database servers available, or as available as they need to be?" Good database administration means knowing immediately that a database server is down rather than relying on users to raise the alarm.
  • "Do I have a proper backup and recovery strategy, and is it working?" No IT group wants to be caught flatfooted in case of a failure or disaster.
  • "Do I have enough space on disk to store persistent and temp data?" Most databases don't function well when they run out of room.
  • "Are there critical entries in the database server error logs?" Every database server writes to log files, even if the information they contain is sometimes inscrutable. They can be a valuable starting point for all kinds of inquiries, so smart DBAs know where they are and keep an eye on them.

That list will vary among organizations, but it contains the baseline standards one needs to enforce. Once they've been met or exceeded, DBAs can move to the next level of questions about capabilities:

  • "How does the database server handle locking, and how do I diagnose problems?"
  • "How can I find out which users are logged in to the database server? What are they doing right now?"
  • "How can I view the most resource-intensive operations?"
  • "Can I schedule database maintenance jobs to make the system healthier?"

The answers to those questions help DBAs make decisions about resource allocation. They can lead to diagnostics on common tasks and measures as simple as knowing how to kill a session that's hogging resources from the rest of the workload.

Then, with the firefighting and database health issues in hand, DBAs can turn to the topics that make databases so important to the business, like performance tuning/optimization, high availability and security. And while database monitoring seems like the best way to keep an eye on performance tuning, high availability and security, it often introduces more complexity.

Each platform has its own methods, tools and APIs for database monitoring, and the learning curve for them is steep because they differ from one another. As noted above, some platforms are too new to have mature, useful monitoring tools. DBAs managing three database platforms may find themselves juggling three different sets of monitoring tools, each of which presents information in a different way with different UI.

Managing Multiple Platforms with Foglight

Foglight for Databases is the database monitoring solution from Quest Software that is designed for cross-platform environments, as shown in Figure 4.

Figure 4. Foglight for Databases, global view

Foglight monitors traditional database platforms like Oracle, SQL Server and DB2, plus newer platforms, both open source and non-relational, like Sybase/SAP Adaptive Server Enterprise (ASE), MySQL (including MariaDB, Percona and AWS Aurora), PostgreSQL (including EnterpriseDB), MongoDB (all editions) and Cassandra (open source Apache and commercial DataStax).

Its cross-platform coverage centralizes monitoring under a single umbrella so that DBAs no longer need bounce among different tools. Foglight also simplifies the UI of examining the health of databases, regardless of platform, by enabling drill-down from one layer to the next through clicks. DBAs enjoy a uniform view of statistics on instance performance, database availability, disaster recovery preparedness, remaining storage and server resources (CPU, memory, disk latency). The tasks of database tuning and workload analysis become consistent across database platforms, making life easier for DBAs.

Managing Multiple Database Editions with Foglight

Some vendors offer several editions of their databases, such as a fully featured enterprise edition, a lower-cost standard edition with fewer features or limited functions, and a stripped-down express edition. The Oracle Diagnostics Pack, for example, includes powerful monitoring capabilities like Automatic Workload Repository (AWR), Automatic Database

Diagnostic Monitor (ADDM) and Active Session History (ASH). However, the pack requires not only a license for the enterprise edition of Oracle, but also an additional licensing fee. That frustrates DBAs in cost-conscious organizations because when they have limited diagnostic capability, they have fewer tools for resolving problems and less control over their databases.

The Performance Investigator (PI) feature included in Foglight is designed to address that frustration. PI does not access any tables, views, procedures or APIs that are subject to license, so DBAs can use the product to get all the performance diagnostics of Foglight without licensing more-expensive editions of the databases.

PI is particularly valuable to DBAs who want to see deep into the database workload and examine, for example, which SQL statements were executed by specific database users or programs. ASH in the Oracle Diagnostics Pack provides that information, but only on Oracle Database Enterprise Edition and only at the additional cost for the pack. Foglight, by contrast, offers the same information for any edition of the database, as shown in Figure 5.

Figure 5. Foglight Performance Investigator

In another example, DBAs may want advisories and root-cause analysis to help improve database performance. Prescribed actions include recommendations about objects, indexes, parameters or file configurations that make the database respond faster. ADDM in the Oracle Diagnostics Pack provides such advisories, but Foglight provides similar advisories accessible in all database editions for both Oracle and SQL Server.

Managing Large Environments

No matter how diligently DBAs try to consolidate or decommission obsolete servers, new servers still seem to come on line faster. Unisphere Research reported on the ever-increasing number of database instances for which DBAs are responsible, as shown in Figure 6.

Figure 6. Managing large environments

Almost 40 percent of DBAs manage more than 25 database instances each, and most DBAs believe that the number of databases in their purview is rising.

Change of Focus in Large Environments

The increase in the number of databases to tend mean less time to spend on the problems of each one. In fact, in large enough populations, some database servers may go for weeks or months on end without attention from a DBA. Large environments nudge admins away from instance management and toward inventory management, such that the entire database environment, rather than individual systems, becomes the focus of installs, upgrades, patches and asset management. With a few hundred database servers, the one inventory management task of patching and upgrading could soak up several full-time DBAs.

Besides managing inventory, admins have to manage capacity of their IT infrastructure and keep an eye on areas of over- or under-utilization. Exceeding available capacity keeps resources from behaving well, introduces the risk of failure and hampers important indicators like performance and availability. At the other extreme, under-capacity systems amount to wasted money in resources, power, rack space and licenses. The balancing act is difficult enough in small environments, let alone in large ones.

As DBAs see their duties become more numerous, automation grows more appealing. The less hands-on work that an individual DBA has to perform on a particular database server, the more efficient the operation can be as a whole. Plus, automation frees up DBAs for higher-value tasks. Patching is a perfect example of this; automated systems like System Center Configuration Manager (SCCM) from Microsoft are made for automated patching. Other tasks that can be automated include maintenance and testing backups or disaster recovery in worst-case scenarios. Even some performance management tasks can be automated.

Standardize, Create Routines and Run Regular Health Checks

Variety and one-offs are the enemies of DBAs managing large environments. It is a huge advantage to set and enforce standards across servers because that's the way to provide a relatively similar level of service. As the environment grows to hundreds or thousands of databases, the work involved in simply knowing what each database server is responsible for grows considerably.

Standardization is valuable in several areas:

  • Common reports that run automatically and regularly against all database servers
  • Common alert templates that use the same criteria and scale of risk to indicate imminent trouble
  • Consistent maintenance jobs when possible so that each server is maintained similarly
  • A configuration management database (CMDB) – formal or informal – with homogeneous information on the status of database servers and instances.

One of the most effective measures in managing large environments is to conduct regular health checks. The checks involve the entire team of DBAs pooling their knowledge of the environment once a month with the goal of eliminating redundant or obsolete servers and database instances. By monitoring the number of incoming connections, teams can identify candidates for consolidation and decommissioning, and incrementally lower the raw number of systems they have to administer.

Managing Large Environments with Foglight

Foglight automates a rich set of out-ofthe-box dashboards and reports on database environments of any size. For the health checks described above, teams of DBAs can sit separately, examining the same Foglight dashboard simultaneously for underutilized systems ripe for consolidation. Or, in the case of automated reporting for upper management, DBAs can schedule reports on, say, recent service-level agreement (SLA) attainment or growing needs for storage capacity, then have Foglight deliver those reports regularly without any ongoing effort.

Figure 7 shows an inventory report of all database systems (here, SQL Server), including versions, editions, hardware profiles, high-availability options, storage space allocations and number of databases on each server. It lists all of the instances currently being monitored.

Figure 7. Foglight for Databases, Enterprise Instance Inventory

Figure 8. Foglight for Databases, Enterprise Availability Summary

Figure 8 shows the kind of report likely to be of interest to C-level executives who are accountable for system availability. Foglight can generate and distribute such reports automatically to any defined set of addressees.

For at-a-glance overviews of resource consumption, I/O and other system details, Foglight offers a drag-and-drop designer for dashboards. Figure 9 shows a typical example.

Figure 9. Foglight custom dashboard

Figure 10. Worldwide RDBMS revenue, IDC

Managing Databases in Different Locations

Gone are the days when all databases resided on disks spinning in racks on the company's premises. Variety in the location and type of servers has become a fact of life for DBAs charged with monitoring databases.

Figure 10 shows the results of research from IDC into projected revenue growth for RDBMS over six years. While the absolute number of on-premises databases will likely continue to dominate, the growth rate for public cloud (33.8 percent) will likely continue to outstrip that of on-premises deployments (2.6 percent).

Much of the growth in public cloud databases is rooted in Database as a Service (DBaaS), which can outsource and simplify the process of owning database infrastructure down to a monthly charge on a credit card. But even DBaaS represents databases that DBAs must still support, manage and monitor.

Besides being located on premises and in the cloud, databases can reside on physical or virtual servers. In global organizations they can be anywhere on the planet, and DBAs must manage them remotely. Managed service providers (MSPs) administer databases on behalf of their customers.

In all such cases, DBAs are tasked with efficiently monitoring and diagnosing the performance of far-flung database environments.

Managing Databases in Different Locations with Foglight

Foglight is designed to provide a single view with the same look and feel for all databases regardless of location and physical/virtual status, as shown in Figure 11.

Foglight monitors databases remotely without the need for additional schema objects in the database or an installed agent on the host computer or database server. Its agentless architecture allows it to monitor any database environment from anywhere — from on-premises to the cloud, from cloud to cloud and from cloud to on-premise — in all architectures and configurations.

Figure 11. Managing different database locations with Foglight

Managing with Minimal Overhead

The cardinal rule in database monitoring is to not do more harm than good. Given that most performance monitoring techniques introduce some performance hit of their own, DBAs are keen to monitor as thoroughly as possible with minimal overhead. The larger the environment, the greater the overall effect of overhead-heavy monitoring.

The main culprit is the large number of APIs for pulling performance data out of a database server. Each platform has its own set of APIs, and some database platforms provide multiple options. SQL Server, for example, offers numerous ways to collect performance data including running traces through SQL Server Profiler, running them server-side, querying Dynamic Management Views (DMV), and using Extended Events. That variety in APIs is common among database platforms, so the number of options begins to mount quickly in cross-platform environments. Unfamiliar platforms further complicate the situation; well-meaning but incomplete research by a DBA can backfire and result in even worse performance.

A few simple guidelines can keep DBAs from making costly mistakes:

  • Don't assume that an API provided by a database platform for diagnosing performance is ready for prime time. DBAs should always know how much overhead is involved before they install anything. If there is no firm, quantifiable information on overhead, then a second-best is the API's process for collecting data.
  • Test APIs on non-production systems. A non-production environment is the place to test things like performance diagnostics. It is easier to put a workload on a test server — even in the cloud — to monitor and diagnose performance issues than to undo problems in production.
  • Consider where any data collected by the APIs is stored. Data collected, stored and analyzed locally on the database server adds overhead to the system. For example, starting a server-side trace on SQL Server generates trace data. Building that trace table, storing it on the database server and then running queries against it will consume valuable resources and add overhead.

The key is for DBAs to gather as much information as possible about the APIs being called, then to test the APIs on non-production instances. That way, they can be sure that the diagnostics tools they may need to run in a crunch are not causing additional problems.

Managing Overhead with Foglight

Managing databases is a higher-value use of a DBA's time than managing database monitoring. Therefore, administrative overhead is more important than performance overhead. Time in a DBA's day spent installing, configuring, patching and generally using a monitoring product takes valuable time away from managing the database server itself. Products that require local agents to collect data can cause more problems than they solve and incur as much administrative overhead as they do performance overhead.

A sensible rule of thumb in managing administrative overhead, then, is that a monitoring product should save at least five times the number of minutes it costs the DBA. That is, if it takes 60 minutes to diagnose a problem without a given monitoring product, then it should take no more than 12 minutes to diagnose it with that product.

Foglight is built around carefully chosen APIs for capturing performance data. To keep its overhead as low as possible, it does not use computationally expensive APIs. Also, it stores all of its monitoring and diagnostics data in centralized repositories. Nothing that Foglight collects goes back onto the database server itself; DBAs running diagnostics or performance tuning are working exclusively against Foglight.

Figure 12 depicts the architecture of Foglight.

Figure 12. Foglight architecture

In the monitored environment on the left, agentless data collection means that nothing is installed on the database servers.

The Foglight Agent Manager in the middle performs remote data collection; DBAs may install it anywhere to collect data from the systems being monitored. For example, in a geographically distributed environment with database servers spread around the globe, Agent Manager can run in each data center for reliable, local collection. Or in a hybrid cloud deployment with 80 percent of systems on premises and 20 percent in the cloud, Agent Manager can reside in the cloud to monitor those systems.

Foglight stores its data in a database repository as shown on the right and performs monitoring and analysis there rather than against the monitored instance.

Conclusion

As database technology blossoms from on-premises environments to the cloud and DBaaS, DBAs face an increasingly fragmented landscape of database monitoring. It is nearly impossible to be an expert in traditional, NoSQL, relational, non-relational and open source platforms simultaneously, yet the need to have a constant, homogenous handle on a changing, heterogeneous environment continues to grow, especially as organizations run more and more databases.

Foglight for Databases gives DBAs deep insight into the health and performance of their databases regardless of platform. It abstracts the difficult work of choosing and calling the APIs best suited to reporting on each type of database and rolls results into a single UI so DBAs can monitor and tune their environment efficiently.

About the Authors

Jason Hall has been with Quest since 2005. He is currently a senior sales engineer focused on database solutions for strategic accounts and has previously held roles in management across a range of teams. Jason's areas of specialization include database administration and development, SQL Server, Oracle, business intelligence, performance monitoring, high availability, and disaster recovery. He holds a MCITP (Microsoft SQL Server) certification and has presented at several industry conferences, including SQL PASS, SQL Server User Groups and SQL Saturdays.

Pini Dibask is Product Manager for

Database Performance Management Solutions. Based in Israel, Pini has been an Oracle DBA since 2006. He is an Oracle Certified Professional DBA (OCP) and Oracle ACE, and has presented at Oracle OpenWorld, IOUG Collaborate and at other Oracle user groups.

About Quest

At Quest, our purpose is to solve complex problems with simple solutions. We accomplish this with a philosophy focused on great products, great service and an overall goal of being simple to do business with. Our vision is to deliver technology that eliminates the need to choose between efficiency and effectiveness, which means you and your organization can spend less time on IT administration and more time on business innovation.