Specifications
Spotlight Cloud monitors a SQL Server replication environment by connecting to the SQL Server instance hosting the distribution database.
- Ensure this SQL Server instance meets the same criteria as Spotlight Cloud has for monitoring SQL Server instances.
- Ensure the user entered for authentication has permissions to connect to the SQL Server instance and is a member of the db_datareader role in the distribution database.
- Data is collected for transactional replication only. The Spotlight overview page for SQL Server replication and SQL Server replication alarms are raised from data collected from transactional replication only.
SQL Server Replication
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.
Scroll down this help page for information specific to SQL Server replication connections.
Address
Specify the server name, server instance name, or IP address of the SQL Server instance hosting the distribution database. For detailed information on how to specify these in the address field see manage SQL Server connections.
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 the permissions needed to connect to the SQL Server instance and is a member of the db_datareader role in the distribution database. The Database User needs:
- VIEW SERVER STATE permission (to connect to the SQL Server instance)
- to be a member of the public role in the master database (to connect to the SQL Server instance)
- to be a member of the db_datareader role in the msdb database (to connect to the SQL Server instance)
- to be a member of the db_datareader role in the distribution database (to monitor replication)
The connection will fail if the account permissions are insufficient to allow Spotlight Cloud to collect the data it needs.
Connection failure properties
Set the connection timeout and the number of times Spotlight should attempt to connect to the distribution database before raising the connection failure alarm.
Distribution Database
Select the distribution database.
All distribution databases on the SQL Server instance are selectable from the menu. If you cannot find the distribution database from the list of options then verify that you have connected to the right SQL Server instance.
Alarms template
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 replication templates that you have created through configure alarms.
The alarms template option is applicable only when creating a new SQL Server replication connection. To apply a template to an existing replication connection, see configuration templates.
Testing the connection
When you add a new SQL Server replication connection or edit the details of an existing connection, Spotlight Cloud tests its ability to connect to the SQL Server instance.
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 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.
Troubleshooting the replication connection
- Make sure the SQL Server instance entered in the address field hosts the distribution database.
- Make sure the user entered for authentication has permissions to connect to the SQL Server instance and is a member of the db_datareader role in the distribution database.