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.
Use the Spotlight Cloud Diagnostic Server app to manage connections
The Spotlight Cloud Diagnostic Server app is installed locally. Double click the desktop icon.
Sign in with your Quest account, and then from the configuration panel, click connections.
This opens the Spotlight Connection Manager. To enter new connection details double click add new connection and select the replication connection type. To edit the properties of a pre-existing connection, double click on the connection with details you want to edit.
Distributor SQL Server instance
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.
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.
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 name
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.
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.
For 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.