SQL Assessment API client sample

Overview

This repository contains an example C# application using SQL Assessment API. The application uses popular SQL Management Objects (SMO) library. WHile it was not required to invoke assessment, SMO gives a convenient object-oriented API for managing SQL Server objects. See Service based SQL Assessment for an example of non-SMO application.

To use SQL Assessment API this sample project references Microsoft.SqlServer.SqlManagementObjects and Microsoft.SqlServer.Assessment Nuget packages.

SQL Assessment workflow

A typical SQL Assessment workflow consists of three stages:

1. Establish a connection to a SQL Server and select target instance or database (Microsoft.Data.SqlClient, SQL Management Objects, SQL Tools Service, PowerShell).

2. (Optional) Create SQL Assessment check list. A default check list is used if this step was skipped.

3. Invoke assessment on the target object selected at step 1.

Using SQL Assessment with SQL Management Objects

Establish a connection and select a SQL Server object with SMO

SQL Management Objects is a handy tool for accessing and managing SQL SErver objects. This sample application connects to a local SQL Server instance with a short statement:

var target = new Server();

To assess a database replace this line with the following snippet:

var target = new Server().Databases["MyDatabase"];

See SMO documentation for more details on connecting to a server or a database.

Invoke assessment with default check list

SQL Assessment asynchronous code returns a list of SQL Assessment results. Each result can be an assessment note, warning, or error. Every assessment result contains a message to the user with a recommendation. Each assessment note is associated to the corresponding check from the list.

List<IAssessmentResult> results = await target.GetAssessmentResultsList();

Invoke assessment with selected checks

SQL Assessment API gives a collection of checks currently available for given target. Selected checks may be passed to GetAssessmentResultsList.

IEnumerable<ICheck> availableChecks = target.GetAssessmentItems();

Select checks to be run.

var checklist = availableChecks.First(5);
var results = await target.GetAssessmentResultsList(checklist);

Select checks by category

SQL Assessment check may be tagged with one or more category names. Checks from default check list always have "DefaultRuleset" tag and at least one area tag. "DefaultRuleset" denotes a check produced by default ruleset supplied with SQL Assessment API. Area tags may be "Performance", "Security", "Memory", "Deprecated", "Storage", etc.

var checklist = target.GetAssessmentItems("Performance", "Security");
var results = await target.GetAssessmentResultsList(checklist);

This is equivalent to the following code:

var results = await target.GetAssessmentResultsList("Performance", "Security");

Custom rule sets

Checks are generated by a SQL Assessment engine for every target. The engine uses rules from one or more rule sets. Rule sets are collected on a stack. A rule set may override rules from an underlying rule set. A ruleset may be constructed with a C# code, but we recommend using declarative JSON format. For more details on rule set files see SQL Assessment API GitHub page.

using (var reader = File.OpenText("my ruleset.json"))
{
    SqlAssessmentExtensions.Engine.PushRuleFactoryJson(reader);
}

Using SQL Tools Service

SQL Tools Service is a JSON-RPC service over stdio. SQL assessment takes the same three steps: connect, select checks, run.

In the following examples adjust Content-Length value according to actual JSON length including CR or CRLF line endings.

Connect with SQL Tools Service

Content-Length:267

{
    "jsonrpc": "2.0",
    "id": "12",
    "method": "connection/connect",
    "params": {
        "ownerUri": "my connection",
        "connection": {
            "serverName": "(local)",
            "authenticationType": "Integrated"
        }
    }
}

See SQL Tools Service documentation for more details on connecting to a server or a database.

Get available checks

Use targetType to select target type:

1. SQL Server instance.

2. SQL Server database. In this case provide database name while connecting to the target.

Content-Length:181

{
    "jsonrpc": "2.0",
    "id": "12",
    "method": "assessment/getAssessmentItems",
    "params": {
        "targetType": 1,
        "ownerUri": "my connection"
    }
}

Sample output:

{
    "jsonrpc": "2.0",
    "id": "12",
    "result": {
        "success": true,
        "errorMessage": null,
        "items": [
            {
                "rulesetVersion": "1.0.280",
                "rulesetName": "Microsoft ruleset",
                "targetType": 1,
                "targetName": "MYSERVER",
                "checkId": "TF174",
                "tags": [
                    "DefaultRuleset",
                    "TraceFlag",
                    "Memory",
                    "Performance"
                ],
                "displayName": "TF 174 increases the plan cache bucket count",
                "description": "Trace Flag 174 increases the SQL Server ...",
                "helpLink": "https://docs.microsoft.com/sql/t-sql/ ...",
                "level": "Information"
            },

            ...

        ]
    }
}

Invoke SQL Assessment with SQL Tools Service

Use the same targetType.

Content-Length:169

{
    "jsonrpc": "2.0",
    "id": "12",
    "method": "assessment/invoke",
    "params": {
        "targetType": 1,
        "ownerUri": "my connection"
    }
}

Sample output:

{
    "jsonrpc": "2.0",
    "id": "12",
    "result": {
        "success": true,
        "errorMessage": null,
        "items": [
            {
                "message": "Enable trace flag 834 to use large-page allocations to improve analytical and data warehousing workloads",
                "kind": 0,
                "timestamp": "2020-11-09T22:46:36.5529014+03:00",
                "rulesetVersion": "1.0.280",
                "rulesetName": "Microsoft ruleset",
                "targetType": 1,
                "targetName": "MYSERVER",
                "checkId": "TF834",
                "tags": [
                    "DefaultRuleset",
                    "TraceFlag",
                    "Performance",
                    "Memory",
                    "ColumnStore"
                ],
                "displayName": "TF 834 enables large-page allocations",
                "description": "Trace Flag 834 causes the server ...",
                "helpLink": "https://support.microsoft.com/kb/3210239",
                "level": "Information"
            },

            ...
        ]
    }
}