Check your SQL Server instance fulfills the requirements of Spotlight Cloud.
Connection details
Open this dialog in the Spotlight Cloud Diagnostic Server application. Show me how to open the connection details screen in the Spotlight Cloud Diagnostic Server application.
SQL Server connection details are configured in four pages of the Add new connection dialog. Scroll down this help page for information specific to configuring SQL Server connections using this dialog.
SQL Server instance
On this page, you will configure the address and authentication information for your SQL server.
Address
Specify the server name, server instance name, or IP address used to link to the SQL Server instance.
If aliases are used then an incorrect alias on the client machine can cause connections to fail. Check the alias name of the instance. For a Microsoft Cluster Server (MSCS) use the virtual name of the cluster.
Check the default ports used by the SQL Server instance.
Default instances typically use port 1433. Some installations use a non-standard port (other than 1433) for running SQL instances.
Named instances can be configured to use dynamic ports or a static port. The underlying network libraries query the SQL Browser Service running on your SQL Server machine via UDP port 1434.
Identify the default port and IP address of the SQL Server instance. To do this, open the SQL Server Configuration Manager | SQL Server Network Configuration | Protocols for {Instance Name} | TCP/IP option
Add the port number in one of the following formats. Prefix the port number with the server name or IP address.
server name, port number
IP address, port number
Authentication
Specify the authentication for Spotlight Cloud to use to connect to the SQL Server instance.
Select Windows Authentication (using Diagnostic Server credentials) to use the Windows user configured to run the Spotlight Cloud Diagnostic Server (as a Windows service). Ensure this account is trusted by the SQL Server instance. When using Windows authentication to connect to a SQL Server, and that SQL server is in a different domain to the Spotlight Cloud Diagnostic Server, the domain the SQL Server is in must trust the domain the Spotlight Cloud Diagnostic Server is in.
Alternatively, fill in the Database User and Password fields. Ensure the database user has sufficient account permissions to retrieve performance data from the SQL Server instance and host by WMI. Typically the account will be a member of the sysadmin server role. It could be a SQL Server login (such as ‘sa’). If this is not feasible in your environment, see How to grant SQL Server account permissions to a trusted user.
The connection will fail if the account permissions are insufficient to allow Spotlight Cloud to collect the data it needs.
Read only intent
Select this option if the SQL Server instance hosts a secondary replica of an availability group and for this secondary replica ApplicationIntent=ReadOnly. Failure to select this option when required will result in some data not being collected for the secondary replica.
Connection Failure Properties
Set the connection timeout and the number of times Spotlight should attempt to connect to the SQL Server instance before raising the Connection Failure alarm.
If the number of times Spotlight should attempt to connect to the SQL Server instance before raising an alarm is greater than 9 then type that number into the given field.
SQL Server Host
Configure whether to monitor the physical Windows server hosting the SQL Server.
Do not monitor - Select Do not monitor if you do not want Spotlight Cloud to monitor the Windows Server.
Cluster (monitor active node) - Select Cluster (monitor active node) for a Microsoft Cluster Server (MSCS).
Spotlight Cloud uses the current host node name to select the operating system connection. Therefore each Windows node in the cluster must be monitored by Spotlight Cloud. Verify each Windows node is in the list of Windows Server connections.
Click Add to add a Windows server to the list. See Manage Windows Server connections for more information.
Virtual environment
Configure whether to monitor the virtual server hosting the SQL Server.
Do not monitor - Select Do not monitor if you do not want Spotlight Cloud to monitor the virtual Server.
Click Add hypervisor server to add a host for montitoring. Refer to Add/edit VMware connection details or Add/edit Hyper-V connection details.
Alarms templates
Select the alarms configuration template to apply to this connection or select Factory Settings Template to apply the factory settings shipped with Spotlight Cloud. Select from any of the SQL Server configuration templates that you have created through configure alarms.
The template will apply to the Windows host of the SQL Server instance if known / monitored by Spotlight Cloud.
The alarms template option is applicable only when creating a new SQL Server connection. To apply a template to an existing SQL Server connection, see configuration templates.
Testing the connection
When you add a new SQL Server connection or edit the details of an existing connection, Spotlight Cloud tests its ability to connect.
Connection failure
If Spotlight Cloud fails to connect to the SQL Server instance then you may want to return to the connection details dialog to ensure the details you have entered are correct. You may want to troubleshoot SQL Server connection failure.
Saving a failed connection
Spotlight Cloud does allow you to save the connection details to a SQL Server instance that it has failed to connect to. If you do save the connection and Spotlight Cloud cannot connect to it then Spotlight Cloud will raise a connection failure alarm. Spotlight Cloud will re-try to connect every 30 seconds until successful. This could be a problem if associated services lock you out after a limited number of failed attempts. To clear the connection failure alarm, wait until Spotlight Cloud successfully connects (when Spotlight Cloud shows collected data). Then acknowledge the alarm.
There are things you can do to change Spotlight Cloud default behavior. You may want Spotlight Cloud to wait for a future occasion to monitor the connection rather than monitoring it right away. The simplest way to do that is to disable the connection. Spotlight Cloud will stop monitoring it. Spotlight Cloud will monitor the connection again when you enable it. See enable/disable.
Note that Spotlight Cloud automatically disables all connections added above your subscription quota.
Other features you may want to use if you save a failed connection are planned outage where you can delay monitoring of the connection till a specified time. You can create an alarm action rule to notify changes in status for an enabled connection.