Automating Large SentryOne Installations

When you have a large number of targets to monitor in the SentryOne environment, it can be a bit daunting to set up. Graphical interfaces are nice in that you can step through setup processes and get immediate feedback, but if you have a lot of servers, the process can be tedious. In my last blog post (Targeting Server Core for Monitoring with PowerShell), I discussed accessing Windows Server Core targets for the SentryOne monitoring. Today I’ll address taking some of those steps and including them in a mostly automated process for setting up all of your servers via scripts.

Automating: Getting Started

In that last post, I shared my test domain, consisting of a domain controller (WS16DC), a Windows 10 client (W10CLT), a monitoring service server (WS16MS0), and two SQL Server servers running Windows Server Core 2016 (WS16SQL1 and WS16SQL2). To set up monitoring using the SentryOne solution, I start with the monitoring service server, running Windows Server 2016 with the graphical interface. I copy the license key file and the SentryOne setup program to the C:\Setup directory, and open up a PowerShell ISE window. (I like using the ISE because I can load my script file in the ISE, then highlight sections and execute just those sections, just like you can do in SSMS with T-SQL code.)

The first thing I did was to put all the target servers in my plan into a file called servers.txt, one server per line, like this:

WS16SQL1
WS16SQL2

The benefit here is that you can put ten, or one hundred, servers into that file, and the process will still work. I then pull the contents of that file into a PowerShell variable.

$srv = Get-Content .\servers.txt

Automating: Configuring Settings

Once that variable is populated, we can use PowerShell’s remoting capabilities to set the settings on the target systems in parallel using the Invoke-Command cmdlet. By default, Invoke-Command will execute the task on up to 32 simultaneous servers, but you can use the -ThrottleLimit parameter to set your own preferred maximum. If you exceed the maximum, Invoke-Command will continue with the remaining servers as the initial ones are complete. Needless to say, executing these tasks in parallel across your target servers is far faster and more efficient than iterating through them one at a time.

To set the network firewall settings on all servers, I execute this command.

Invoke-Command -ComputerName $srv -ScriptBlock {
New-NetFirewallRule -DisplayName "Windows Performance Counter port 445" -Direction Inbound -Protocol TCP -LocalPort 445 -Action Allow
New-NetFirewallRule -DisplayName "WMI port 135" -Direction Inbound -Protocol TCP -LocalPort 135 -Action Allow
netsh int ipv4 set dynamicport tcp start=50000 num=255
Set-NetFirewallRule -DisplayGroup "Windows Management Instrumentation (WMI)" -Enabled True
Set-NetFirewallRule -DisplayGroup "Remote Event Log Management" -Enabled True
([ADSI]"WinNT://$($env:COMPUTERNAME)/Administrators,group").add("WinNT://$($env:USERDOMAIN)/s1svc")
}

The last command adds the TESTDOMAIN\s1svc account in my domain to the local administrators group on each target server. It uses the COMPUTERNAME environment variable to select the local group, and the USERDOMAIN environment variable to specify the domain name.

Automating: Power Settings

The next command I run doesn’t need to be run for the SentryOne process, but it is a best practice in SQL Server environments, as it sets the Power setting of each server to High Performance. Many servers are left at the Balanced setting, which can negatively impact performance.

Invoke-Command -ComputerName $srv -ScriptBlock {
$p = Get-CimInstance -Name root\cimv2\power -Class win32_PowerPlan | Where {$_.ElementName -eq 'High Performance'}
Invoke-CimMethod -InputObject $p -MethodName Activate
}

It works by getting the list of power plans on the target system, filtering for the one named ‘High Performance’, and then activating that plan using Invoke-CimMethod.

Automating: SentryOne Service Account

The next steps involve executing the Transact-SQL code to add the domain SentryOne service account as a Windows Login on each target SQL Server instance, and adding it to the sysadmin server role on each instance. Because I’m running this from the monitoring service server, where I haven’t installed any SQL Server tools, I’m using ADO.NET, which doesn’t require any external modules. It’s always there when you’re running PowerShell.

The SqlConnection object needs a standard connection string, and I use the COMPUTERNAME environment variable to specify the local server in the string. I open the connection, then build the CREATE LOGIN query in $q. The SqlConnection object and the query are the only parameters needed for the SqlCommand object, which has the method ExecuteNonQuery(), which adds the login. I then use the same steps to add the login to the sysadmin server role and close the connection.

