I wanted to spend the first portion of this post for all the beginners out there.

It gets better.

The learning curve from T-SQL to Powershell is fairly steep, but once you start automating and making your life easier the rewards you reap will far outweigh all the 4 letter words along the way.

I’m still cursing my comparison operator muscle memory.

The best way to learn is to just start coding.

Another great way is to simply install reputable modules and look at the code written by the experts.
dbatools
DBAReports
ReportingServicesTools

With the myriad of programming and scripting languages available today why all the ruckus about powershell?

Well, for me the answer lies somewhere between the learning curve and “time to market” . Although the learning cure is there, it’s much smaller than other programming options. With a team of DBAs the chances of all of them picking up VB, or C# is nearly impossible when compared to Powershell. Also, if you’re using WPF over Winforms on top of that, then adding XAML into the mix is just cruel.

When my team now see what I am doing in such a short amount of turn around time from planning to release, the excitement drives them to learn.

Also, this way when something breaks I’m not the only human on my team able to fix it.

Is my default answer always powershell? well in true DBA fashion, it depends. Every project is different and it may or may not be the best candidate to solve a problem.

But often times it is.

Anything I have to do manually is a candidate for automation. When I do anything I’m always asking myself: “self, can I automate this with something?” Often times the answer is yes, and more often than not that journey leads straight to Powershell.

How I use Powershell:

1. Transferring logins
   • I’m always transferring logins between instances in dev and prod
   • whenever I need to do this i’m always using the dbatools function copy-dbalogin
2.  Auditing backup/job history info
   • Using our CMS, and the folder structures each are registered in, I can get a specific subset of data
   • I’ve used this same function to do X work on all registered servers in each CMS domain
3. Updating hundreds of SSRS subscriptions
   • This was the subject of my very first technical blog post here
   • The update function is on its way into the official MS ReportingServicesTools module! WooHoo!
     (I just need to learn the whole unit testing thingy pester)
4. DBReboot module – Rebooting SQL servers tied to application services
   • In order to help facilitate a healthy patching schedule I created a module to help reboot sensitive instances
   • Uses a SQL repository DB to bring services down for an application in specific order

By far the coolest thing i’m working on currently would be a DBReboot module. This was created out of a need to reboot sensitive SQL servers with finicky applications attached.

In order to do this manually we would have to engage 3 teams. THREE!

One team to bring down the services in order, the DBA team to make sure no sessions are connected and to reboot the SQL. Then the application owner to verify it’s working properly when everything is back up. Not cool man. not cool.

Not sure about you but I’m not thrilled about anyone on my team doing this on a Friday night or Saturday morning when they can concentrate on more important projects.

At first I thought this would be fairly straight forward. However, it turned out to be a slightly more complicated moving target.

The database repository holds the SQL server information to reboot, all the subsequent application servers attached and all the services for each. The module interacts with the repository to gather any servers that are scheduled to reboot at the time of function execution.

Each application server can have the same set of services, or completely different services. To make things more complicated we also needed the option to bring the services back up in the reverse order for certain pain in the butt applications.

The scheduling is fairly straight forward with a schedule table holding, days, time and dayofmonth options (like in sql agent jobs).

The main calling powershell function takes the current day and time and verifies whether a server exists for the current schedule.

The public functions involved are listed above and work in conjunction with the repository database to capture stored procedure output, which is used to iterate over servers and services.

All of the ordering is done by stored procedures simply because that’s what SQL is better at, set based operations. Powershell is used to start/stop and iterate over objects. In the end it’s the perfect combination!

Below is a high level overview of what is in the module

Restart-DBRebootServices – main function requiring the DBrepository parameter (integrated security only right now)
all subsequent functions below are called from this main function.

Restart-DBRebootServer -DBRepository Tattoine\Yoda

Set-DBRebootServices – this function will iterate through the services based on a serverID and will call either start or stop services based on the required service action

Set-DBRebootServices -appservers $appServerNames -sqlconnection $sqlConnection -serviceaction STOP -ErrorAction STOP

Within the set-function is where the start/stop service functions are called based on the above -ServiceAction

 $functionname = $serviceaction+"-DBRebootServices" 
 & $functionname -Computername $appservername -ServiceName $servicetostop -isfuzzy $isfuzzy -ErrorAction Stop

Stop-DBRebootServices – Stops services, either wildcard, or exact passed from set-services function.

Reset-DBRebootHungServices – Checks for any services, passed from the stop-function, that may be in a hung state and kills the process

$tokill = Get-WmiObject -ComputerName $Computername -Class win32_service -Filter "state = '$serviceaction" + "pending' and name = '$($hung.DisplayName)'"
Invoke-command -ComputerName $Computername -ScriptBlock {stop-process -id $args -force} -ArgumentList $tokill.ProcessId

Start-DBRebootServices – Starts services, either wildcard or exact, passed from the set-services function.

Final thoughts

Unfortunately the module is not 100% ready for outside eyes yet. However, it is working in one of our environments successfully. A more comprehensive blog post is in the works, along with a github repo.

There is just so much to do and so little time!

A big thanks to Rob Sewell for hosting and Adam Mechanic b | t for the original TSQLTuesday! Can’t wait to start reading all the other blog posts!