FAQs - ApexSQL Enforce

Currently, ApexSQL Enforce supports Microsoft® SQL Server™ only.

ApexSQL Enforce supports the following SQL Server™ versions: 7.0, 2000, and 2005.

Yes, ApexSQL Enforce has a command line interface that allows for unattended, scheduled operation. The output of an unattended operation can be an HTML report, XML export, a SQL script to fix detected violations or textual summaries and details which can be redirected to any file.

Yes, ApexSQL Enforce allows users to add new rules, modify existing ones, and create entirely new rulebases. Rules can also be based on existing ones (cloned). New rulebases can also be based on existing rulebases (have all objects except rules copied over), which can provide a familiar environment for easier writing of custom rules.

Yes, ApexSQL Enforce rules have a runtime property - FixSQL - that can be updated dynamically to create SQL statements that would fix the violation. FixSQL for all violations can be aggregated using Export FixSQL; this allows for fixing multiple violations in a single batch, with optional transaction handling.

Currently, ApexSQL Enforce only works with the properties and attributes of SQL objects. In the next major feature release however, the full script of objects will be exposed (e.g. WHERE clauses, JOINs, SELECT clauses).

We plan to post an Online Enforce Library that will allow for rulebase sharing in the next few months.

ApexSQL Enforce rules are written using .NET Framework 2.0, either C# or VB.NET.

