General
What systems are supported?
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
Is ApexSQL Enforce a stand-alone application or add-in for SQL Server Management Studio and or Visual Studio?
ApexSQL Enforce is a stand-alone application and it can be integrated in SQL Server Management Studio and Visual Studio.
What will ApexSQL Enforce console app produce as a return code if the application completes processing, but the job has “Failed”?
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
Can I query the database directly from an ApexSQL Enforce rule if I can’t get sufficient information from the API?
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();
Are Options set at the application or rulebase level?
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.
Can I create a Project to save a rulebase, rulebase selections and options along with a data source?
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.
Do you solicit, accept rules created by users, the community?
Yes, good community created rules will be added in the default rulebases and published in updates.
Do you have an "Example" rulebase, just to show how to accomplish simple things?
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
Rulebases
Can I import and/or export rules?
Yes, rules can be imported from the Rulebase import window
Will the ApexSQL rulebase be updated with new rules?
Yes, new and updated rulebases will be posted on the download page. Updates will be announced with “Product update alerts” on our blog
How can I tell if the rule base has been changed?
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).
How can I tell if there are any new, different rules when I import another rule base into an existing one?
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.
Rules
Can I customize existing rules?
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.
Can I create entirely new rules?
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.
What language are rules written in?
Currently rules are written in C# and VB.NET, but we also hope to add PowerShell support at some point in the future.
Is there an IDE to write rules in?
Yes, the rule Condition editor featuring syntax highlighting and auto-complete functionalities.
Are rules written against SMO?
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.
Is ApexSQL Enforce integrated into the ApexSQL DevOps toolkit?
Yes, ApexSQL Enforce can be used as an integrated element in ApexSQL DevOps toolkit when performing the “Review” step.
What can I do programmatically with rules?
Rules can be executed to raise violations which will cause that object to “Fail”, create Fix SQL scripts, programmatically write “Notes”.
Will rules be able to change my database?
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.
Can I save a configuration of a particular rulebase, data source, option combination?
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.
Can I spool out customized notes, additional info?
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.
Can you show an example of how to spool out Notes?
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”
What is the “Ignore” method and how will that processed rule be shown?
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)
Can I test an individual rule to see if it works?
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.
Processing
What data sources can I run this against?
Rules can be run against databases and static scripts. To run against a script, a database connection is required.
What sub-elements of the data sources are rules run against?
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.
Can I process more than one rulebase at a time, against a single data source?
No, only one rulebase can be opened and run against a single data source at the time.
Can I run ApexSQL Enforce unattended via a CLI?
Yes, ApexSQL Enforce includes a full command line interface aka CLI.
Can I see a history of previously executed jobs?
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.
Can I see when a database was last reviewed?
Yes, by setting up the trace logging to actively create log files.
What happens if a rule fails?
The application will show in the result objects that the rule failed and inform the user where the failure happened.
Fix SQL
Do the rules include “Fix SQL” to correct problems?
No, but we are planning to include this in the future ApexSQL Enforce versions.
Can you show an example of how to spool out FixSQL?
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.
Will the rules directly execute/change anything on my database?
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.
Can I run individual FixSQL snippets directly from the app, rule results grid?
Yes, Fix SQL snippets can be opened in the internal SQL script editor and can be run by selecting the Execute button.
Can I spool together all FixSQL scripts into a single script?
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.
Diagnostics
How can I quickly identify where in the script, object the error exists?
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.
Results
Does ApexSQL Enforce produce any results or reports?
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.
How can I measure the health of my processed database or scripts?
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.
How can the impact score be customized?
The impact score can be customized by changing the Impact score per severity level in the Options menu.
What are the different severity levels of a rule violation?
ApexSQL Enforce has three levels of severity: low, medium and high.
Can the impact levels of the severities be changed?
Yes, these values can be changed for each severity at the application level.
Can results be compared over time?
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.
How do I make a rule fail when it is processing an error?
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.