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.
Specify the connect string used to link to the Azure SQL Managed instance (that is, the Server Name, Server Instance Name, or IP address). You may also need to specify the port number, if required.
The display name of the Azure SQL Managed instance 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 Azure SQL Managed instance can have the same display name.
If you don’t create a display name then Spotlight uses the instance address for display purposes.
Specify the authentication for Spotlight to use to connect to the Azure SQL Managed instance.
Select the SQL Server Authentication from the Authentication field and fill in the User and Password fields. Since this user is a database user, ensure that the user has sufficient account permissions to retrieve performance data from the Azure SQL Managed instance. Typically the account will be a member of the sysadmin server role. It could be an Azure SQL Managed Instance login (such as ‘sa’). If this is not feasible in your environment, see Grant Account Permissions.
The connection will fail if the account permissions are insufficient to allow Spotlight to collect the data it needs.
Use Extended Events
The Spotlight Diagnostic Server will use Extended Events to collect data from the Azure SQL Managed instance. The data is used by:
- SQL Server | Workload Analysis Drilldown
- SQL Server | Wait Events Drilldown
- Deadlock checks: SQL Server | 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 Server | Workload Analysis drilldown and SQL Server | Wait Events drilldown may take a few minutes to repopulate with data.
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 Azure SQL Managed Instance connection by navigating from Azure SQL Managed Instance connection | Properties | Details | Advanced Options » | Min duration.
- 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 Azure SQL Managed Instance 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, Azure SQL Managed Instance connection uses the Global Extended Event specified in the Spotlight Option settings.
The Spotlight Diagnostic Server will use SQL Server Trace to collect data for deadlock checks. The Spotlight Diagnostic Server will NOT collect data for the SQL Server | Workload Analysis drilldown and SQL Server | Wait Events drilldown.
If you set this value after the connection to the Azure SQL Managed Instance 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.
Obscure string literals in SQL text and parameters in query plans
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.
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 Azure SQL Managed Instance 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.
Connection Failure Properties
Set the connection timeout and the number of times Spotlight should attempt to connect to the Azure SQL Managed instance before raising the Connection Failure alarm.
Select the alarms configuration template to apply to this connection.
Select from any of the Azure SQL Managed Instance configuration templates that you have created through Configure Alarms or select Factory Settings Template to apply the Factory Settings shipped with Spotlight.
Test the connection
On entering / modifying connection details in the Spotlight Client, click Test to test the connection.
Connection to the Azure SQL Managed instance
If Spotlight cannot connect to the Azure SQL Managed instance then use another tool such as Microsoft’s SQL Server Management Studio or sqlcmd to connect to the Azure SQL Managed instance. Is the issue with Spotlight’s ability to connect to the with any any/every tool’s ability to connect to the Azure SQL Managed instance?
Check the Spotlight Enterprise Release Notes for known issues.