Database Monitoring Strategies

Every data-centric (IT) organization that uses an SQL (ess-que-el) Server has at least one database administrator (DBA). The DBA’s assigned responsibilities are typically some equally ambiguous re-phrasing of “take care of the database.” The better the organization is able to expand upon “take care of the database”, the more likely the DBA is to be able to meet the expectations.

The Strategic Business Context

One of the most important responsibilities any DBA will own is the monitoring of the database. Without adequate database monitoring, there is little basis upon which to improve and validate the fundamental performance, security, availability and reliability of the database. Nearly every organization looks to the DBA as a channel into the ether-data that database monitoring can provide. The expectation is that a good DBA can tell anyone what they need to know about the database at any time. Very often this expectation is coupled with a reality that no one else in the organization knows enough about the topics to even make an accurate assesment of the DBA's skill set. 

Even for a highly skilled DBA, this can be an inefficient proposition. Equally so for the database itself. Blanketing a database server with monitors in anticipation of all possible questions the organization may choose to ask is daunting even in a small shop. It is without exception more effective for the DBA to educate the business so that informed decisions are possible than to anticipate the missing information from poor requirements.

Fortunately, most of the questions to the DBA from the business will be data related. Such questions require only a simple query; provided of course, that the sitting DBA knows the data well enough when the question needs an answer. Business monitoring requirements that are easily translated into a SELECT statement are reasonably well supported by the ad hoc query method commonly used by DBAs to model and answer questions from the business. The problem is, the organizations intended methodologies and security patterns don’t always support that method. For example, when the questions from the business deviate from well know business goals, a political response may be expected from the DBA. 

             "What was going on in the database at such and such a day/time?"

             "Who made this change? "
             "Would you make this change? "

or, the ominous 

             "Who authorized this?"

To the extent that the database is crucial to the success of the business - and of course relative to the size of the IT organization - resources will be allocated for database administration. In this way database administration has always been scalable, even if unavoidable. The incipient requirement could be for only a set of part time activities and responsibilities willingly taken on by a de facto DBA. At the other extreme, a large team of highly skilled DBAs could administer thousands of SQL Servers across the globe.

As the IT organization matures, DBAs need to work with application developers, IT operations folks, system administrators, business administrators, and even the boss to identify business process problems by monitoring the application data and configuration metadata. For the SQL Server DBA this could mean anything from the non-stop flow of requests for ad hoc queries of the production data set to an automated query performance monitoring process. At the same time it could mean anything from digging through the bits on two servers to try to figure out why the code works differently between them to documenting the code change history affecting the customer table, to automated validation of configuration. This effort is clearly to the common good of the organization.

Individuals from each functional area of the IT organization must bring specialized expertise to the shared database monitoring effort. The DBA must define and defend the database perspective among this group.

A strong and growing body of evidence indicates that to the degree that perspectives are solicited by, incorporated into and disseminated from the strategic canopy of the business, a corresponding increase in overall performance in those named areas results.

This article strives to speak from the DBA and database perspective in that ongoing and ever changing discussion. Specifically, this article will advocate that an essential element for success in this effort is an IT organizational database monitoring strategy. So essential is the monitoring strategy that even if it is not explicit, repeatable, or even well known, it can be described. Better strategies explicitly foster repeatable process and are well know to those interested. And as a result the better strategy is fairly static, changing appropriately with the business and business applications.

In less complex data environments, the strategy is simple. Everyone involved has intimate knowledge of the complete strategy. If simple enough, there is no perceived need to codify the strategy. At some point, better sooner rather than too late, database monitoring must fall in line with the organizations other internal detection and monitoring systems. Like all other aspects of the organization, as systems complexity increases, the need to flesh out the database monitoring strategy increases and the need for database monitoring practices consistent with other monitoring activites increases. One reason for this is that everyone involved in the system knows what is going on, or at least has adequate visibility for safe and proper operation. Another reason is that as more people become champions and administrators of the monitoring systems the tendency toward chaos due to differences of experience, technical skills, business focus, business role, interpersonal skills, ego, etc. increases in the absence of good leadership and effective strategic guidance from those leaders.