Invoke-Command -ComputerName $srv -ScriptBlock {
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$($env:COMPUTERNAME);Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "CREATE LOGIN [$($env:USERDOMAIN)\s1svc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]"
$cmd = new-object System.Data.SqlClient.SqlCommand($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$q = "ALTER SERVER ROLE [sysadmin] ADD MEMBER [$($env:USERDOMAIN)\s1svc]"
$cmd = new-object System.Data.SqlClient.SqlCommand($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$cn.Close()
}

SentryOne Setup

Now that the requirements are in place in all targets, I can manually execute the SentryOne setup program, where I specify Do Not Install for everything but the Monitoring Service. I specify WS16SQL2 as the target for the SentryOne repository database and verify that my login can access that server. On the next dialog I specify the SentryOne service account (TESTDOMAIN\s1svc) and complete the installation. Now the monitoring service is running on WS16MS0, and I can now continue.

Since the monitoring service has been installed, I can now import the SentryOne PowerShell module, and connect to the repository on WS16SQL2.

Import-Module "C:\Program Files\SentryOne\11.0\Intercerve.SQLSentry.Powershell.psd1" 
Connect-SQLSentry -ServerName WS16SQL2.TESTDOMAIN.COM -DatabaseName SentryOne

License Key

To save time during the client installation later, I can use ADO.NET again to load the license key into the repository database. The license key is XML, and goes into the LicenseText column in the dbo.License table, so I get the XML out of the file using Get-Content, and use the same ExecuteNonQuery() to execute the insert statement in the repository database.

# Add the License Key to the repository database
$lk = Get-Content '.\SentryOneLicense(WS16SQL2-WS16SQL2).txt'
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=WS16SQL2;Integrated Security=SSPI;Initial Catalog=SentryOne");
$cn.Open()
$q = "DELETE FROM [dbo].[License]"
$cmd = new-object System.Data.SqlClient.SqlCommand($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$q = "INSERT INTO [dbo].[License] ([LicenseText]) VALUES ('$lk')"
$cmd = new-object System.Data.SqlClient.SqlCommand($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$cn.Close()

Register Targets

Once that’s done, I’ll iterate through the servers in servers.txt to register each target in the repository. The USERDNSDOMAIN environment variable contains the full domain name (TESTDOMAIN.COM in this case), so for server WS16SQL1 the -Name argument receives WS16SQL1.TESTDOMAIN.COM, the fully qualified domain name of the server.

# Register the connections
$srv = get-content .\servers.txt
ForEach ($sv in $srv) {
$fqdn = $sv + '.' + $env:USERDNSDOMAIN
Register-Connection -ConnectionType SqlServer -Name $fqdn
}

Client Installation

At this point, I switch to the workstation where I’ll install the client. I only need to copy the SentryOne install program there and run it. I opt to install every component except the monitoring service, specify WS16SQL2 as the server hosting the SentryOne repository database, and when it’s done, indicate that I want to run the S1 client. When it opens, it displays my name and email address (which it got from the license key I’ve already inserted into the dbo.License table), which I verify.

When it opens to the navigator, I close the client, and open up a PowerShell window on the client. After loading the SentryOne PowerShell module, I connect to the repository, as I did on the other system, but now I can use the Get-Connection cmdlet, and pipe the result to the Invoke-WatchConnection connection, which starts the monitoring service watching each of the targets registered earlier.

Import-Module "C:\Program Files\SentryOne\11.0\Intercerve.SQLSentry.Powershell.psd1"
Connect-SQLSentry -ServerName WS16SQL2.TESTDOMAIN.COM -DatabaseName SentryOne
# Start watching the connections
Get-Connection -ConnectionType SqlServer | Invoke-WatchConnection

Setup Complete!

At this point the installation and setup steps are complete. SentryOne is collecting data to allow you to be more effective at solving performance issues across your entire environment. Whether you have 10 or 1000 servers to monitor, the SentryOne PowerShell module will get you monitoring them quickly.

Allen White

Allen (@SQLRunr) is a Microsoft MVP and the Senior Technical Training Specialist for SentryOne. He’s worked with SQL Server for more than 20 years in a variety of roles – developer, administrator, architect, analyst.