Connection Details for SQL Azure | Spotlight Enterprise Documentation

Monitor SQL Azure by supplying the following connection details to Spotlight.

How to enter / edit connection details

Use a Spotlight Client to enter / edit connection details.

From the Spotlight Client

Click Configure | Connections.
Double click Add new connection.
Fill in the connection details.

Connection details

Address

The URL used to link to the SQL Azure database.

Display Name

The display name of the SQL Azure connection. The display name is used in all displays including the connection tree, connection lists, reports, tables, grids and charts, alarms, alarm actions, alarm descriptions, configuration files, template files, Heatmap and drilldowns.

The display name can be up to 255 characters long. Use letters and numbers. Do not use special characters like % and spaces in the display name. The display name is not case sensitive. The display name must be unique; no two Spotlight connections to SQL Azure can have the same display name.

If you don’t create a display name then Spotlight uses the SQL Azure address for display purposes.

Authentication

Specify the authentication for Spotlight to use to connect to the SQL Azure database.

Select the SQL Server Authentication from the Authentication field. In the User field, enter a valid user name that enables Spotlight to connect to the SQL Azure database. In the Password field, enter a password to connect the specified user to the SQL Azure database.

Select the Microsoft Entra Password from the Authentication field and fill in the User and Password fields. Ensure the user is allocated as an admin user for SQL Azure database active directory.

Select the Microsoft Entra MFA from the Authentication field and fill User and Application ID fields. Ensure the user is allocated as an admin user for SQL Azure database active directory and Application ID is provided for SQL Azure database connection.

Select the Microsoft Entra Integrated from the Authentication field. User and Password fields are disabled for this authentication mode and will be using Dignostic Server credentials. Ensure the user is allocated as an admin user for SQL Azure database active directory and Spotlight Diagnostic Server is logged in using the same user credentials.

Note: To connect to Azure SQL Managed Instance and Azure SQL Database together from one Diagnostic Server using Microsoft Entra Integrated authentication, see Configure Connections

Database

The name of the SQL Azure database.

Use Extended Events

Selected

The Spotlight Diagnostic Server will use Extended Events to collect data from the SQL Azure instance. The data is used by:

SQL Azure | Workload Analysis Drilldown
SQL Azure | Wait Events Drilldown
Deadlock checks: SQL Azure | SQL Activity Drilldown, Locks - Deadlocks Alarm.

If you select to use Extended Events (following a period of time when the use of Extended Events was deselected) the SQL Azure | Workload Analysis drilldown and SQL Azure | Wait Events drilldown may take a few minutes to repopulate with data.

Min Duration

The starting minimum duration to use to collect for Wait Events and Workload Analysis. Local Extended Event is used to customize/configure minimum duration per connection. This is optional field that can be set for any SQL Azure connection by navigating from SQL Azure connection | Properties | Details | Advanced Options » | Min duration.

NOTES

When the Min Duration parameter under local and global extended events have either same or different values, the local extended event value is used by the respective connection and other SQL Azure connections(for which local extended event value are not set) uses global extended event value.
When the Min Duration parameter under Local Extended Event is left blank, SQL Azure connection uses the Global Extended Event specified in the Spotlight Option settings.

Not Selected

The Spotlight Diagnostic Server will use SQL Azure Trace to collect data for deadlock checks. The Spotlight Diagnostic Server will NOT collect data for the SQL Azure | Workload Analysis drilldown and SQL Azure | Wait Events drilldown.

If you set this value after the connection to the SQL Azure is established then the change will not show up immediately on the user interface as the Workload Analysis drilldown and Wait Events drilldown will continue to show historical data till no data is available.

Note: The setting for Use Extended Events can be set collectively for all SQL Azure on the Spotlight Diagnostic Server from Configure the use of Extended Events.

Note: Collection ‘Create Event Session’ failure can occur when the total memory allocated to Azure SQL Extended Event sessions exceeds the allowed limit.
As per Microsoft’s official guidance it is recommended to configure 1 MB or less of memory per session (extended event configuration).
In the observed case, multiple Spotlight connections created Extended Event sessions, and several old stopped sessions remained in the database. Although inactive, these sessions continued to reserve memory, causing the overall memory limit to be exceeded and preventing new sessions from being created.
Immediate Resolution: Remove unused stopped Extended Event sessions to free memory. Currently, the Azure SQL Database implementation does not automatically clean up stopped Extended Event sessions.

Obscure string literals in SQL text and parameters in query plans

Selected

Placeholder data will replace string literals in all displays of the SQL Statement and Query Plan. This protects privacy information that may be contained in those literals.

Large numbers (greater than 99,999,999) are replaced with 987654321.

Not Selected

Large numbers and string literals in SQL Statements and Query Plans are displayed as is.

Exclude Spotlight sessions from Wait Events and Workload Analysis

When selected Spotlight sessions are excluded from the Wait Events and Workload Analysis drilldowns.

Store SQL text and query plans in playback

When Selected, Spotlight stores SQL query statements and plans in the Playback Database for this SQL Azure connection. This data will be available during playback of events and data collected in the recent past in the SQL Activity drilldown | Sessions page and the SQL Activity drilldown | Query Execution Statistics page.

Note: Data will not be stored if the Spotlight Diagnostic Server setting has been disabled, see Configure | Diagnostic Server | Store SQL Text and Query Plans in Playback.

Connection Failure Properties

Set the connection timeout and the number of times Spotlight should attempt to connect to the SQL Azure instance before raising the Connection Failure alarm.

Alarms template

Select the alarms configuration template to apply to this connection.

Select from any of the SQL Azure configuration templates that you have created through Configure Alarms or select Factory Settings Template to apply the Factory Settings shipped with Spotlight.

Note: The template will apply to the Windows host of the SQL Azure instance if identified. The host is identified in the Connection section of the Connection Details page.

Tip: The alarms template option is applicable only when creating a new SQL Azure connection. To apply a template to an existing SQL Azure connection, see Configuration Templates.

Test the connection

On entering / modifying connection details in the Spotlight Client, click Test to test the connection.

Troubleshooting

Release Notes

Check the Spotlight Enterprise Release Notes for known issues.

Tags: