SQL Sentry Tips and Tricks: Monitoring Targets Across Multiple Domains
A frequently asked question when I’m speaking with SQL Sentry customers is, “Can I monitor targets across multiple domains?” The answer to this question is yes. Although there might be specific scenarios in which you’ll want to have multiple SQL Sentry installs, it’s possible to monitor targets across multiple domains through one install (i.e., one centralized SQL Sentry database). There are a couple of different options available to do so. In this blog post, I’ll walk through the options.
Before we jump in, it’s important to understand several key components and features within SQL Sentry.
- Target: The target is a device that houses data in your environment. It could be a physical server, Azure SQL Database, Azure SQL Database Managed Instance, Hyper-V or VMware host, Amazon RDS for SQL Server, etc. Monitoring a target is when you’re actively collecting performance data from this instance.
- SQL Sentry Monitoring Service: This is a Windows service responsible for collecting performance data from your monitored targets and sending this information back to the SQL Sentry database. The account running this service will be granted several permissions on the target machines to collect all relevant performance data.
- Site: A site is a logical grouping of targets, typically grouped by physical location, department/application, or domain. Each site must have at least one monitoring service to collect performance data for targets in the given site.
Pass-Through Authentication
Pass-through authentication allows Windows machines in different domains to communicate with one another via identical user accounts on each machine. In relation to the SQL Sentry install, the machines of interest will be the machine hosting the monitoring service and the target you want to monitor. The main benefit of pass-through authentication is that you don’t need to install additional monitoring services to collect performance data in different domains. Instead, you can make a copy of the service account on the monitored target.
Although pass-through authentication isn’t ideal at scale, it works nicely when you want to monitor one or two targets in a different domain. The more targets you’re planning to monitor, the more times you’d need to replicate this account. If you’re looking to monitor anything more than a handful of targets in another domain, I recommend setting up Site Configuration, which I’ll cover later in this blog post.
To summarize, the account running the monitoring service must exist on both the service machine and all targets you want to monitor. A more detailed example of pass-through authentication and more information can be found here.
If pass-through authentication isn’t an option, you can also monitor targets in another domain with a SQL Server account. This allows SQL Sentry to retrieve all SQL Server-related metrics without the need for additional monitoring services.
If you’ve made the decision to install a monitoring service in the second domain, you now need to ensure this service is exclusively attempting to collect performance data from targets within this domain. To do so, you’ll want to setup Site Configuration.
Sites in SQL Sentry
In all SQL Sentry environments, there will be at least one site configured. This can be seen in the Navigator pane and will be listed as the Default Site. When you install SQL Sentry, the monitoring service that will be installed will sit in this site.
Note, for fault tolerance and load balancing, you’ll need multiple monitoring services. You might also need to install additional monitoring services as you continue to scale out the monitoring environment over time. To get a better understanding of when multiple monitoring services might be required, please see our Monitoring Services and Targets Per Site estimates.
The screenshot below shows a site in SQL Sentry hosting two monitoring services for the reasons previously mentioned.
Right click the site name in the Navigator pane to rename sites
The monitoring services shown in the screenshot above will collect performance data for all targets specified in that site. This is exactly what should be expected, but now let’s look at what needs to be done to start collecting performance data in a different domain (Domain B).
Site Configuration
The preferred method for monitoring targets in different domains is to configure each domain as its own site. Each domain in this case is represented by a site and has at least one dedicated monitoring service available. All monitoring services will then write data back to the centralized SQL Sentry database.
The following is required to do so:
- A machine to host the monitoring service in the domain.
- An Active Directory (AD) account within the domain that will have the same permissions/rights on the target machines as the previous service account has in the original domain. This account will collect all performance data locally.
- A SQL Server account that will allow you to send the data back to the centralized repository through the port used by SQL Server.
- Ports between the target and the monitoring service
Monitoring Service Install in Domain B
At this point, you can now create the new site and then install the monitoring service in Domain B. To create a site in SQL Sentry, simply right click All Targets in the Navigator pane and select Add - Site. After naming the site, the newly created site should display in the Navigator pane.
Right click All Targets to create your new site
Before you start adding targets, you need to install the monitoring service in Domain B. If you’re using the standard installer, you’ll need to run the installer on the machine that will host the new monitoring service.
Note, all SQL Sentry components must be on the same version! When running the standard installer, select custom install and select the monitoring service. I tend to include the SQL Sentry client and use this client to add targets in this domain later. You can view the steps to do so here.
During setup, be sure to specify the SQL Server account on the Database Account Information tab and then enter the AD account for Domain B on the Service Account Information tab. As mentioned earlier, the service account will be part of the Windows Administration Group on each target within Domain B and collect the performance data locally. The SQL Server account will then send this information back to the SQL Sentry database.
Using the SQL Server account for the database connection
Using the AD account in Domain B for the monitoring service information
Once the monitoring service is created, you now need to make sure it’s in the correct site within the SQL Sentry client. The service will initially be placed in the Default Site—you’ll want to move it. You can do so by clicking and dragging the monitoring service to the correct folder. You can also manage the creation of sites and movement of either services or targets through Site Configuration.
Site Configuration: Found under the Configuration folder in the Navigator pane
Conclusion
With this knowledge, you’ll be able to set up SQL Sentry to monitor your SQL Server environment across multiple domains if needed. And the same features/policies covered in this blog post can be applied in other scenarios.
Typically, after setting up Site Configuration, you’ll need to alter settings and alerting at the site level. In his blog post “SQL Server Alert Tuning Basics with SQL Sentry,” Patrick Kelley discusses how to leverage the topology of the SQL Sentry Navigator pane in relation to alerting. To learn more about settings that can be managed at site level, please reference the SQL Sentry documentation here.