Press "Enter" to skip to content

Category: Powershell

Using Show-Command with dbatools

Published 2019-08-06 by Kevin Feasel

Garry Bargsley shows how you can “build” a GUI for any Powershell cmdlet, using one from dbatools as an example:

The Show-Command cmdlet will display in a graphical interface all the parameter options related to a function, cmdlet or workflow.

I always recommend the book “Learn Windows PowerShell in a Month of Lunches” for people that ask me how to get started with PowerShell.  It is a very thorough book and I believe helps build the foundation you need to user PowerShell to the fullest.  Earlier this month I spent time going through the book for maybe the third time.  I must have blown right by the Show-Command cmdlet which shows up in Chapter 4, as I do not ever remembering seeing it before.  Then a couple of weeks ago Kenneth Fisher ( b | t ) post a blog about this command.  I like how Kenneth related the Show-Command in PowerShell to generating scripts with SSMS.  This is a great comparison for the DBA to understand.  After reading his blog post I thought, wow, this might be a handy tool to combine with my favorite module out there dbatools.

Click through for a demonstration.

Comments closed

Getting Powershell 7 Preview on Ubuntu

Published 2019-08-01 by Kevin Feasel

Max Trinidad shows us how to get the Powershell 7 preview without using apt:

First, look under the release documentation and search for the deb package. In my case I’m install the amd64 version.

Then, right-click on the “powershell-preview_7.0.0-preview.2-1.ubuntu.18.04_amd64.deb”, and select “Copy link address“.

This is also something you could script out. It’s quite useful in scenarios where your apt repositories don’t have the latest version of something but you still need or want it.

Comments closed

SQL Assessment API

Published 2019-07-30 by Kevin Feasel

Ebru Ersan announces a public preview of the SQL Assessment API:

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.

Looks like you can customize rules as well. I wonder if it will work better (or have more support) than Policy-Based Management. I’m also clocking how many minutes before dbatools supports this…

1 Comment

Data Aggregation with Powershell

Published 2019-07-29 by Kevin Feasel

Jess Pomfret shows us how we can aggregate data using Powershell:

As a SQL Server DBA, aggregating data is first nature.  I can easily throw together some T-SQL if I need to get the average sales per product group, or perhaps the number of employees hired per month. Yesterday I was writing a little PowerShell when I came across a problem. I needed to get the size of my database data and log files for several databases on a server, when I realized I didn’t know how to group and sum this data in PowerShell.

Click through to learn how.

Comments closed

The SQL Notebook Experience, Featuring Powershell

Published 2019-07-23 by Kevin Feasel

Rob Sewell takes a break from book-writing and talks about using Powershell in SQL Notebooks:

Yes, it’s funny but also it carries a serious warning. Without understanding what it is doing, please don’t enable PowerShell to be run in a SQL Notebook that someone sent you in an email or you find on a GitHub. In the same way as you don’t open the word document attachment which will get a thousand million trillion pounddollars into your bank account or run code you copy from the internet on production without understanding what it does, this could be a very dangerous thing to do.

With that warning out of the way, there are loads of really useful and fantastic use cases for this. SQL Notebooks make great run-books or incident response recorders and PowerShell is an obvious tool for this. (If only we could save the PowerShell output in a SQL Notebook, this would be even better)

“It’s a bit hacky” is a generous statement, but it’s really cool that Rob figured out a way to do this. There is a Powershell kernel for Jupyter, but I’ve not had the best experience adding new kernels to Azure Data Studio (at least not F#’s kernel, which I tried).

Comments closed

Multi-Server Patching with dbatools

Published 2019-07-22 by Kevin Feasel

Eric Cobb shows how you can use dbatools to upgrade multiple SQL Server instances:

Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you’re going to be spending a considerable chunk of time on it.

Thankfully, the dbatool Powershell Module makes this process much easier! In fact, once you get it figured out, using Update-DbaInstance to patch your SQL Servers becomes very simple. But, getting it figured out can be a little confusing, so I’ve outlined the steps below that I use. The biggest hangups I had were setting up a central patching location and using a credential to access it.

Click through to see an example of this in action.

Comments closed

Powershell Modules Everywhere

Published 2019-07-18 by Kevin Feasel

Kevin Chant has some thoughts on whether you should have SQL Server Powershell modules on every server:

Recently I’ve seen recommendations about putting PowerShell modules on every SQL Server. I must admit it has got me thinking if this is indeed worthwhile.

In addition, it makes me wonder if it’s actually better to put the Powershell modules on a select number of management servers instead?

If you are wondering which modules I could be talking about, I mention some in a previous post which you can read in detail here.

Read on for Kevin’s thoughts on the matter.

Comments closed

Determining Your OS in Powershell

Published 2019-07-17 by Kevin Feasel

Patrick Gruenauer shows how you can determine your operating system in Powershell Core 6 and Powershell 7:

Recently, I discovered three PowerShell Core variables that could be very helpful when it comes to determining the operating system in PowerShell. PowerShell has become a cross-platfrom tool and can be installed on Linux or MacOs, too. So it could happen that you have to determine the operating system first before starting any other actions.

Click through to see what they are and how to use them.

Comments closed

Troubleshooting DSC in Push Refresh Mode

Published 2019-07-17 by Kevin Feasel

Jess Pomfret continues a series on Powershell Desired State Configuration:

One of the biggest obstacles people face when using DSC is the troubleshooting and reporting pieces. There are options here to integrate with third party tools to create a more polished enterprise solution, but if you’re going with just straight DSC you might feel it is lacking some in this area.

We do however have several tools available to troubleshoot issues with configurations or to monitor our nodes to determine whether they are still in the desired state. I’m specifically going to look at the options available if you’re using DSC in the Push refresh mode.

Click through for discussion of the PSDesiredStateConfiguration module as well as DSC-related event log messages.

Comments closed

Powershell and Windows Terminal Profiles

Published 2019-07-16 by Kevin Feasel

Jeffery Hicks shows how you can modify your Windows Terminal profile using Powershell:

I recently updated my Windows 10 systems to the 1903 release. One of the reasons is that I wanted to try out the new Windows Terminal preview. You can find it in the Windows Store. This is bleeding edge stuff and far from complete but promises to a great addition. Now you will be able to have all your command terminals, in one tabbed application and easily be able to switch between them. As I said, this is far from being a finished and polished product. Right now, if you want to add a new profile, that is another terminal, you have to manually edit a json file. If you have VS Code installed, the file will open in that.  Otherwise, I’m assuming you’ll get whatever application is associated with the .json extension.

Read on for a Powershell one-liner which lets you create a terminal profile.

Comments closed