A Fine Slice Of SQL Server
Kevin Marquette goes into great detail on Powershell’s $nullconcept:
When a
$nullvalue is used in a numeric equation then your results will be invalid if they don’t give an error. Sometimes the$nullwill evaluate to0and other times it will make the whole result$null. Here is an example with multiplication that gives 0 or$nulldepending on the order of the values.
Nulls are tricky to handle in any language, making their nuances important to understand.
Comments closedJeffery Hicks might have taken things a bit too far:
Well, I knew I wouldn’t be satisfied. The other day I shared a PowerShell prompt function that could display telemetry like information for a few remote servers. One of the drawbacks was the limited amount of information I could display. I’ve revised that function and have a new version that displays additional information via a few performance counters. I’ve also reorganized the function to make it a bit more efficient. Want to see it?
My jokey lede aside, this is really cool. Click through for details and to get a link to the code.
Comments closedGarry Bargsley has gone through twelve days of the dbatools module’s functionality:
The final day is upon us and I have saved the best for last and one you can take in to the holiday season as a present from our fearless leader of dbatools. The Start-DbaMigration is where the dbatools module started years ago. Who of us have not said “I really should automate this tedious SQL migration stuff” so I do not forget a step when I have not had enough coffee. Well Chrissy LeMaire (b | t) not only said it, but made it reality. This early work has grown in to the dbatools module that we know today and has grown in to a multi-tool toolkit that help the DBA expand their skills without having the burden of learning a scripting language and the complexity that goes with hooking it to your SQL Server.
This tool has literally changed my life since finding it a couple years ago. Not only am I doing work in PowerShell, I am automating work processes. Also, this has contributed to my personal growth. Chrissy and team has such a welcoming atmosphere and inclusion around the project, it has guided me to contribute to an open source project as well as guided me to presenting at SQL Saturdays events.
Read the whole thing. And if you missed parts of the series, they’re all up on Garry’s blog.
Comments closedMax Trinidad walks us through an issue with Powershell Core Preview 3:
Just in case you haven’t try to install PowerShell Core Preview.3, in Windows, which became available on the evening of the 10th of December. If are doing a clean installation, meaning that it was previously manually uninstall, or that this is your first installation, then you are fine.
The issue with installing PowerShell Core Preview.3 is when you do an upgrade over a previous version: either Preview.1 or Preview.2.
That’s a common occupational hazard when working with preview tools. But Max has your solution.
Comments closedJames Livingston gives us a Powershell-based implementation of Dijkstra’s shortest path algorithm:
In my previous post I shared a SQL Server 2017 graph database of US capitals. Graphs are a computer science core competency and present some interesting challenges for programmers. Most notable of these challenges is finding the shortest path between nodes. Dijkstra’s algorithm is a commonly taught algorithm for finding shortest path. Dijkstra’s is often asked about during entry level developer interviews and it is a great algorithm to implement when learning a new language since it requires utilizing loops, logic, and data structures.
Here’s my implementation of Dijkstra’s algorithm using PowerShell, traversing a graph of US capitals. Rather than manage our own graph nodes and edges, we’ll utilize graph tables and queries in SQL Server. There’s a lot of different ways to implement this in PowerShell, my first cut of this ended up using a hash table so I could perform random access. There’s a give-and-take with custom PowerShell objects, which sacrifice random access for some other benefits.
Click through for the code.
Comments closedAndy Levy shows how to use the PoshRSJob module to implement multithreaded Powershell solutions:
Let’s say you’ve got a lot of RSJobs. Or maybe long-running RSJobs. Wouldn’t a nice, user-friendly view of the progress be handy? You bet it would!
This is both dense and verbose, but I wrote it really quickly and it works (but not just for this post, I’ve used it in production), so I’m not tinkering too much with it right now. Once the jobs are created (and a few have started), this script runs a loop watching the output ofGet-RSJoband updates a progress bar to reflect where we are. It also takes care of receiving and removing the completed jobs.
This is pretty easy to work with—or at least Andy makes it look that way.
Comments closedGarry Bargsley continues the 12 Days of dbatools series, this time taking a look at taking backups:
We are on the home stretch and I have saved the last third of the series to cover some pretty cool/amazing commands. Not that all 500+ commands are not cool/amazing but these last few are used continuously in my environment and bring automation closer to your finger tips with PowerShell and SQL Server then ever before.
Have you ever been asked to take a database backup before a developer does a deployment? Have you ever been asked to backup a database to restore it to a development or other environment? How about, hey can you take a quick transaction log backup? These can all be accomplished using Backup-DbaDatabase and only changing a couple of parameters each time.
This post is strictly about taking backups, but dbatools is also great about testing backups.
Comments closedWell, if you’re running a Server Core (I hope so, for domain controllers, dns, file services and more there’s no good reason to do not so), then it’s an ease to change that. The corresponding setting has to be configured in the registry. Regedit can be opened on Server Core (there are more graphical user interfaces that are shipped with core, for example notepad.exe and timedate.cpl). I’m talking about this key:
Click through for the instructions.
Comments closedJess Pomfret shows us an example of creating mandatory parameters but also having a default set:
I came across a situation this week where I wanted to add the option of running an existing script, for a specific server/database combination. The script currently has no parameters and runs against all servers in the environment from a scheduled task. I wanted to make sure that behavior didn’t change. The other requirement was that if I specified Server, Database should be a mandatory parameter and vice versa.
The final solution was to add the two parameters to a parameter set and make them both mandatory. I also had to add a different DefaultParameterSet (thanks to Andrew for this idea), otherwise it defaulted to the defined parameter set, meaning the script always required both Server and Database parameters.
Click through for a demo.
Comments closedGarry Bargsley has a series looking at different cmdlets in dbatools. Today’s focus is Agent jobs:
Agent Jobs are the heartbeat to most SQL Server environments. Jobs are how we move data, purge data, automate processes, backup databases, perform database maintenance and many other functions. Managing one SQL Server and the Agent Jobs can be a daunting task, much less managing 10, 20, 100 or more. Luckily dbatools has you covered!!! There are a wide range of commands that help you with Agent Jobs. There are commands to Copy jobs, Find Jobs, Get Jobs, Create New Jobs, Remove Jobs, Set Jobs Settings, Start Jobs and Stop Jobs. That is a huge range of functionality, so lets get started on the fourth day goodness.
Click through for descriptions and examples.
Comments closed