Fatal action guard: guarding against inadvertent execution of code that may damage or destroy data in SQL Server

There are many opportunities, before, during and even after to guard against potentially fatal actions that may damage or destroy data, like executing an Update or Delete statement without a Where clause. Such a fatal action guard can be implemented in different ways and at different times, relative to the incident, for example, before it to prevent it from happening in the first place, or after, to reverse an action that has already been executed.

Before

The best way to create a fatal action guard to manage inadvertent data loss is to prevent the script from even being created in the first place, let alone executed. If there is a programmability object e.g. a stored procedure that may contain such fatal code, we want to catch the problem before the object is added to production.

One way, is to implement checks as part of a continuous integration and delivery process. These checks would review all code for any particular bad SQL code smells, and report violations prior to the code/commits being approved for production.

Such a Review step can be implemented as part of a continuous integration pipeline, with ApexSQL Enforce, with two rules in particular, designed to detect and alert on fatal actions.

The first such rule is “Missing WHERE clause in DELETE statement”. This rule is part of the pre-installed rulebases.

In its core, this rule is searching for statements that doesn’t have WHERE clause, and is set to target Delete statements object.

The first part of the condition is searching for WHERE clauses:

bool MissingWhere = ActiveObject.WhereClause==null;

The second part is set to raise violation if the MissingWhere is missing:

if (MissingWhere)
{
RaiseViolation();
}

After a successful execution, ApexSQL Enforce will show the result summary with a detailed resultset list with each statement in the script. The line:col column will show the exact spot where the violation is raised and makes it easier to review the script in the DDL viewer.

The second rule is “UPDATE without WHERE clause” and can be found in the same rulebase. As the name says, this rule is set to raise violation on UPDATE statements without a WHERE clause. The condition for this rule is basically the same, with the only difference that the rule is set to target Update statements objects.

SSMS and Visual Studio integration

These checks can be moved forward in the development process, by integrating ApexSQL Enforce directly into SSMS or Visual Studio. That way as soon as the code is written, it can be immediately reviewed. Host integration is planned for ApexSQL Enforce R2.

Check-in policies and enforcement

Although this offers a great opportunity to catch bad code, it relies on the developer to manually execute these checks and to not forget. A fatal action guard that checks code on the actual check-in to source control, thus rejecting the check-in based on best practices violations, is a great way to keep bad code out of source control (and well away from production) and good code clean. By preventing the code from even being checked in to source control, the threat is neutralized well before it even gets into a CI pipeline, let alone a database.

TFS offers check-in policies currently. See the article: How to utilize Team Foundation Server check-in policies for more information. Integration of this feature is planned for ApexSQL Source Control as well.

During

That is all well in good, but for developers and DBAs who live life on the edge and make changes directly to production, such pre-boarding checks won’t help. In this case, having point-of-time fatal action guard to prevent inadvertently executing potentially fatal SQL code can be a life saver.

ApexSQL Complete, a free add-in for SSMS and Visual Studio offers a feature, to accomplish just that. Execution alerts exist to alert the user when they are attempting to execute an Update or a Delete without a Where clause.

This will not only alert the user to a potentially harmful action, but also allow a quick, tactical retreat before the deleterious code is executed.

Execution alerts can be used as fatal action guard in real time. The T-SQL code in query editor is being analyzed right before the execution and compared against so-called user-defined set of “rules”. If any of those rules are met, the mechanism triggers an alert in form of a warning message presenting a condition that might cause a problem in the future. It is up to the user to either proceed with an execution or to abort it, leaving the database state unchanged.

From the ApexSQL Complete main menu, click the Options item, and in the newly opened dialog select the Execution alerts tab. Notice that Execution alerts comes with five predefined rules which are some of the most common mistakes DBAs make in a daily work:

By default, all five rules are active. The user can create new rules and edit the existing ones to meet his needs. To edit set of rules for an existing execution guard, simply select it from the list, and click the Edit button:

This will open the Edit alerts dialog in which there are three columns: Type, Condition, and Value:

Each column has a drop-down list which represents a condition. All three columns combined make a rule. The user can combine conditions on multiple rows. If some rule is unnecessary, the user can either delete it or simply uncheck it and it will no longer be active:

