A Fine Slice Of SQL Server
Mikey Bronowski shows off PSKoans:
Recently I have got a question about resources to learn PowerShell. There is plenty out there in the wild, but I came across an interesting module I would like to write today – PSKoans.
I’m a big fan of the koan strategy of learning. It ramps you up slowly and gives you plenty of code to help understand syntax and flow. The F# koans are fantastic, as are Python’s.
Comments closedJess Pomfret has a check for who owns specific failover cluster resources:
If we are going to test that we’re in our expected configuration, we need to record what that configuration looks like. I have a hard coded list of cluster names. However, you could easily pull them from a text file, or a database. Once we have the list of clusters we can use
Get-ClusterGroupto determine the cluster roles and their current owners.To persist this owner information I’m using
ConvertTo-Jsonand then outputting it to a file. This creates a file that can easily be read back into PowerShell as an object usingConvertFrom-Json.It’s also probably worth mentioning that this ideal configuration can be stored in source control. That’ll keep the file safe and you can easily keep track of any changes that are made to it.
Read on for the full set of steps.
Comments closedGarry Bargsley wraps up a series on SQL Agent job failures:
Here we are, another Tuesday!! It is time for our Fourth and final installment in the Failing SQL Agent Jobs series. This week, we will cover how to store SQL Agent Job Failures to a database for tracking and review.
Click through for the script.
Comments closedJana Sattainathan builds a recursive solution to an interesting problem:
Here is the example he gave me:
I am looking for a way to detect recursion loops in a specified AD Group. Take this example of groups and members. I started with Group1, and found Group2, with group member Group2. Group2 has a group member Group3. Enumerating Group3 finds Group1, and loop!
He also said: Some groups are nested many levels down, and a single top level group can have more than one recursive loop. The script should be able to not only identify Group1>Group3>Group1, but also Group1>Group8>Group5>Group15>Group1.
This is a good use case for recursion.
Comments closedThere are multiple security-related objects that are not easily accessible via SQL Server Management Studio. The first one would be Service Master Key, if exists, can be seen under the master database. Luckily, dbatools can help us to take a backup.
Click through for the details. If you’ve missed any of Mikey’s posts, here is the full list of posts.
Comments closedMartin Schoombee has started a series on automating Power BI deployments. The first post covers some of the basics of Powershell:
The console works great when you’re executing single commands, but not when you’re developing entire scripts. The ISE has a built-in console and has everything you need from a development perspective.
I have recently started using Visual Studio Code to develop my PowerShell scripts, and it works pretty well and feels like a more complete development tool. You would also need to use VS Code if you want to install and use PowerShell 7, because it doesn’t work with the ISE. I don’t want to get too lengthy here, so if you’re interested in using VS Code you can read all about it here.
Click through for the entire article.
Comments closedMikey Bronowski continues a series on dbatools:
OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple instances is easier with dbatools.
Mikey covers services, SQL Agent jobs, endpoints, traces, and Extended Events sessions, so check it out.
Comments closedRob Sewell has a love for automation:
I have tried my best at all times to follow this advice in the last decade and pretty much I am happy that I have managed it.
– I use PowerShell (a lot!) to automate all sorts of routine tasks including migrating this blog
– I use Jupyter Notebooks to enable myself and others to automate Run Books, Training, Documentation, Demonstrations, Incident Response. You can find my notebooks here
– I use Azure DevOps to automate infrastructure creation and changes with terraform and delivery of changes to code as well as unit testing.
– I use GitHub actions to create this blog, publish the ADSNotebook module
– I use Chocolatey to install and update software
– I have used Desired State Configuration to ensure that infrastructure is as it is expected to beAt every point I am looking for a means to automate the thing that I am doing because it is almost guaranteed that there will be a time in the future after you have done a thing that there will be a need to do it again or to do it slightly differently.
Click through to see what Rob has for us in the way of collecting SSIS log data after job failures.
Comments closedAaron Bertrand has a new project:
There’s a whole lot of grunt work in there that shouldn’t have to be done by a person. I don’t think you could automate the whole thing, because it is hard to predict exactly what events will be interesting and not, but I think 90% is achievable.
A colleague mentioned that they want to build something that would help, but even when that happens, that would up in proprietary code that only helps us. I saw Drew Furgiuele’s post on Building a Better Get-SQLErrorLog, and that gave me some ideas for what I would build. After reaching out to Drew, I created a GitHub repository with a working name of GetAllTheErrorLogs. Its elevator pitch is a simple sentence:
Powershell to assemble a timeline combining salient events from Windows Event Log, Failover Cluster log, and SQL Server errorlog.
Click through for the details as well as Aaron’s current progress.
Comments closedJess Pomfret automates runs of dbachecks via scheduled task:
To automate the running of our daily checks we’ll first create a PowerShell script and then schedule that using task scheduler. If you have other enterprise scheduling tools available you could easily use those instead to invoke the PowerShell script.
The script for my example, shown below, is pretty simple. I have a section to define where the data will be stored (the ability to save dbachecks result information straight into a database was introduced with dbachecks 2.0 and so I would highly recommend updating if you’re on an earlier version).
Click through for the full explanation and code.
Comments closed