T-SQLTuesday #94: Using Invoke-SQLCmd to run SQL Jobs Remotely

T-SQLTuesday #94 Let's get all Posh!
T-SQLTuesday #94 Let’s get all Posh!
It’s time for another T-SQLTuesday blog party, and it’s being hosted this month by Rob Sewell (blog|@sqldbawithbeard). So export-ideas from his announcement post or create a new-idea of your own, and then join in the party.

For my contribution to this event, I want to talk about how I prefer to use a remote job server for Availability Groups and an important component of that is using the PowerShell commandlet invoke-sqlcmd.

Fun Fact
Fun Fact

Posh is my least favorite Spice Girl!

Remote Job Servers

One of the practices that I put forward as being a best practice for handling jobs on Availability Groups is to use a remote job server. I hope that some day they will have SQL jobs as a contained feature. Until then, it’s on us to make sure we are hitting the correct server. Popular ways to do this include:

  1. Disabling jobs on the inactive node of the AG
  2. Checking to see if the database you need is in the correct state before performing any work
  3. Running the SQL job from a remote server

I am very fond of that last option. It’s not the cheapest solution (licensing-wise), but it does simplify a lot of things. I simply need to connect to the AG Listener in the job steps, and the job won’t care which server is active. This is fine and dandy for things like SSIS packages that were designed for connecting to remote instances, but the T-SQL steps are the ones that throw people a curve ball.

get-help invoke-sqlcmd

Need help making your T-SQL steps work in remote SQL jobs? Invoke-sqlcmd is the answer. This commandlet is a replacement for the sqlcmd command line tool that can be used to execute SQL Server calls. Just make the step a PowerShell step and invoke this commandlet. Alternatively, you can make it an Operating System (CmdExec) job step and call PowerShell.exe with the -command argument.

The examples below show 2 simple queries being executed with invok-sqlcmd using a Powershell job step and a CmdExec job step:

Powershell Job Step
Powershell Job Step
CmdExec Job Step
CmdExec Job Step

Get-fun storm-castle -force

Now that you have a tool to help you deal with SQL jobs that run against an AG, let me end with a quote from one of my favorite movies.

get-fun storm-castle -force
get-fun storm-castle -force

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *