In the last post, we explored a couple of examples of using Extended Events to enhance T-SQL error handling. There was some potential there. But a hard-coded SPID was necessary: we couldn't use the code examples for anything automated. It was cumbersome, too. Let's change that, shall we?
In the previous post, I examined a couple of error handling scenarios in T-SQL that don't work as expected with TRY...CATCH. The first problem involved multiple errors for a single statement in the TRY block: when control transferred to the CATCH block, only one error was accounted for. The second problem involved multiple errors caused by DBCC CHECKDB on a corrupt database. The TRY block "ate" all the errors and control never transferred to the CATCH block, leaving the impression there were no errors at all. Now let's look at a way to enhance error handling with Extended Events to overcome the shortcomings of TRY...CATCH for the two scenarios.
I've known for quite a while that there are issues with TRY...CATCH for T-SQL. I've had my share of frustrations with it, and I am clearly not alone. Just check out some of these Microsoft Connect items: 1 2, 3, 4, 5, 6, 7, 8, 9, 10. Five of them are Closed As Won't Fix, three of them are Active (all three were opened more than 8 years ago), and the other two are Closed By Design.
In my previous post, I defended the use of linked servers for administrative purposes. Now let's look at some practical uses. The basic concept is to connect to the CMS, iterate through sys.servers, select data from the remote servers, then return the data for all linked servers as a single data set. Let's begin with an example that returns a list of databases on all of our linked server SQL instances.
If you've ever given any consideration to IT security, you've probably heard of the layered security model. It's often explained with an onion analogy: at the center are your assets, surrounded by multiple layers (security controls) protecting them. Those assets are often your company's data in a relational database management system (RDBMS), such as SQL Server. One of the last lines of defense against unauthorized access to your data is the security configuration of the RDBMS. It is what lets the good guys in and keeps the bad guys out. This is not something to be taken lightly.
There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:
For many SQL Server professionals, our first overt need to handle an event was for a data-related action (ie an insert, update, or delete). Naturally, we use triggers to handle these events. Or more specifically, DML triggers. And if you are familiar with them, working with DDL triggers should be familiar too.