• Redgate Monitor
• Database Security Monitoring

Checking for Database Events Using Extended Events and SQL Monitor

You’d tend to think it was impossible for a database to suddenly ‘disappear’, until it happens. I still wince from the memory of someone in my dev team accidentally deleting a production database. He’d had both staging and production open, in two separate SSMS query windows, and got confused. I thereafter engineered the network routers to make sure that anyone who needed access to staging or production servers had to use a hot desk workstation in an isolated room. It served to prevent lapses in concentration.

How do you make sure that nothing untoward like this is happening to your key databases? It’s easy enough to get alerted if a database goes offline, or into any state other than online, and SQL Monitor’s built-in Database Unavailable alert will do just that. However, I want more. I want to know if a new database is created, attached or started, or if an existing database is stopped, detached or dropped. Firstly, I want to be alerted when a change like this happens, and then to get a list of all these database events, who did them, what type of change, to which database, when they happened and from which client application it came.

It sounds simple, and it is: we can set this up easily using an Extended Events event session that detects our database-level events, and then a SQL Monitor customer metric that that will query the event data on a schedule. We can raise an alert if the number or the nature of the events that have occurred, in, say, the past half hour, might cause the DBA anxiety.

Why Extended Events and SQL Monitor?

There is a whole class of database-level Extended Events that track every database checkpoint, page corruption, mirroring state, backup or recovery operation, bulk copy, file size changes, as well as database creation, stop, start, deletion or update, for any database on the server.

We can build extended event sessions to collect the data we need, and it will do most of the work for us. The nice advantage of adding SQL Monitor into the mix is that, with the event session, we can create a custom monitor that will query the collected event data, on a schedule, to see if any events have been raised within a specified period. We can set an alert on it, as well as plot its values on a graph.

SQL Monitor is also good at correlating events. If, for example, we wanted to check for BCP activity that isn’t part of a scheduled process, we can create an event session to bulk copy activity and write a custom metric for it. We can display its output on a timeline graph, and look for correlations with other metrics, such as the ‘Suspicious Errors’, ‘Permission Changes‘ or ‘Unauthorized Configuration Changes’ metrics.

Another important advantage of SQL Monitor, or any similar, external monitoring process, rather than using SQL Server’s own Agent, is that an external system is outside the bounds of the database user. It is difficult for anyone to tamper with it other than by switching off some of the Extended Events. A simple way to get around this, incidentally, is to use SQL Monitor to track the number of active Extended Event sessions and fire an alert when the number drops.

How to monitor for database events using Extended Events

In SQL Server Management Studio (SSMS), you can peek at these extended events in the session properties screen or in the session wizard.

I’ve selected only the database category and then the six events I want to monitor, database_attached, database_created, database_detached, database_dropped, database_started and database_stopped.

From a security perspective, I’m also interested in knowing if someone uses BCP to copy out data, via the databases_bulk_copy_rows event, but that’s probably better as a separate alert. There is also value in an alert that monitors changes in database size (databases_data_file_size_changed) but again that’s best dealt with separately, and you’d also want to know the new size and the amount of growth.

With all these extended events, we also need to decide which extra actions we need, in addition to the default ‘payload’ of event columns for each of them. We do this by clicking on the ‘Configure’ tab.

I’ve decided to add the client_app_name, database_name, sql_text and username. I find the SQL Text to be the most useful, but the database name is very handy to have. The database name isn’t sent with every event, whereas the SQL Text action is reliable in supplying the details.

With all this, we’ll know the name of the database on which the event occurred, the text of the DDL that was executed, the name of the client application that issued it, and associated database principal. In other words, we’ll know exactly who did what to which databases.

We also need to decide whether to store the events in a ring buffer or a file. With this type of event, I prefer a ring buffer because there are going to be so few events. I suggest that you retain a useful number of events for forensic work. I’ve specified sixty of each type in the following script, as an example, but you can adjust it. If we want to ensure the permanence of the event data, then it is easy to shred the XML document into a relational format and store the relevant rows in a table.

Creating the event session

