You finally got approval for SQL® Sentry, and it’s purchased and installed. Now begins the arduous task of manually adding all the targets to be monitored. If you are in a medium to large shop, this can be hundreds to thousands of servers. While this certainly sounds like a job for a junior DBA, we can alternatively leverage the SQL Sentry Powershell module to make quick work of this.
In my code example, I have a Server Inventory table. I am using Invoke-DBAQuery from the DBATools module to populate the variable to loop through, register the computer add then a new SQL Server instance to be watched. If you do not have a Server Inventory table but have a Central Management Server, you could use this DBATools Cmdlet instead Get-DbaRegServer. I will also mention using a CSV file in an example later in this post. Quick note, if you are not using DBATools, you should. It makes the life of a DBA so much easier.
As shown below, this is not a “super-fancy” script, but if you want to add more functionality, this gives you the framework to take it in any direction you want.
I also wanted to provide an example from one of our customers, Adrian Mioduszewski, and how he uses this type of functionality. His script is more robust than my example and uses more cmdlets from the SQL Sentry module to check if the server being added is already a target in SQL Sentry. Also, Adrian’s example uses a CSV file as the list of servers to be added.
I hope these examples will help you get up and running with SQL Sentry quickly and efficiently.
If you have any questions please put them in the comments so we can have further conversations.