Getting started with ApexSQL Enforce
When you first start up ApexSQL Enforce you see the Gui shown above. If you already use other tools from ApexSQL you feel immediately familiar with this application. If this, however, is your first ApexSQL tool you are confronted with a typical Windows application that is concise and designed just to serve its purpose.
The purpose of ApexSQL Enforce is to check SQL Server databases against a customizable set of rules and see if these databases conform with these rules or not. In cases where the application detects that a certain rule is not followed or violated, it can create and/or execute T-SQL statements that rectify the detected violation and bring a database back into compliance with the defined rules.
Basic terminology
Since ApexSQL Enforce is, to the best of my knowledge, the first application of its kind, it is appropriate at this point to give a short introduction into the terminology that this tool uses.
Rules are “the most elemental” objects in ApexSQL Enforce. They describe what should be checked in the database. An example of such a rule is, for example, “Table should have a Primary Key” or “Replace rules with check constraints”. Rules are organized in rulebases, which are basically XML files in a certain format. Within one rulebase rules can be grouped by object types and categories. Examples of object types are Database, Table, or Column. They are predefined by ApexSQL Enforce. Categories are, for example, Naming Convention, Database Modeling, or Security. They are fully customizable to your own needs. Along with each rule you can define a severity that serves as a visual indication of the level of action that you need to take when that rule is violated. Severities are also fully customizable. The shipped severities range from “Tip” to “Critical”.
Now you should have a basic overview of the meaning of the terms I am going to refer to quite often from this point on.
Rules and rulebases
You don’t need to create a new rulebase prior to using this program because ApexSQL Enforce already ships with several sample rulebases. One of these rulebases the “Best Practices” rulebases will be used throughout this review. As its name indicates, this rulebase contains many rules that fall indeed into the category of best practices in SQL Server development and configuration.
After opening a rulebase you see all contained rules in the main grid. This main grid is basically the center of the whole application. From here you can start all actions. More on these actions in just a moment. Let me first introduce you to the second main part of the ApexSQL Enforce Gui: the Rulebase explorer.
This rulebase explorer is a treeview that offers a different presentation of the rules in a rulebase and comes in handy in certain situations. Just like the main grid you can also run rules against a database from the explorer.
Let’s come back to rules and examine one rule closer. I’ve picked the “Check if primary key can be reduced to only one column” rule as my example.
By double-clicking this rule you open up the rule editor as shown above. As you can see on the first tab you can edit properties such as name, description, object type, category, and language. Language? What does this mean?
ApexSQL Enforce allows you to check SQL Server databases for the violation of certain conditions. This is done by means of a proprietary object model in ApexSQL Enforce. To access this object model you write small blocks of .Net code which then in turn is run against the database. This .Net code can either be written in C# or VB.Net.
This .Net code is displayed on the condition tab. In the above screenshot you can see the condition that checks if a primary key consists of more than one column.
In order to enter and/or edit conditions you click on the “Edit…” button. This opens up the condition editor which is a full-blown code editor.
As you can see is this editor equiped with the intelliprompt technology that helps you to access the methods and properties of the ApexSQL Enforce object model easily.
Using this editor you can write and compile your code snippets.
While my sample rule is expressed via a fairly simple condition, the above screenshot is an example of a more complex condition.
On the violation tab you edit the properties of a rule that come into play when that particular rule is violated.
Most prominent property here is the severity. This property serves as an indicator for the importance of a given rule. As you can see the shipped severities range from “Tip” to “Critical”. While these severities cover almost all commonly used levels, you can fully customize them to suit your individual needs.
On the last tab of the rule editor, you can specify for which SQL Server versions that rule is applicable. If you don’t want this rule to be applied against certain SQL Server versions, just uncheck these versions and ApexSQL Enforce will not apply this rule when run against such a database. As you can see from this screenshot is ApexSQL Enforce ready for SQL Server 2008 in its CTP version now.