This video shows how to start using ApexSQL Enforce. Specifically, how to create a new rulebase, based on the existing one, shipped along with the product installation.
Besides that, the video shows how to manage rules and categories, how to run rules and how to review results in general, as well as a result for each rule individually. In addition to this, this video shows how to create a new rule including rule categorization and specifying a condition.
Transcript
Hello and welcome to this ApexSQL Enforce general overview video. In this video, we’ll show how to use ApexSQL Enforce, including loading a new rule base, editing and configuring rules, processing a rulebase and evaluating results.
ApexSQL Enforce is a SQL developer tool used to create, maintain and run a set of best practice code rules against a SQL server database or a SQL script. The process of executing rules can be set to run unattended via the command line interface. A set of pre-defined rules is included with the product but rules can be updated and customized. Entirely new rules can be written as well.
Once ApexSQL Enforce is started, we can start with a new rulebase. Clicking the New button in the Rulebase group of the main ribbon initiates the New rulebase dialog:
Besides a name and a description for our new rulebase, we can create a blank rulebase or use an existing rulebase as a starting point. Choosing to create a blank rulebase will create a new rulebase with no rules or categories. For the purpose of this video, we’ll choose to create a new rulebase, based on the existing one, by checking the Create rulebase from existing option:
Since ApexSQL Enforce comes with pre-written rulebases, we are going to choose from one of these, by using the Browse button:
This will open a location where ApexSQL rulebases are located. At the time this video was created, there are currently four rulebases included in the installation. These are two examples called Tutorial and ApexSQL, and there are two variants of these, depending on the preferred programming language, C# or VB.net. Let’s create a new rulebase based on the ApexSQL example:
We’ll choose to include all rules from the ApexSQL example by selecting the Include rules checkbox:
Besides rules, when creating a new rulebase from the existing one, categories that are specified in the existing rulebase will be imported as well. In this case, for the ApexSQL rulebase we have 10 categories:
In the case of creating a new rulebase from an existing one, without checking the Import rules option, no rules will be imported, but categories from the selected rulebase will be imported along with all the underlying category descriptions. In any case, categories, as well as imported rules can always be modified later.
Besides importing rules from existing samples, and creating new rules using the Import button, rules from other rulebase can be imported into the current rulebase. Rules from the current rulebase will be compared with rules from the rulebase selected to be imported, so the user can pick specific rules to import:
Comparing rules shows line by line differences visually, in the lower section of the Rulebase import window:
Once we have a rulebase with the appropriate rules loaded, let’s review what else we can configure, before running the rulebase against a database.
Using the Edit button, the Edit rulebase dialog appears offering options for modifying name or adding a description for the current rulebase. Particularly important is the Threshold tab. We can configure various values that will abort a review in case the violation, impact or errors are greater than the specified ceiling.
Similar to aborting the process of executing rules, by specifying a value in the Impact field, running rules will be finished, but marked as failed:
Another part of the rulebase is the Category configuration. Categories are used to group rules and rule results. The Categories button from the main ribbon displays the Categories dialog. This is where all of the categories can be reviewed along with the number of rules currently assigned to that particular category and a description of the category:
Category management is straightforward so creating new categories or editing of existing ones is simple. It is important to mention that a category cannot be deleted if it contains at least one rule:
Now that we have covered categories, let’s switch to rules. Once loaded, all rules appear in the main grid, grouped by category by default:
Rule management is available under the Rules group in the main ribbon where rules can be added, deleted or modified. If we need a new rule similar to an existing one, we can clone one rule and make necessary changes instead of creating a rule from scratch:
The same set of functions is available from the main grid context menu:
To run a rule, individually, or a group of rules, simply check them in the main grid and use either the Run button from the main ribbon, the Run command or the Run checked rules command from the context menu:
We want to run all the rules for the purpose of this video, so we’ll choose the Check all command from the context menu to check all the rules:
Using one of the available options, we’ll run all the rules from the grid. Before rules are executed, we’ll need to provide a connection to a database or a SQL script if we want to check an individual SQL script:
After all rules are executed, the Result summary dialog appears, with general information about passed and failed rules:
We can review information about the rule base result, whether it passed or failed, rulebase result summary and details as well as specifics on violations and processed rules and objects. In this specific case, the impact score was much higher than 10, which was our failure threshold, which caused this to fail. Almost 90% of rules passed, 10% failed. Rules that failed were mostly with a low severity.
Besides this general overview of the executed rules, we can check each and every executed rule result.
In order to show only failed rules for easier review, we could set this by unchecking the Passed rules in the Options dialog:
However, since this option needs to be configured before running rules, we will simply group results by the result column dragging it to the grouping area:
This will give us a nice overview of all the failed rules:
Let’s investigate one of the failed rules in detail. We can do that by highlighting it, which shows additional result details:
Under the Results tab, we can find some general details about the rule and an object that it was processed against.
Switching to the Description tab, we can find out additional information and context about the rule including links to the additional content:
The Violation tab shown if the violation is Low, Medium or High as well as an advice on how to fix the problem:
One of the important part of the Results section is the Fix SQL tab that contains the actual SQL script that can be used to fix the problem that caused a rule to fail on the first place:
In order to avoid using each every Fix SQL script to fix a problem, we can export all scripts into a single SQL file that can be executed. We’ll do this by using the Export button and choosing the Export to FixSQL option:
This can be done for all results or for the checked ones. For the purpose of this video, we want all to be exported:
This combines all the Fix SQL scripts into a single one that can be reviewed in the integrated editor and edited if there is a need. Once we are satisfied with the script, we can execute it against a selected database directly from the integrated editor, using the Execute button:
Changes that should be applied to a database are wrapped up in transactions, so either everything is applied in case of no errors, or none of the changes are applied.
Although the best practices rulebase that is shipped with ApexSQL Enforce is very comprehensive, you may still want to create your own rules. So, the next step is to check how to create a new rule. We’ll click the Add button to initiate the New rule dialog:
Rule ID will be automatically generated, so we can specify a name and a description for a new rule. Next, we can choose if a rule will run against database objects, like tables, or T-SQL code. For the selected rule type, we can pick a specific item from the drop-down list:
Each rule must be assigned to specific type, for example, a table:
Here we’ll also set the severity as high, medium or low
Under the Condition tab, the actual code that runs the rule needs to be written:
Here we can see that if the active object, which is a primary key based on what we specified as the type, is not clustered, then we will raise a violation. This will fail the rule for this object and we’ll be able to note the details when reviewing the results.
In this video, we have shown how to create a new best practices rulebase from an existing one, how to manage rules and categories as well as how to configure failure and/or abort thresholds for rulebases. We also reviewed how to edit individual rules and also review processing results and details.
Thanks for watching. For more information, please visit apexsql.com