To create a rule from scratch, use the New button. This will open the Create rule dialog in which the user can specify the Name, and Description of a rule. From the Type, Condition, and Value columns it is up to the user to set the conditions using the drop-down boxes:

Use the Add button inside this dialog to add a new “line” of rules. The Delete button removes the entire row of conditions:

So, let’s see Execution alerts in action and how this feature can be used as fatal action guard in a simple Delete statement. Note that the Delete statement is without a Where clause, and execution of this query would delete every row in a table. When the query is executed, Execution alerts analyses the query chronologically and looks for any rules defined to guard accidental data loss/modification. If any of them are met, this will trigger an execution alert and prevent this action from happening. The warning message presents information to user which execution alert is triggered and on which line in the query editor:

This use case scenario is covered by one of the predefined rules. Feel free to create your own set of rules that suits you best in a daily work.

Multiple execution rules can be triggered for one query. If this is the case, Execution alerts will list multiple rules that have been triggered in a single warning alert.

For more information on SQL coding execution alerts and fatal action guard methods please see the article: How to prevent accidental data loss from executing a query in SQL Server aka “Practicing safe coding”

After

Even with all of the aforementioned, bad code does get executed. Inadvertent changes are one of the leading causes of data damage and loss right up there with database failure, malicious changes and administration snafus.

If you have executed code that has damaged or destroyed data, all is not lost. In fact, if you have the proper safeguards in place, such transactions can easily be rolled back like they never happened, and without having to restore from a backup and overwrite any changes made subsequent to the last backup. In this way, you can rely on a post-incident fatal action guard, that even though it may not prevent the incident, can easily roll it back and recover the damaged or destroyed data in full.

You will need the following

  • A database set to Full recovery model, not simple
  • A transaction log file(s), either online or backups, that include the transaction in question
  • ApexSQL Log, a SQL Server transaction log reader that can selectively rollback any transactions that exist in the transaction log files

The process of rolling changes back with ApexSQL Log is simple:

  1. Start ApexSQL Log

  2. In the “Database connection” step of the wizard, choose your SQL Server instance and the database for recovery and provide connection credentials

  3. In the next step of the wizard, ApexSQL Log will automatically add transaction log files and backups that belong to your database. You can add additional transaction logs by clicking on the “Add file” button. When adding transaction log backups, always ensure that full chain of transaction log backups has been added to ensure ApexSQL Log has proper sources to successfully perform the recovery

  4. Next, in the “Select output” step of the wizard, opt to “Open results in grid”. This will allow users to see auditing results in a comprehensive grid and investigate information found in the transaction log file and isolate the particular transactions using numerous filters

  5. In the “Filter setup” step of the wizard, it is generally recommended to use plenty of filters to ensure that only those operations that need to be rolled back are included in the recovery process. Users can specify time range, exact operation types, tables where changes were made, users that have executed them and more. Note that filtering process can/should be continued once we’ve audited results into the grid so we can isolate particular update operations we are looking to rollback once we have a complete insight into our transaction log files. Once filters have been configured, click “Finish” to proceed

  6. After short processing, ApexSQL Log will perform transaction log auditing and display auditing result in a grid where user can perform deeper investigation of the transaction log data and use grid filters to further filter out auditing results until they can completely isolate operations in question, specific updates in our example

  7. Now that we can see auditing results in the grid, we can tune our filters to increase our precision. In accordance to the results, filter out:

    1. Auditing time period

    2. Specific operations and tables (even if you are not sure which table(s) have been affected, it’s always a good choice to filter out all tables for which you sure were not affected with those unintended updates/changes)

    3. SQL Server users (logins) which were used to commit changes we are looking for

  8. Once we’ve completed configuring filters, click on the ‘Apply’ button to commit filter changes to the grid

  9. With filters in place, instead of a grid full of operations, we should be able to isolate only those updates we want to rollback that match our filtering configuration

  10. Now that investigation is complete, the only remaining task is to roll back those unintended updates. To do this, select and check them in the grid, and click on the “Undo” button in the main ribbon

  11. After few short moments, ApexSQL Log will generate an undo script and show it in the built-in editor where it can be inspected (and edited if the need requires it). The only remaining task is to complete the process by clicking on the “Execute” button in the main ribbon. And, just like that, unintended updates have been rolled back, as if they’ve never occurred

 

October 24, 2017