An overarching business strategy encapsulates the IT strategy. The IT strategy envelops all database practices including the monitoring strategy. Strategy is the organizational instrument that explains how goals are attained. For example, goals of 20% increase in throughput might be supported with performance monitoring tactics or 99.9% availability might require both systems state monitors and heartbeat monitoring of one or more applications or services.

The purpose of the database monitoring strategy is to create sufficient structure to transform the business’s need to know into effective and appropriate monitors. If the strategy is comprehensive and effective, then the technical monitoring solutions are more coherent and cohesive. There can be no database monitoring strategy that is not consistent with the IT organization’s over-arching monitoring strategy and not targeted at the well know goals of the IT organization. The database monitoring strategy holds a place in the organizations overall strategic framework.

The Strategic Framework

• describes the best practices for mapping business rules to monitoring requirements
• directs the organization to determine what to measure and monitor as part of the ongoing business processes
• instructs IT to provide the most appropriate technical monitoring solutions
• protects the primary purpose of the database

Business requirements ought not define how to implement database monitors. Business requirements for a monitor ought to define what needs to be verified and why it is important to do the specific verification. This will serve as a foundation to determine the relative urgency and escalation protocol for each monitor.

A strategic inspiration from leadership is required to make this structural distinction clear to all.

This distinction is also a likely crossroads for the virtual interface that stimulates productive dialog between the keepers of the business interests and the doers of technical implementations. When the keeps and doers are one in the same, the strategy must also create visibility to that monitor by others within the IT organization.

There exists a large minority of database monitoring requirements where the DBA is the keeper of the business interest and the doer of the technical implementation. While an obvious role conflict and therefore an internal security risk in theory, it is common for DBAs to a build database monitors that are not known to others in the organization. Other IT operations folks have comparably compromising assignments in other technical areas and will create monitors not intended for general consumption yet deemed necessary for personal use. This is a common practice. A database monitoring strategy explains the expected protocols to be followed to assure transparency for such monitoring activities.

Interestingly, most, but not all, metrics for general systems monitoring will ultimately come from the database. This includes a large number of monitors that are not owned by the DBA group. Because such scenarios are common and can quickly develop in any organization, the advantage of strategy is that the DBA is informed. This can be useful to prevent incorrect or inappropriate monitoring responses.

When there is consistent and adequate structure in defining monitoring requirements, the DBA knows what the business wants to know about the database and is afforded the opportunity to plan, test and deploy a solution. Well defined automated processes are more easily achievable. Through the planning and process that emerges from strategic initiative, the organization will get dynamic information deemed vital to continued success in a timely manner – and with very low overall impact on the system. Alerts, notifications, and on-demand reporting requirements are also more easily provided as necessary when the strategy defines what is appropriate. Without an effective database monitoring strategy, the DBA is left to build a waterfall of one-off monitors in response to a disconnected series of request and requirements.

And if the DBA leaves the IT organization, the monitors fall into disuse, but are left to clutter the environments due to uncertainty about the use and effect of removal.

During the research for existing information on database monitoring strategies, a handful of white papers covering the characteristics of “top performing IT organizations” surfaced. The papers, from the IT Process Institute, summarize a survey of 341 IT organizations. The top performing IT organizations as implicitly defined by the studies are not so much fast as they are accurate and resilient. Not firemen or superheroes, but IT workers doing mostly planned work. It sounded pretty dang good.

They maintain production systems in a known, tested, risk-reduced state. They standardize configurations to reduce complexity and improve scalability and supportability. They update production systems from golden-build configurations to minimize risk. And, they monitor for configuration drift and unauthorized changes to keep systems in a known state.

They minimize access to production systems. They recognize that small changes have a big potential impact, and they remove development access to the production environment. They clearly define roles and responsibilities and separate the duties of develop, test and build, and production release.

They allow modifications to the production environment only through a carefully controlled process. Every production modification is recognized as an availability and security risk. Each release meets build requirements, including documentation and support instructions. Releases are tested. Backout plans are tested. Releases are scheduled during maintenance windows and considered a failure if they do not exactly follow release instructions or are not completed on time. Release failures and process exceptions undergo root cause analysis to identify improvements that reduce process variation.

They use executive influence and human-resources practices to build a culture where following documented processes and procedures is recognized as a basic job expectation. They focus on process exceptions as a way to identify the cause of variance and to identify process improvement efforts.

