powershell

Monday Morning Module Maintenance Monoliners

Andy Levy
Or: Andy Authors An Amazing Alliteration Do enough work with PowerShell and you’ll build up a decent collection of modules installed from the gallery into either your computer or your user profile (or maybe both!). Here are two one-liners to help keep things up to date and tidy. Note: I’m calling these one-liners but I’ve inserted line breaks for readability here. These do count as one-liners as each one is a single, unbroken PowerShell pipeline.

SQL Saturday Albany Slides & Demo Code

Andy Levy
Thanks to everyone who came out to see dbatools for the Uninitiated at SQL Saturday Albany on July 20th, 2019. I had a lot of fun sharing dbatools with you and hope you’re ready to start exploring on your own! The slides and demo scripts are available in my GitHub repository. If you have any questions about the session, please feel free to contact me via: Email Slack (@alevyinroc) Twitter

SQL Saturday Albany NY 2019

Andy Levy
I am pleased to announce that I will be presenting at SQL Saturday #855 in Albany, NY on July 20, 2019. Join me at 2 PM in room LC05 for “dbatools for the Uninitiated” You’ve just inherited a large SQL Server estate, and next month’s merger will double the number of instances you’re responsible for. Or maybe you have one big instance with thousands of databases on it. Are there backups?

dbatools One Point OH YEAH!

Andy Levy
Announced at DataGrillen 2019 today, the amazing dbatools PowerShell module has officially released version 1.0. This is a tremendous milestone for the best Open Source project built for data professionals. What started out as a single PowerShell script for migrating SQL Server instances in Chrissy LeMaire’s (blog | twitter) datacenter has become the most important and comprehensive Open Source toolkit for SQL Server database administrators and developers. Whether you’re managing one server or one thousand, this module is an indispensable tool which will make your day more productive and less error-prone.

Tidier PowerShell Scripts with Default Parameter Values

Andy Levy
I was recently working on a PowerShell script to set up some new databases for my users and found myself writing the same things over and over again. 1 2 3 Invoke-DbaQuery -SqlInstance MyServer -Database $SrcDB -Query "select field1 from table..."; Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "update table..."; Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "insert into table..."; By the 4th Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.

A Monumental Migration to SQL Server 2016 - Part 2

Andy Levy
In my previous post, I outlined the preparations we undertook to migrate a large SQL Server 2008R2 instance to SQL Server 2016. This post details migration day. Final Prep We completed our nightly backups as usual on Friday night, so when I arrived Saturday I kicked off a final differential backup to catch any overnight changes. We’ve multi-threaded Ola’s backup script by creating multiple jobs and I started them all at once with (of course) PowerShell.

A Monumental Migration to SQL Server 2016 - Part 1

Andy Levy
A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016.

Thread-safe PowerShell Logging with PSFramework

Andy Levy
In my previous post, I mentioned that I wasn’t checking the status of my RSJobs because I had logging built into my function. I originally tried to log out to plain text files with Out-File, but quickly hit a wall. In a multi-threaded script, attempting to write to the same text file from multiple threads results in collisions and waits at best, and outright failure at worse. Perplexed, I turned to the PowerShellHelp channel in the SQL Community Slack and asked for suggestions for thread-safe logging to text files.

PowerShell Multithreading with PoshRSJob

Andy Levy
Intro PowerShell has had a native method for spawning multiple “threads” ever since I can remember, in the form of the *-Job functions. They work OK, but there are a couple downsides: Each job is its own PowerShell process, so it takes a non-trivial amount of time and memory to spin each up There’s no built-in method for throttling the number of concurrent jobs This combination will become an ugly mess if something spins out of control and you spawn dozens or hundreds of jobs.

Copying Individual Tables with dbatools

Andy Levy
@SQLMonkeyNYC asked on Twitter this morning: #sqlhelp Does anyone have a product that will back up a single table and restore it to another database? Thanks in advance!! — The SQL Monkey (@SQLMonkeyNYC) November 14, 2018 Pat Phelan replied, suggesting that dbatools can do it, but after thinking on it for a bit and poking at a few functions, I realized that it’s not possible with a single function (yet). No worries.