Rules 101 – getting started with ApexSQL Enforce

Applies to
ApexSQL Enforce

Summary
A lot of the power of ApexSQL Enforce is its combination of:

1. A sophisticated script parsing tree and database metadata exposed via an internal object model/API that provides the target for writing rules against
2. Powerful scripting languages like C#, VB.NET and PowerShell (TBA)
3. An internal IDE for writing new rules and editing existing one’s including
   • Auto-complete
   • Syntax highlighting
   • Rule parsing and testing
   • And everything else you would expect from a IDE e.g. line numbers, find and replace etc.

Description

This article is a how to guide on getting started with rules in ApexSQL Enforce.

Some background

What ApexSQL Enforce does when it processes a database or a script is to load it, extracting all of the metadata and parse all of the scripts into a parse-tree. All of this information is then loaded into an object model which serves as the internal API that rules can be written against by users themselves, via the built in IDE, using auto-complete.

The internal object model/API can be visualized, at least at a high level, by the Rulebase explorer which shows the object model tree and the number of rules assigned to each object. Each node represents an element of the internal object model.

This sounds complicated, but we’ve abstracted that complexity with an easy to navigate internal API complete with auto-complete and syntax highlighting that makes writing rules often as simple as connecting the dots … literally.

Conditions

The core component of a rule is the condition, what the rule is evaluating, and the result, which is generally whether a violation is raised or not.

The purpose of script in ApexSQL Enforce rule is basically to evaluate the condition to a Boolean, and if the result is negative e.g. something bad exists, to raise a violation.

Conditions are self-contained scripts, in the programming language of your choice as long as it is C# and VB.NET. They can do anything any code can do although, at their simplest, they process a programmatic condition to return true or false, and based on that condition raise a violation. Violations are raised by calling a simple method RaiseViolation(). ApexSQL Enforce does the rest.

The true/false results might be whether this bad thing was done when creating a new stored procedure. If the answer was “true” then a violation will be raised. Or it might be if something is missing, which is bad, and again if “true” then a violation would also be raised.

Here we show an example of a rule determining if a cursor exists in a procedure or not. If it does we’ll evaluate this condition to true and raise a violation, because we don’t like cursors and want to fail any procedure that contains them.

In this case, we will check for the existence of a deprecated element in a SQL script. If we can find it (returning a Boolean result of True) we will again raise a violation, failing the offending object, sub-object or script element.

ApexSQL Enforce consumes that raised violation, and adds it to the results of all other rules. If a violation was raised, the rule will be considered to have failed. If a violation wasn’t raised, it will be handled as a rule that processed successfully. This failure will be displayed in the results window.

Advanced techniques

Conditions can do things like make direct calls to the database, executing SQL in case the information isn’t available via our API to get additional information:

Rule conditions can also spool helpful information to the log for any particular reason, trouble-shooting, audits, reporting etc:

And even creating Fix SQL scripts to improve the condition of the database that violated a rule:

Other rule attributes

Now that we’ve covered the basic concepts of conditions, the rest is pretty easy. Rules have names, descriptions, and authors. A rule is designated a severity of high, medium or low. A rule must be designated with a type e.g. Table, to determine when it will fire as ApexSQL Enforce walks through its internal object model and encounters an object of that type.

An example of a type is a stored procedure parameter. As ApexSQL Enforce iterates through every element in its internal object model, each time it gets to a parameter of a store procedure, it will raise this event. If there is a rule written for stored procedure parameters, it will fire, and process the rule.

Putting it all together

Rules can be created by clicking “Add” and all of the key attributes can quickly be filled in:

Then the condition can be written via the internal IDE to isolate the particular meta data element to evaluate, query its state and return a result of whether it passed or not. If OK, do nothing, and ApexSQL Enforce will treat it as successfully processed. But if not OK, raise a violation, and ApexSQL Enforce will consider the rule to have failed.

If we not only want to raise a warning but also fix the problem, we can programmatically create the “Fix” as in Fix SQL, that can be spooled to a larger script, after the job is complete, that can be run to address any and all offending issues detected by ApexSQL Enforce, amenable to such fixes.