Hello and welcome to this ApexSQL Enforce general overview video. 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 SQL script. The process of executing rules can be set to run unattended via the command line interface.
A set of predefined rules is included with the product, but rules can be updated and customized. Entirely new rules can be written as well. Once ApexSQL Enforce has 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 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 prewritten 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 Apex SQL, and there are two variants of these depending on the preferred programming language-- C-SHARP or VB.net.
Let's create a new rulebase 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 rulebases 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 specified 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 of a violation, impact, or errors that 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 useful 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 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, will run all the rules from the grid. Before the rules are executed, we'll need to provide a connection to a database or 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 past and failed rules. We can review information about the rulebase results, whether it passed or failed, rulebase results 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 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 Past 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