Yes, the programming language is an attribute of an individual rule, not the rulebase. This allows for mixed language rulebases (VB.NET and C#). Rules can be imported easily regardless of the language they were written in.

ApexSQL Enforce loops through all of the rules in a rulebase and processes them one at a time against each SQL Server database object of the rule's defined object type. For example, if Rule X is defined for the Table object type, the rule will be run against all tables of the selected database(s).

The currently selected rule can be accessed via "this" in C# or "Me" in VB.NET. It can also be accessed via Enforce.ActiveRule.

The currently instantiated object can be accessed via this.ActiveObject (C#), Me.ActiveObject (VB.NET), ActiveObject, or Enforce.ActiveObject

These accessor keywords, as well as others, are available via the context menu of the rule editor. In general, typing Enforce. will enable the user to step through the IntelliPrompt hierarchy.

Yes. The full database Object Model is instantiated when a rulebase is processed. It can be accessed via several entry points. Starting at the top of the Object Model hierarchy, the database can be accessed using Enforce.Server.Databases. Via the ActiveObject, the user can walk up the model via ActiveObject.Parent or walk down via child collections. (e.g. ActiveObject.Columns).

Yes, the following properties can be updated during runtime - Impact score, FixSQL, Notes and Advice.

No. Only one violation can be raised per rule. If a rule (condition) can result to several policy violations, consider breaking it up into smaller rules or using dynamic properties such as Notes or Advice or logging each violation to the trace log manually.

Individual rules will evaluate to Pass, Fail or Error. Pass means no violation was raised. Fail means a violation was raised. Error means that the rule generated a runtime or compile error during processing.

Each rule evaluates a user-defined condition. If the condition produces a "negative" result (as determined by the user), then the rule will explicitly raise a violation as in the following example.

If (Me.ActiveObject.Triggers.Count > 1) Then

      Me.RaiseViolation()

Else

      'Do Nothing

End If

If no violation is raised, then the rule will pass by default.

Rulebase verdicts are rendered at the database level to either Pass or Fail depending on the user-defined thresholds for success/failure.

Results are rendered per database with either Pass or Fail verdicts. These verdicts are determined by Success/Failure Impact Score threshold, a total of the weighted severity (Impact Score) assigned to each rule violation or by a number of specific severity violations for the rulebase. Success/Failure thresholds can be customized via the Rulebase Properties dialog.

Yes, the failure threshold is primarily determined by the Impact Score threshold set at the rulebase level. An additional threshold condition can be set based on the number of violations of a certain severity. I.E. 10 Critical violations. So, if 10 Critical violations were detected the database would still "Fail" even if the Impact Score never exceeded the Impact Score failure threshold. If either condition is exceeded, Impact Score or maximum violations, then the database's verdict will be "Fail".

Default Impact scores are assigned to Severities. Default impact scores can be set for each Severity at the Rulebase level via the Severity Editor. Normally, violations with higher severities will have higher impact scores. Selecting a particular severity for a rule will ensure the rule inherits the assigned impact score as well. Impact scores are stored at the individual rule level so they can still be edited at the Rule level in both design-time (editing rule's properties) and run-time (setting the impact through rule's condition).

In Tools | Options | Resultset, the type of results (Fail, Error, Pass) can be included or excluded from the resultset. Only Fail and Error are included by default.

Yes. Select the “More Databases” tab in the “Select Databases” dialog.

Categories (including descriptions) as well as Severities (name, icon, and description) are stored at the Rulebase level – so changing the Category name will change it for all rules.

Note: Impact is stored at the rule level, so in order to change impact scores you must change them for each rule individually. To change the Impact Score for new rules, update the impact for the Severity and all subsequent rules will have the new Impact score.

Yes, selected rules can be exported/imported using Rules | Import/Export Rule(s). Selected rules or all of the rules in a rulebase can be imported/exported to an XML file which can then be easily distributed or reused in the creation of compilation rulebases.

Rules can be "cloned" by selecting Clone Rule from the Context Menu; this allows for the creation of a duplicate rule within the same rulebase. Note: The Name must be changed as it is unique.

Here's a code snippet that demonstrates how to look for special characters in the name of a column:

char[] tblChars = ActiveObject.Name.ToCharArray();

foreach(char c in tblChars)

{

      if(c >= 'a' && c <= 'z')

      {

            continue;

      }

      else if(c >= 'A' && c <= 'Z')

      {

            continue;

      }

      else if(c == '_')

      {

            continue;

      }

      else

      {

            RaiseViolation(string.Format("Invalid char: {0}", c));

      }

}

Please see the "Avoid special characters when naming columns" rule in the Best Practices rulebase for a complete example.

Here's a code snippet that demonstrates how to match an SQL Object's name against the list of reserved words:

if(Server.ReservedWords.Contains(ActiveObject.Name))

{

      RaiseViolation("'" + ActiveObject.Name + "' is a reserved word.");

}

Please see the "Avoid using reserved words for naming views" rule in the Best Practices rulebase for a complete example.

Here is a code snippet that demonstrates this for Table objects:

string tableName = this.ActiveObject.Name;

if(tableName.StartsWith("tbl"))

{

      this.RaiseViolation("Table name should not use the 'tbl' prefix");

}

if(!tableName.EndsWith("s"))

{

      this.RaiseViolation("Table name should use the plural format");

}

if(tableName == tableName.ToUpper())

{

      this.RaiseViolation("Table name should not be in CAPS.");

}

Please see the "How to write naming convention rules" rule in the provided Tutorial rulebase for a more complete example. Note that specific naming conventions apply to each SQL object - Server, View, Table, etc - so you should consider creating separate rules for each of these objects.

Currently, rules can only exist for one Object Type. To duplicate the rule for another object type, simply clone it and then change the object type. Please see the FAQ on Cloning.

ApexSQL Enforce includes both a "Best Practices" and a "Tutorial" rulebase (in both C# and VB.NET). The Tutorial rulebase is a good place to start. Examine each rule in sequence of Rule number, I.E. 1 then 2 etc. These examples will introduce the basic functionality of ApexSQL Enforce and demonstrate increasingly complex and sophisticated usage of this software application.

The default location for all rulebases shipped with ApexSQL Enforce is:

C:\Documents and Settings\<user name>\My Documents\ApexSQL\ApexSQLEnforce\

Current example rulebases are "Tutorial CS.axrb", "Tutorial VBNET.axrb"

Yes, ApexSQL Enforce includes a Best Practices rulebase written in collaboration with Micrsoft SQL Server MVPs. It can be found in the following example rule base:

C:\Documents and Settings\<user name>\My Documents\ApexSQL\ApexSQLEnforce\

Current "Best Practices" rulebases are "Best Practices CS.axrb" and "Best Practices VBNET.axrb"

Rules can be "Ignored" via the command "Ignore", Enforce.Ignore and/or ActiveRule.Ignore. This will suppress any result - pass or failure. Ignored results won't show up in the resultset.

System.dll, System.Xml.dll, ApexSQL.Enforce.SqlMetaData.dll.

Please see the rule "How to use elements from automatically included namespaces" in the provided Tutorial rulebase for a usage example.

System, System.Collections, System.Collections.Specialized, System.Text, System.Text.RegularExpressions, ApexSql.Enforce.SqlMetaData, ApexSql.Enforce.SqlServer, ApexSql.Enforce.SqlServer.Broker and for VB.NET rules only, Microsoft.VisualBasic.

Please see the rule "How to use elements from automatically included namespaces" in the provided Tutorial rulebase for a usage example.

Rule compilation is a translation of rule condition from programming language (C# or VB.NET) into code. This code can be executed by the computer to evaluate the rule (pass or fail). Compiler errors that are returned by compiler indicate that the rule condition can't be translated into executable code.

Yes, ApexSQL Enforce has sophisticated tracing functionality that allows for user-defined text "traces" and event logging.

Here are some code examples of using the tracing functionality:

// Write a dynamically set text in the trace log:

this.LogTrace("Custom trace log text for" + "'" + this.ActiveObject.Name + "'");

// Dump current object's data in the trace log:

Enforce.DumpObject(this.ActiveObject);

Please see the "How to write to the trace log" rule in the provided Tutorial rulebase or the "Max worker thread set to a non-default value" rule in the provided Best Practices rulebase for detailed usage examples.

Avoid sub-object level rules like columns, parameters, etc. Rules at this level must be processed for every column for every table. In many cases, the same rule can be accomplished, for example, by looping through the columns in a table level rule.

Debugging is not currently supported but is planned for future versions. There are however detailed information on compile errors during the design time and full stack-traces of run-time errors. Conditions can be written to use the Enforce.DumpObject() method to output all of a problematic object's properties to the trace log. Please see the "How to write to the trace log" rule in the provided Tutorial rulebase for a usage example.