Connection Details for Amazon RDS for SQL Server | Spotlight Enterprise Documentation

Monitor SQL Server 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.

Tip: Spotlight can add multiple Amazon RDS for SQL Server instances via discovery or by importing details from a file. See the Connection Discovery Wizard.

Connection details

Address

Specify the connect string used to link to the Amazon RDS for SQL Server (that is, the Server Name, Server Instance Name, or IP address).

Note: If the Amazon RDS for SQL Server instance is hosted on a Windows server and UDP port 1434 is closed then the port number must be included in the address used to connect Spotlight to the Amazon RDS for SQL Server instance.

Display Name

The display name of the Amazon RDS for SQL Server 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 Amazon RDS for SQL Server can have the same display name.

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

Authentication

Prerequisites:

Before connecting to Amazon RDS for SQL Server from an EC2 instance using Windows Authentication (AWS Managed Microsoft AD), follow these steps:

Add the credentials of the user currently logged into the EC2 environment:
- Navigate to Services | Spotlight Diagnostic Services | Log On | This account.
Click OK to save the changes.
Restart the Spotlight Diagnostic Service.
Open SOSSE, and you should now be able to connect to Amazon RDS for SQL Server using Windows Authentication.

Specify the authentication for Spotlight to use to connect to the Amazon RDS for SQL Server instance.

Select Windows Authentication (AWS Managed Microsoft AD) to use the Windows user configured with AWS Managed Microsoft AD to run the Spotlight Diagnostic Server. User and Password fields are disabled for this authentication mode and will be using credentials created for AWS Managed Microsoft AD user. Ensure this account is trusted by the Amazon RDS for SQL Server.

Note: To connect using Windows Authentication, users need to install the SOSSE client in the Amazon EC2 environment. This environment must be part of the same Active Directory (AWS) domain.

Alternatively, fill in the Database User and Password fields. Ensure the database user has sufficient account permissions to retrieve performance data from the Amazon RDS for SQL Server instance and host by WMI. Typically the account will be a member of the processadmin server role. It could be an Amazon RDS for SQL Server login. If this is not feasible in your environment, see Run this script to grant permissions to TrustedUser.

The connection will fail if the account permissions are insufficient to allow Spotlight to collect the data it needs.

Use Extended Events

Selected

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

Amazon RDS for SQL Server | Workload Analysis Drilldown
Amazon RDS for SQL Server | Wait Events Drilldown
Deadlock checks: Amazon RDS for 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 Amazon RDS for SQL Server | Workload Analysis drilldown and Amazon RDS for SQL Server | 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 Amazon RDS for SQL Server connection by navigating from Amazon RDS for SQL Server 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 Amazon RDS for SQL Server 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, Amazon RDS for SQL Server connection uses the Global Extended Event specified in the Spotlight Option settings.

Not Selected

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 Amazon RDS for SQL Server | Workload Analysis drilldown and Amazon RDS for SQL Server | Wait Events drilldown.

If you set this value after the connection to the Amazon RDS for SQL Server 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 Amazon RDS for SQL Server on the Spotlight Diagnostic Server from Configure the use of Extended Events.

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 Amazon RDS for SQL Server 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 Amazon RDS for SQL Server instance before raising the Connection Failure alarm.

Tip: If the number of times Spotlight should attempt to connect to the Amazon RDS for SQL Server instance before raising an alarm is greater than 9 then type that number into the given field.

Alarms template

Select the alarms configuration template to apply to this connection.

Select from any of the Amazon RDS for SQL Server 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 Amazon RDS for SQL Server 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 Amazon RDS for SQL Server connection. To apply a template to an existing Amazon RDS for SQL Server connection, see Configuration Templates.

Test the connection

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

Troubleshooting

Connection to the Amazon RDS for SQL Server instance

If Spotlight cannot connect to the Amazon RDS for SQL Server instance then use another tool such as Microsoft’s SQL Server Management Studio or sqlcmd to connect to the Amazon RDS for SQL Server instance. Is the issue with Spotlight’s ability to connect to the Amazon RDS for SQL Server instance or with any any/every tool’s ability to connect to the Amazon RDS for SQL Server instance?

Release Notes

Check the Spotlight Enterprise Release Notes for known issues.

Tags: