ApexSQL Enforce

SQL Server version 2005 and above, Azure SQL Database, Amazon RDS for SQL Server are supported. This information is available in our supported software page

ApexSQL Enforce is a stand-alone application and it can be integrated in SQL Server Management Studio and Visual Studio.

ApexSQL Enforce will produce a variety of return codes e.g.: -1 for Pass and 102 for Fail, in the case that the rulebase processed a database that resulted in failure due to a high impact score. The full list of return codes can be found in this article

Yes, conditions can make direct calls to the database and execute SQL statements. For example:

Var SqlCommand = Enforce. Server. Connection.CreateCommand();

SqlCommand.CommandText = “SELECT * FROM Customers”;
SqlCommand.CommandType =CommandType.Text;

var reader = SqlCommand.ExecuteReader();

Options like impact score by severity and resultset type options like: including passed, failed and errors on rules, are set on application level. However, failure thresholds, rulebase properties and rule selections are saved on rulebase level.

There are no Projects in ApexSQL Enforce, however, rulebases can be created, saved and edited. Rulebases include rulebase selection, rule categories and failure threshold values.

Yes, good community created rules will be added in the default rulebases and published in updates.

Yes, ApexSQL Enforce comes with preinstalled rulebase named “ApexSQL rulebase (C#)”, that show basic examples of writing rules and give brief explanation on what results will be produced. These rulebases can be found under the default rulebase directory:

%local disc%:\Users\%user%\Documents\ApexSQL\ApexSQL Enforce\Rulebases

Yes, rules can be imported from the Rulebase import window

Yes, new and updated rulebases will be posted on the download page. Updates will be announced with “Product update alerts” on our blog

Rulebases have version numbers. Check the currently used rulebase number to make sure you are using the latest version. Also, rule level changes will be highlighted in the import dialog (see next).

When importing a new rulebase, ApexSQL Enforce will compare the new rulebase with the rulebase that is currently opened. New and updated rules are marked with green and blue boxes, respectively. Changed rule attributes and line level changes in the rule condition are also highlighted when compared to their previous version.

Yes. To or customize an existing rule, double click the rule title in the Rules list, or click the Edit button in the Main ribbon menu. Once clicked, the rule editor will appear, which allows you to fully customize the selected rule.

Yes, by clicking the Add button in the Main ribbon menu the New rule window will appear with all the necessary tools for creating a new rule.

Currently rules are written in C# and VB.NET, but we also hope to add PowerShell support at some point in the future.

Yes, the rule Condition editor featuring syntax highlighting and auto-complete functionalities.

No, although SMO may be used in some cases internally, rules in ApexSQL Enforce are written against our proprietary object model that renders faster and includes more detailed metadata.

Yes, ApexSQL Enforce can be used as an integrated element in ApexSQL DevOps toolkit when performing the “Review” step.

Rules can be executed to raise violations which will cause that object to “Fail”, create Fix SQL scripts, programmatically write “Notes”.

No, rules from the ApexSQL rulebase won’t change the database and there are safeguards to prevent rules from impacting a database. However, some rules can create Fix SQL scripts that can be run separately to fix violations. Also, rules could be written in such a way that could change data. For this reason, it is important to import rules only from verified sources e.g. ApexSQL and to physically inspect all new rules before running them.

Failure thresholds, rules themselves including properties like categories, and individual rule selections can be saved in the Rulebase itself. Other options are set globally for every rulebase.

Yes, you can write notes that will include script statements and be executed as part of the rule. Notes will be available in the Notes tab in the Results table and can be exported with the rest of the results.

This is a simple example of how to include dynamic notes in a rule condition:

this.Notes = "Sample Notes text set at run time";
this.Notes = this.Notes.Replace("text", "string");

The results will look like the following:

“Sample Notes string set at run time”

Ignore is a statement in the Condition editor that allows exceptions to the rule by excluding objects with certain attributes. (eg.: ignore objects with a name longer than 7 characters)

Yes, by clicking Run in the Condition editor the Process rulebase window will appear, where you can select a database to test your rule against its target object type. When the process is done, results will be shown in the Results summary dialog and in the Resultset tab.

Rules can be run against databases and static scripts. To run against a script, a database connection is required.

Rules can be run against all database objects including: tables, columns, indexes, primary and foreign keys, views, stored procedures, users, functions. Within script-based objects and scripts themselves, rules can be run against various sub-elements in the script/DDL like a where clause.

No, only one rulebase can be opened and run against a single data source at the time.

Yes, ApexSQL Enforce includes a full command line interface aka CLI.

Previously executed job can be reviewed by using Trace logging. In the options window under Trace logging, you can setup what will be traced and stored in log files. To create a repository of previously run jobs, the XML output could be exported to a database table and compared over time.

Yes, by setting up the trace logging to actively create log files.

The application will show in the result objects that the rule failed and inform the user where the failure happened.

No, but we are planning to include this in the future ApexSQL Enforce versions.

This is a simple example of how to include FixSQL in a rule condition:

if(!ActiveObject.IsEnabled)
{
FixSQL = string.Format("ALTER TABLE {0} ENABLE TRIGGER {1}",
ActiveObject.Parent.QualifiedName, ActiveObject.Name);
RaiseViolation();
}

This script is set to check if the current trigger is disabled and enables it.

Not from verified rulebases but rules are powerful enough to execute SQL directly which could result in changed data from rules users customized themselves or import from others. ApexSQL Enforce does contain safeguards that prevent that from happening but you should always review rules before executing them.

Yes, Fix SQL snippets can be opened in the internal SQL script editor and can be run by selecting the Execute button.

Yes. Select a resultset tab and click Export to FixSQL. In the export options select All. After that, the SQL script editor will open containing all FixSQL snippets in a single script.

Errors are shown in the Line:Col column in the Resultset table and are highlighted in the script viewer when you double click on a single result in the Resultset table.

ApexSQL Enforce produces result summaries after each run. The result set contains detailed, rule/object level results and violations, Fix SQL queries and Notes. Results can be exported as Fix SQL script files, HTML reports and XML exports.

Data source health can be measured by the Impact score which can be seen in the Result summary. Higher impact score indicates lower overall health of the reviewed data source.

The impact score can be customized by changing the Impact score per severity level in the Options menu.

ApexSQL Enforce has three levels of severity: low, medium and high.

Yes, these values can be changed for each severity at the application level.

Results can be saved to a database by processing the XML file, and compared over time. Currently, ApexSQL Enforce doesn’t feature a repository to directly track results over time.

You can define RaiserrorStatements that will rise violations. For example:

if (ActiveObject.RaiserrorStatements.Where(stmt => stmt.Severity > 18 && !stmt.Definition.NormalizeSpaces().ContainsIgnoreCase("with log")).Count()>0)
RaiseViolation();

In this example, the script will raise violation if the severity is above 18 and requires “with log” clause.