Overall, these practices contribute to a systematic way to achieve the highest levels of performance. In top-performing IT organizations, consistently high and predictable performance is not dependent on individual preference or skills, and can be achieved across location and business unit. (source: http://www.itpi.org/home/white_papers.php).

If one allows the bold font in the quote to add emphasis, this comes across as four very sound IT operational principles. Considering only the summary guidance, a very different “overall” conclusion is made. In that context, the white paper concludes that skilled or even thoughtful people are not required. Not only not required but apparently irrelevant to achieving the highest performance standards described if the concluding statement is taken at face value. I have been fortunate to have worked with a number of very irrelevant people.

By now the image I’m trying to paint is supposed to be that of the patriarchal sweatshop boss strutting down the line of bleary eyed automatons, thoughtlessly chomping on a stogie, reminding all that they can all be replaced. That can’t be what the ITPI means. Most of us are comfortable that this is not a good foundation for a healthy work environment let alone a catapult to high performance IT. A little worrying that research even suggests that breakthroughs in high performance organizational benchmarks, no matter how cleverly derived, will follow a conscious organizational move toward mindlessness and ill-prepared workers. I must be missing something?

Perhaps it is simply the limitations of this author. It is possible that I have never worked in shop the ITPI would rate highly? Several shops I have worked in would seem to meet some of the broad anecdotal definition of “top-performing” used by ITPI in these white papers. However, in my experience, and without exception, those shops relied upon several highly skilled IT workers to make it happen. Could the reason none of those shops could be considered top performers be because skills and preferences got in the way? They could have hired garden slugs and tree sloths to get the same or even better results?

Philosophically sound or not, the ITPI could be the stuff your boss is looking at these days, trying to decide if she should relent and give you another token pat on the back this year or if this is the year that the process is mature enough to opt for the ITPI recommended wages of mediocrity.

To be sure, the statements ITPI make about the 4 indicators seem reasonable interpretations of the data that might emerge from a large survey of IT shops. And each is consistent with the practices and methodology trends described in other Microsoft SQL Server DBA community knowledge resources. I buy the general research as described in the white papers. Similarly, the first level interpretations are consistent with my expectations. It is good information about what others are doing and the success they are achieving. On the other hand, the “overall” conclusions seem Orwellian bordering on Simpsonian (as in Homer).

Monitoring for Change & Monitoring Change

Granted, the skilled worker pool can and does change over time. Having a good balance of skills and skill levels among workers is preferred to one person islands of knowledge and information. Regardless the state of balance, after the loss or reassignment of a highly skilled IT worker there is a period of reduced overall effectiveness within the shop in general. The IT organization must often re-invent itself in some way with the loss or addition of such workers. Building process to ensure knowledge transfer will reduce the risk for the organization and be of help to all workers in the post transitional environment.

Of particular mention, a process that allows highly skilled workers to avoid mentoring and to refrain from established modes of knowledge transfer poses an increased business continuity risk that is worth close examination. This is another tired old tactic, sometimes termed “job security” by workers, that wouldn’t even work with the cigar chewing line boss. It is the flip side of the nonsense coin where worker skill and preference is irrelevant if process is exceptional. Knowledge hoarding is never to the advantage of the organization and I doubt it ever serves the intended purpose. Furthermore, job security is a pipe dream. People playing such games are not well suited for the cooperative technical environment. They hold the others in the organization back.

To be clear, I fully accept the ITPI researcher’s assertions that a main ingredient in the recipe for effective and collaborative IT operations is process, but another equally important consideration is finding competent and compatible people - from the technical and interpersonal perspectives - at every skill level. The exact number and proportion of highly skilled and less experienced workers varies by situation and business decision. Without a collective minority of highly skilled key workers working within a strategic framework that is adaptable to varied skills and skill levels, an IT organization will never really cook.

And without an organizational resilience that fully values and exploits the skills and preferences of the available human resources, exceptional organizations will not emerge.

The Need for Lead

Change control, configuration standardization, change monitoring and leaders that lead are some of the process fundamentals that must be addressed to realize an effective database monitoring strategy. This has been true for as long as I have been a DBA. Time and again we see the framework weaken for a time when key people leave an organization. At these times, the adherence to good fundamentals tends to lessens until the responsibilities of the departing worker(s) are understood and absorbed by others. The process temporarily becomes less robust as workers try to compensate for the missing resources. If the framework was weak already and something actually does go wrong, the result could be a priceless moment. Once the lost ownership is re-claimed from the void, good fundamental practices will re-emerge in almost all cases. In part because the process has the flexibility to work with a wide variety of “individual preferences and skills” and in another part because good people become more skillful and develop preferences toward good outcomes no matter what is thrown at them.

There are many actions that can enhance and enable the normal dynamics of change. Collectively, the framework for working together to maintain a robust and reliable environment is initialized only when the boss convincingly says “make it so” and means it. When IT leaders exude and demand cooperation and excellence, the IT workers are most likely to deliver cooperation and excellence. In this way a basis for mutual respect and trust is established. The successful change process, accurate change monitoring, and ultimately higher levels of service become possible only when IT leaders model the change they wish to create. If the business and in particular the IT leadership do not lay the necessary groundwork and do the work of strategic planning, monitoring will invariably be reactive and incomplete.

Unlike configuration and tangible resource changes, application change management process is almost always formalized to some degree in every organization. Using standard configurations is also a widely accepted best practice. Monitoring for configuration changes and configuration drift is not often a standard practice.

A database monitoring strategy will be effective in detecting change and in cataloging who did what when. It will not say how to monitor this or how to monitor that, instead it will describe a framework that can cleanly maps specific monitors to exact business reasons when that is appropriate.

A database monitoring strategy provides best practice and business reason based guidance to those that define, refine, automate, document, evaluate and use the mechanisms in place to verify a data driven application's health. The database monitoring strategy is a subset of the overarching system monitoring strategy. Every IT shop has a monitoring strategy. It may or may not be clearly defined and clearly communicated. Without adequate transparency, the strategy’s value is reduced.

The database monitoring strategy is most effective when the requirements are clearly defined and software lifecycle practices applied in the same way as other database development and pre-deployment testing. Usually that means the strategy describes a project based and iterative IT undertaking to identify, build and implement the monitors necessary to assure the database's and the application's health. What the ITPI would call planned work.

It also means that the monitoring strategy must be adaptable to the changes of the monitored database servers. Not the other way around.

The database monitoring strategy is a valuable data layer planning mechanism. With a shared strategy, knowledgeable folks can work cooperatively to assure business continuity, even as the applications and roll call of knowledgeable staff change.

A good database monitoring strategy is not difficult to define. This is true in general for best practice guidance because simplicity is a fundamental best practice. However, and as is also generally true, to overcome the absence of a business requirements driven strategy requires a much greater effort if it can be done at all.

The scope of a database monitoring strategy will cover all database servers in the organization under one plan yet allow for any important differences between the monitoring requirements of one database server's role and another. For example, there are - appropriately - different considerations for each software lifecycle environment. Unit test, development, integrative testing, other pre-production, production, and post-production environments each require different monitors, different responses, and must elicit responses with differing urgencies and probably differing monitor alert recipients as well. A single comprehensive strategy will provide a roadmap to provide comprehensive and efficient monitoring.

There is no silver bullet here: more like a yellow onion. The best strategy will not only present an accurate indication of system health, but will also be instrumental in producing a timely, multi-layered and ever growing body of knowledge and information about the organizations database administration and operations monitoring practices. This information is critical and is often lost as talent transitions between organizational roles or leaves the organization. To the extent that collecting the information becomes strategic and the vigor with which that strategy is pursued, the impact of talented individuals departing, moving within or even joining the organization is mitigated.

There will always be a transition period when people come and go. It is little more than common sense that if workers are provided an easy to use protocol for relevant knowledge and information capture and use of that aid is not left as optional that a history is created that will ease those transition periods.

A design guide for analyzing monitoring requirements and identifying appropriate strategies is very helpful. This information must be combined with a commitment by the resident systems and database experts, leaders, managers and business unit contacts to identify and maintain the right strategy. That last bit is important. Any attempt to shoehorn a monitoring strategy into an IT organization that does not embrace the pragmatic benefits through an unequivocal commitment from the organization's leadership is almost certain to fail. It is tempting to extend that syllogism to say that any IT organization without an effective monitoring strategy is doomed. A closer to the truth statement might be, "any IT organization without an effective monitoring strategy is exposed to unknowingly repeat the same mistakes again and again".

That bit about the full support of management is a difficult one for most DBAs. The boss may not even be approachable – no matter how “open door” the stated policy is - by a DBA about the need for them to get behind the monitoring strategy. Probably not too hard for a good DBA to get the bosses stamp of approval, but that is not enough here. Strategic monitoring is a business decision. The organization must be committed to strategic monitoring to get the benefits. If the manager does not champion the strategy by taking it up the chain of command, for example or establishing consequences for non-compliance, the business is not afforded the opportunity to participate. Similarly, only the manager of all those responsible for database administration and operations can certify a single strategy used by all workers led by that manager. Everyone must work together in the strategic sense.

The definition of any monitoring strategy is never complete. Even the best database monitoring strategies can be improved. In addition, applications, databases and database platform technologies all change - and fail - in ways that require adaptive monitoring tactics. Once an effective monitoring strategy is successfully deployed, some small part of the energies and time once spent fire-fighting and other binary triage must be re-invested into review and maintenance of the strategy. Like almost all things IT, the monitoring strategy becomes iterative and all changes are introduced incrementally. Regular review of the requirements is necessary.

As I mentioned earlier, this information is directed toward Database Administrators, developers, operations folks, NOC personnel: anyone that has a direct role in assuring the health and performance of the organizations SQL Server infrastructure or an assigned database related duty contingent upon any change.

Data and IT managers will also find a wealth of employee planning goals and work performance evaluation metrics for those involved in implementing and maintaining the final strategy.

The relational database implementation focus here is on Microsoft's SQL Server database engine of the 21st Century:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008

While the basic principals are similar for monitoring other database engines and other active process, the physical implementation details are too varied to attempt a general application of database monitoring principles to the many other important platforms.

The differences between even the listed SQL Server Product Versions cannot be ignored. Nor can the differences between the growing family of SQL Server Editions: from Express to Enterprise. There is no choice but to include every identified aspect among the considerations to be sorted out while defining the strategy. The most significant among the differences are the the database layer events, reliable asynchronous messaging and .NET integration introduced with SQL Server 2005 and expanded with Extended Events, the Declarative Management Framework, centralized performance repository and the Data Collector in SQL Server 2008.

Somewhere during the SQL Server 2000 maintenance cycle it became possible to effectively and adequately monitor a SQL Server without a significant negative affect on over-all server performance. Of course, without the discipline and thought of a strategy it remains possible to unintentionally bring any SQL Server to its knees simply by trying to see if it's OK.

There are a number of symptoms of a missing monitoring strategy. Most are manifest as a non-stop sense of urgency among staff. Some only slightly embellished real world examples of inadequate monitoring deficiencies - with my sincere apologies to the very funny Jeff Foxworthy - are:

• If you were ‘requested’ to VPN in to the production database server at 1AM on the 4th night of your Greek Island vacation only to discover that the message no one could figure out was a harmless log shipping warning that occurred when the database backup fileserver was rebooted after OS patches were applied, you might need a monitoring strategy. Any manifestation of a never ending sense of urgency is a sure sign of an inadequate strategy. The only antidote is good planning. The more generally observable the sense of urgency, the more critical the need for a strategy. You may survive without the antidote, as too many shops do, but you will not find overdrive. If only there was a document in place back at the shop that explained the log shipping process and maybe even a couple of other folks in the shop that understand the process.

• If you just discovered that the production database server has mysteriously restarted itself in the middle of the night twice last week and you have no idea why or that it even happened until just now, you might need a monitoring strategy. A database outage is as serious a problem as an application can suffer. Not knowing why it happened are the words on the invitation for it to happen again. The effective monitoring strategy will provide a well know place that explains the necessary detail to capture for every outage and a standard mechanism to record all measures pursued to understand and rectify the underlying cause along with the results and observations for each occurrence.

• If the production database server has “locked up” twice in the last month and the reason remains undetermined, you might need a monitoring strategy. It is time consuming when the monitoring practices in place do not reveal adequate information for a problem resolution. Even with an effective database monitoring strategy this will happen. Usable monitoring strategies are adaptable so that necessary changes to monitors are without side effect and can be made with a very high certainty of success and no system impact if a problem occurs.

• If you've ever tried to restore a database backup from tape and the restore failed, you might need a monitoring strategy. Verifying the backups is part of any effective monitoring strategy that I am able to imagine. The typical organization has good intentions around the database backup practices. However, the difference between good intentions and an effective monitoring strategy may well be the difference between an ill-prepared outage and an elegant forward recovery. A monitoring strategy that identifies and includes second level tasks that are not easily automated like verifying backups and reviewing logs is critical.

• If there is more than one person with the ability to introduce database configuration and DDL changes into any live database in your organization, you might need a monitoring strategy. Guidelines for introducing changes are key to eliminating conflicts, any ensuing outages or instability and even the bitter pill of rollbacks. An effective monitoring strategy must cover the complete software lifecycle infrastructure of the organization. An effective monitoring strategy will include peer-review and feedback to promote agreement before changes are introduced over disagreement, lack of transparency, or among all interested workers

• If the NOC is staffed 24x7 and/or the Database Administrator requires more than 2 hours of sleep each night, you might need a monitoring strategy. Highly skilled workers are almost always outnumbered by less experienced workers. When the application is always live, the likelihood that knowledge stored only in the Database Administrator head will be necessary to make correct business decisions, even at 2AM, is far too frequent. If the DBA is tasked with, and given the time and support necessary, virtually all closely held information can be available to on-site staff without the need to roust anyone at 2AM.

• If you have explained the same process or work flow more than once, you might need a monitoring strategy. One of the hardest monitoring problems to solve is education. Monitoring systems are complex and ought to be completely transparent to the application's users. A typical pattern for an organization when a highly skilled database worker leaves and a replacement hired is for the hard work of the former employee to be discarded and for the new employee to implement a parallel but altogether different strategy based on previous experience. The result is that all others in the organization that were familiar with and perhaps even relied upon some small part of the previous strategy are instantly less effective. The effective monitoring strategy protects the best interests of the organization by including the body of personal knowledge necessary to support existing monitoring strategies.

• If the organization places low/no importance on a strategically refined monitoring strategy, you just might need a monitoring strategy. There are many management styles in action today. Many are based on the metaphorically flat organizations where the skilled workers are lightly managed. Such organizational philosophies invariable lead to a territorial - almost feudal or fief like- worker behavior. An expert carves out a realm - more often a fiefdom - and takes all responsibility and discretionary prerogative for that realm of responsibility and ownership. Such an amazingly disastrous pattern that is repeated over and over in the real world. The expert - perhaps inadvertently - creates job security in part by building monitors that are not exposed to others in the organization. Effective monitoring strategies must recognize and formalize collaborative efforts because they are more valuable to the organization as an ongoing concern.

Create a monitoring strategy

Monitoring requirements are everywhere. Each worker has some sort of a strategy or method around identifying and tracking problems. The difficulty with unstructured monitoring is twofold. First, the differences between personal strategies are frequently incongruent. Second, there is seldom any evaluation as to the effectiveness of personalized strategies. For example, one person may choose to manually edit a database column to correct an application anomaly. The business is happy because the error is corrected. However, if the problem continues to occasionally corrupt the data and another person - with no knowledge of the first person’s activities - escalates the application error back to the development folks. The result could well be a developer spinning his wheels looking for an adaptive mechanism to find a problem that is hopelessly masked by the unknown manual edits. At a minimum, the example demonstrates the frailty of any less than strategic monitoring.

In fact, reconciling many personal monitoring strategies into a common shared strategy is much of the work necessary to define an effective database monitoring strategy. To discard all existing monitors when defining the strategy would be a fatal mistake. Not only might you re-invent the wheel, but you could re-invent the flat tire. Furthermore, finding the least common denominator between two monitors is often a very good first step toward a better monitor.

Most of the tools necessary for an effective database monitoring strategy are included with a database product. When a built-in solution is not available or does not provide the necessary problem relief, third party software is usually less costly - and at least somewhat less buggy - than internally developed custom solutions when available. For example, the monitors for the tape backups above may be improved significantly by one of the many excellent third party backup products that use compression for pre-SQL Server 2008 database engines. This could improve automated test restore speeds enough that a test restore of each backup to commodity hardware becomes practical.