You can simply create the event session from session properties window, or the New Session wizard, as described above, but I find it useful to extract the SQL Script , which you can get from the top of any of the pages.

Listing 1: DatabaseEvents event session

With any monitoring that uses Extended Events, we must create and start the session before we can use the results. This must be done independently of the custom metric in SQL Monitor, so you must remember to do both before SQL Monitor works. When a session is stopped, you lose the results.

Testing the event session

Here is a series of batches to test out the session. I’ve added guard clauses so that one can avoid errors when running tests out of sequence.

Listing 2: Testing the DatabaseEvents event session

We can view the data directly by using the ‘Watch Live data’ in SSMS:

It’s simple enough to display the other event columns too, by right-clicking on the grid.

Checking the results: Who tampered with that database?

I much prefer to extract the event data using SQL, so I can configure the query exactly as I want.

Listing 3: Querying the DatabaseEvents event data

As you can see, it shows us when the database change, the affected database, and the SQL Server principal that made the change, and the code that made the change.

This is probably less useful than it seems because it isn’t always easy to know which database was involved. Sometimes, an DatabaseId will relate to no database or a different database because a deleted database is removed from the sys.databases table and the ID is soon recycled, so if you try to find the name of the database from the ID it could easily be wrong or absent because it could relate to entirely the wrong database at the time that the lookup was done. The TSQL is a good guide to the affected database, in this case, but you get the whole batch, and if the batch has a lot of different actions that affect databases, then things can get murky.

Adding SQL Monitor into the mix

We now create a custom metric that we can correlate with other metrics on the server. As I mentioned earlier, there may be other custom metrics for these events that you want to create, based on the type of action being monitored, and the anxiety-level that the event is likely to create in the DBA, and therefore the severity of the alert.

Creating the DatabaseEvents custom metric and alert

This metric will query the event data, on a schedule, and assign each type of event an ‘anxiety rating’, between 1 (relaxed) and 10 (horrified). We are, of course, much less anxious about a database being started than a database being dropped. However, all DBAs who use this will want to set their own anxiety ratings, depending on the relative importance they attach to each type of database event.

We add up the ratings over a period to create an overall ‘Anxiety Level’ value that we can plot. I’ve chosen a sixty-minute period, after which, for me at least, the anxiety about an event tends to dissipate, though you may be different. In any case, you’ll remember the trauma, when you see it represented on a graph in SQL Monitor. There will, of course, be an alert as well.

Listing 4: The DatabaseEvents custom metric

When we create the DatabaseEvents custom metric in SQL Monitor (Configuration > Custom metrics), we paste in the code from Listing 4, and we specify that it runs only on the master database, because all these activities take place in the context of Master. We are, in effect, taking a server-based perspective of what is happening. We also need to set the frequency at which the metric data is collected. I’ve chosen a minute interval.

On the next screen, we set an alert, specifying our minimum ‘anxiety level’.

Testing out the metric and alert

Now, we just need to test it all out, simulating a session where our errant developer, Dave, thinks he is logged on to his development server rather than production. All we do is to run the test suite from Listing 2 with a delay between each batch:

The next screenshot shows the resulting ‘DatabaseEvents’ metric data, displayed on an analysis graph in SQL Monitor.

And here is the accompanying alert for “Unusual Database Events for a production server“:

When an alert is raised, you can find out who did what to which database using the query in Listing 3.

Conclusions

SQL Monitor comes with just the metrics that the Redgate developers can be confident are needed by most users. You just add the extra metrics that are relevant to your database server environment. These can be for special concerns, for ETL processes, for the particular applications you are running, or for metrics that are for processes or application components outside the server.

This example of monitoring database events such as databases being dropped or stopped should just serve as an example of the sort of thing that is possible. If the process you are measuring can be reduced to an integer, SQL Monitor can deal with it. In this case, I’ve introduced the idea of ‘anxiety level’ of an imaginary Robot DBA on the server, which may or may not match your own, as a measure that can be plotted. This allows several different events of varying importance to be reduced to a single metric.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more