Run T-SQL Script Using Powershell via SQL Agent Job

Question

Tried to think of the best subject title; however having a problem getting this done. I'll give my preferred way, but open to any best method to get this done.

I have a tsql script that is done, formatted, perfect, does what I need. I want to run this script against multiple servers, scheduled as a SQL Agent job to run every X minutes.

I was trying a flavor of OSCMD: powershell.exe Get-DbaCmsRegServer -SqlInstance instance |invoke-dbaquery -Query 'MY SCRIPT'

Yes I'm incorporating dbatools (which is deployed on all servers, and no problem when I substitute 'MY SCRIPT' for something simple like 'sp_helpdb', for example.

It's trying to add my script with multiple lines, single quotes, parameters etc. I could, but rather put the script in the agent job (unless this just isn't the best way), use -File c:\folder'MyScript.sql'

So, the ask is, how can I get it to run the way I need, and prefer? Please make some suggestions, and even better ones than I am trying to do. When I put my script in double-quotes, the job succeeds, but nothing happens, so it's not executing as intended.

Please advise better, best, workable methods to get this done. MANY THANKS!!

Answer 1

We have a database for maintenance tasks on all servers, housing things such as Ola Hallegren's backup solution, and Brent Ozar's blitz scripts. If it were me, I would create a procedure there with the script contents, then create a SQL Agent Job to call it on the schedule you like. Then, you can script out the job, as well as the procedure and deploy it to all your servers via Central Management.

You'd designate a server as the Central Manager (e.g. the one you just created the Proc and Job on), register other servers to it, then deploy the Proc and the agent job to all registered servers. Once you have everything registered, you right click on your Central Management server and select "New Query". It opens a session on all servers, then drop the code you want to exist everywhere, run it, and you're done.

Using Powershell and dbatools seems unnecessary when SSMS provides native functionality to accomplish all of this.

Expose Registered Servers in SSMS under the View menu.