Database monitoring has always been central to the role of Database Administrators (DBAs) to minimize downtime and optimize performance. Scheduled monitoring keeps an eye on resource usage like CPU, disk space, memory, and I/O capacity to spot trends and understand when more capacity will be needed. The same information also enables baselines to be established so that, for example, it is immediately apparent if a high-resource utilization is an abnormal spike, a worrying recent trend, or just normal behavior for the period in question.

Alongside scheduled monitoring, reactive monitoring also has its place in responding to alerts about a drop in performance or an increase in deadlocks, and drilling down to the cause of the problem before it becomes an issue. Here, the history and timelines which monitoring provides will help to identify if a stress phenomenon coincides with a particular type of processing, such as a weekly aggregation or a scheduled data import.

All of this will be familiar to any DBA, but the way databases are developed and managed has seen a big change over the last few years, driven by the adoption of DevOps, the move from on-premises to the cloud, and increasing concerns about data privacy. These three factors are introducing new challenges to database monitoring that every DBA needs to be aware of.

DevOps, for example, encourages the frequent release of small changes to applications, and those changes often mean the database needs to be updated as well. This was highlighted in Redgate’s 2018 State of SQL Server Monitoring report which revealed that almost 50% of respondents deploy database changes multiple times a week. That’s a big change from a few years ago when deployments were often major events, regarded with trepidation and planned carefully to allow for the expected downtime failed deployments would cause.

The cloud has also become mainstream, and the same report showed that 48% of respondents are already using some cloud technology, and moving to the cloud is their biggest concern. Interestingly, SQL Server implementations on Azure and Amazon are the most common cloud technologies used, and this will probably increase now that Azure SQL Managed Instances provide near 100% feature compatibility with on-premises SQL Server, yet also offer the benefits of a cloud service. The task here will be monitoring a mixed server estate, with legacy systems remaining on-premises, and new systems moving to the cloud.

And then there’s the privacy question. With new requirements for access to personal data to be restricted, there are demands on DBAs to monitor access rights and privileges – and demonstrate that such a system is in place.

Despite all these changes, many of the reasons for monitoring databases and the methods of doing so remain the same. This Refcard focuses on SQL Server databases but the broad principles are the same for any database. DBAs and systems administrators should make the most of built-in tools and resources but also call on third-party tools where necessary to manage their own server estate in the way their business demands.

Being a DBA is much more than knowing how to install a server and set up a database. One of the most important responsibilities is being proactive by monitoring instances to identify potential problems. But what should be monitored and why? Here are the top five things to monitor in the new era where DevOps, the cloud, and data privacy have entered the picture.

#1 Performance

Monitoring database performance has traditionally been about taking baselines, watching resource utilization (CPU, memory, I/O) changes over time, and determining the top ten or so worst performing queries so you can tune them.

With the database increasingly expected to be included in DevOps, leading to more changes, more often, another added measure has come into play. However thorough the testing regime is in development, a change can reach production which slows down performance, so monitoring the effect frequent changes have, at the time they are deployed, also needs to be added to the list.

#2 Security

The introduction of the GDPR, as well as upcoming legislation like New York’s Stop Hacks and Improve Electronic Data Security Act (SHIELD) and the California Consumer Privacy Act (CCPA) have put the spotlight firmly on security. Indeed, the SQL Server Monitoring report mentioned in the introduction revealed that security and protection are the biggest challenge for those responsible for managing SQL Server estates.

So, as well as keeping track of failed logins and how many accounts are in the sysadmin group, DBAs also need to be monitoring for SQL injection attacks, changes in server and database settings, and modifications to permissions, users, and roles.

#3 Backups

There are exceptions, but just about every database should be backed up on a regular basis, including frequent transaction log backups. Unless you have a job in place to back up every database on an instance by default, it’s easy to miss adding new databases. By the way, make sure you have a process in place to test backup files as well.

#4 File Growth

Over time, database files can run out of free space as can the volumes where these files live. Transactions must consequently wait while database files grow, and applications will grind to a halt if there is no more space in the files or space runs out on the volume. While the cloud offers elasticity and the ability to grow volumes almost instantly, this is still the case for on-premises servers.

#5 Job Outcomes

Great DBAs automate everything they can and use SQL Server Agent or some other job scheduler to run the scripts. They understand what the jobs do and the consequences of a job failure or long running job. They also have every job documented so that they can take a day off once in a while.

This is a preview of the Database Monitoring Refcard. To read the entire Refcard, please download the PDF from the link above.