asked
pass the instance name to an agent job
I want a SQL Agent job to find the server\instance name which launched it. I have a bunch of SQL servers - VMs, active-active 2 node clusters, active-passive 2 node clusters, SQL Server 2008 R2. I want to run the BPA on them on a schedule. I want to run this as a SQL Agent job on each instance. I want to use MSX to distribute the job to the instances. I have the same proxy everywhere to use as the runas credential. So, I have a Powershell script which will run the BPA (doesn't work as a SQL Agent Powershell jobstep because it uses Import-Module). This is stored in a ps1 file on a file share. The script needs to be told the server\instance name. I can run the Powershell in a CMDExec jobstep or an SSIS jobstep (any other possibilities?) On the MSX server I have a table containing the destination folder names. The Powershell script reads the folder name from this table using the server\instance as the key. How do I find the server\instance of the SQL Agent which launched the job and how do I pass it to the Powershell script? In a Powershell jobstep I could use $thisInstance = $(ESCAPE_SQUOTE(SRVR)) but I have no idea what these words are or what language they are in. Thanks in advance for any help
@Wilfred - I liked your first draft answer where you suggested Policy Based Management. Perhaps with that I wouldn't need to run the BPA for the auditor; I'll look into that but I also want to run Multi Server Administration jobs which can find out their instance (apart from the BPA I want to archive the error logs to a shared folder - name depends on the instance name). Thanks @Jonathan - this changes the problem :) so I now have a TSQL job step with the instance name; I need to run a Powershell script using a proxy credential from there. I don't know how to do that.
Have you tried running a script on multiple instances using "Central Management Servers"? This will automatically add a column with the instance name. Or "Multi Server Administration" should also be an option.
can you use the output from SELECT @@SERVERNAME or SELECT @@SERVICENAME to pass the necessary into your script?
6 People are following this question.
