Requirement:
To be able to run a generic diagnostic information gathering script remotely via System Center Operations Manager on a SQL Server instance without having to start a separate session of SSMS and / or RDP and executing script.

There are quite a few well tested and trusted TSQL scripts available developed by MVPs and SQL experts like Glenn Berry, Brent Ozar and many others which helps in fixing problems with SQL Servers.

SQL Server Management Pack provides a fix number of tasks that can be performed against database instances and databases. While they are great you might want to be able to execute your own custom scripts or by the industry and community experts like the ones mentioned earlier.

So here is one of the ways to extend the SQL monitoring and diagnostics functionality within SCOM, the principals of which can be applied to non SCOM scenario which I will try cover in a separate post.

High level overview of the process:
Amend the script in a way that it can be run with a single execution attempt i.e. by pressing F5 if pasted in SSMS connected to an instance i.e. the script should have

  1. Create script steps for main stored procedure and / or dependencies
    • execution
    • cleanup (if required).
  3. Save the script in  .sql file
  4. Get some understanding and of SQLCMD the command line connection tool for SQL Server.
  5. Create a DOS batch file with command line parameter for full instance path
  6. Save the batch file .bat and .sql file in a read only shared for all network location that can be universally accessed.
  7. Create a SCOM Task in the context of the SQL Database engine and pass full instance path to the batch file.
  8. Test execution on least priority or own servers from SCOM.

Now lets take a deeper dive. For practical demonstration I will use the Sp_Blitz script by Brent Ozar

To be continued…