I would like to rebuild many of our SQL scripts into Powershell to further integrate my department with the Windows Operations team and to open up a lot of other options.
1-preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?
Do you have a major preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?
I am looking at using invoke-sqlcmd and copying over old code but with updated error handling, versus rewriting them into pure Powershell scripts. I know there are some pitfalls with using invoke-sqlcmd but the ability to have my scripts already 80% written is a big driver to avoid rewriting them.
Would you recommend just reusing old scripts, or are there benefits I can't find which would justify rewriting them in powershell?
2- Best Method To "Move To The Next Step"
How would you recommend having powershell execute the next entry in the array/loop only after the prior step has completed, successfully?
Each step of the job has a 'quit on success', I then plan on having it execute each step 1 at a time using a Powershell array or loop with all the step names in it. If it fails at any step, break and display error. Some steps take a long time however, and I don't want the other steps to start before the prior finishes completely. There's probably some powershell logic I can use without having to query the job status tables.
In this case, would you use try-catch blocks in SQL and in Powershell, would you recommend doing it in just powershell? I'm trying to pass the console output of the invoke-sqlcmd but this might be somewhere the native powershell variables might be easier to work with.
Currently I'm running:
invoke-sqlcmd 'exec msdb..sp_start_job @job_name = "Refresh", @step_name = "step1"' -Verbose
VERBOSE: Job 'Refresh' started successfully.
Would I then verify I had no errors by taking that output, perhaps using something like Write-Verbose, and performing my error handling that way? It seems like there's a better way.
Thanks in advance.
Edit: I decided to just change the jobs into stored procs and execute those from Powershell using an array for now. if anyone has a better suggestion let me know.