Press "Enter" to skip to content

Category: Powershell

Cross-Platform Powershell Remoting

Published 2017-06-14 by Kevin Feasel

Anthony Nocentino shows how to enter Powershell sessions using OpenSSH-basted remoting:

Nothing special here, simple syntax, but the seasoned PowerShell remoting pro will notice that we’re using a new parameter here -HostName. Normally on Windows PowerShell you have the -ComputerName parameter. Now, I don’t know exactly why this is different, but perhaps the PowerShell team needed a way to differentiate between OpenSSH and WinRM based remoting. Further, Enter-PSSession now has a new parameter -SSHTransport which at the moment doesn’t seem to do much since remoting cmdlets currently use OpenSSH by default. But if you read the code comments here, it looks like WinRM will be the default and we can use this switch parameter to specify SSH as the transport.

Once we execute this command, you’ll have a command prompt to the system that passed as a parameter to -HostName. The prompt below indicates you’re on a remote system by putting the server name you’re connected to in square brackets then your normal PowerShell prompt. That’s it, you now have a remote shell. Time to get some work done on that server, eh? Want to get out of the session, just type exit.

It’s interesting to see how well Microsoft is integrating Linux support into Powershell (and vice versa, but that’s a different post).

Comments closed

Powershell And Environment Variables

Published 2017-06-13 by Kevin Feasel

Adam Bertram explains how to use environment variables in Powershell:

Environment variables are exposed with a PowerShell drive known as “$env:”. It’s possible to browse through all of the environment variables by typing $env: at the console and hitting the tab key. This will allow you to see the names of each environment variable in alphabetical order.

The $env: drive is the recommended place to refer to any environment variables with PowerShell. However, it’s possible also to read the variables via .NET in PowerShell by using the GetEnvironmentVariable static method on the Environment class. This is essentially the same task.

Read on to see how you can use these in your scripts.

Comments closed

Migrating Tables Using Powershell

Published 2017-06-13 by Kevin Feasel

Jana Sattainathan has a script to copy a table and its associated indexes from one database to another:

Recently I got a request from a user that he wanted to copy a specific set of tables and their indexes into a new database to ship to the vendor for analysis. The problem was that the DB had thousands of tables (8,748 to be precise). Hunting and pecking for specific tables from that is possible but tedious. Even if I managed to do that, I still have to manually script out the indexes and run them in target as the native “Import/Export Wizard” does not do indexes. It only copies the table structure and data! I am not a big fan of point and click anyway.

My first thought was to see if dbatools had something similar, though a quick glance at the command list says perhaps not yet.

Comments closed

Creating Powershell Documentation In VS Code

Published 2017-06-12 by Kevin Feasel

Rob Sewell has a post covering a nice addition to Visual Studio Code when you’re building Get-Help documentation for a cmdlet:

Now you can simply type <# and your help will be dynamically created. You will still have to fill in some of the blanks but it is a lot easier.

Here it is in action in its simplest form

But it gets better than that. When you add parameters to your function code they are added to the help as well. Also, all you have to do is to tab between the different entries in the help to move between them

Looks like a nice time-saver.

Comments closed

Linux SMO In Powershell Core

Published 2017-06-12 by Kevin Feasel

Max Trinidad wins the technology mix-in competition of the day, using Powershell Core to access SQL Server SMO on a Linux instance:

In my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file

As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.

Just in case, here’s the link to download “.NET Core 2.0 Preview 1“: https://www.microsoft.com/net/core/preview#windowscmd
https://www.microsoft.com/net/core/preview#linuxubuntu

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:
https://github.com/PowerShell/PowerShell/releases

Read the whole thing.

Comments closed

Synchronizing Logins And Jobs

Published 2017-06-09 by Kevin Feasel

Ryan Adams enumerates several methods for synchronizing logins and SQL Agent jobs across mirrored instances or Availabilty Group replicas:

There is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent or you can just use Scheduled Tasks in the OS.  The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server.  Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.

Chrissy wrote this with the intent of making migrations easier, and she succeeded.  In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier.  The advantage here is that you can automate a lot more than than just logins.  In fact you can migrate and automate pretty much anything at the server level.  Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.

https://dbatools.io/

DBATools would be my preference in this situation as well, but click through to see four other methods, as well as code.

Comments closed

Basic Parsing On Invoke-WebRequest

Published 2017-06-08 by Kevin Feasel

Andy Levy shows how to handle complex redirects with Invoke-WebRequest in Powershell:

So off to the PowerShell prompt I went and ran Invoke-WebRequest -Urihttp://firstresponderkit.org/ to start looking at the object returned so I could see what I needed to parse out to find my way to the true download URL.

Then Firefox (my default browser) opened, and I was staring at https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/tree/master.

I was expecting an HTTP 30X redirect status code which, based upon previous experience, Invoke-WebRequest would honor. Instead, I got a 200 OK which is the web server saying “yep, here’s your stuff, HAND!”

Read on for the solution.

Comments closed

Production-Quality Powershell Functions

Published 2017-06-07 by Kevin Feasel

Missy Januszko has some tips on turning those Powershell scripts into reusable functions:

Breaking down your code may mean chopping apart your lengthy script into smaller pieces. As a best practice, a function should do only one thing. A retrieval cmdlet retrieves information and sends that information to the pipeline. Conversely, a functional cmdlet performs an act but not a retrieval act. It may take input from another cmdlet and act upon that input. It may or may not send output information to the pipeline. Lastly, output cmdlets format output in a desired display. As a result, this will allow us to use the pipeline more effectively to pass parameters between functions. In the above example, most of the function is a retrieval function. The exception is that it formats the output into a table with the last line. I will remove that line and let the user of the function decide how they want it formatted.

As a friendly warning to operations folks who are using more and more Powershell, when you do it right, you end up being a developer.  But we can keep that a secret, just between you and me.

Comments closed

Hash Tables In Powershell

Published 2017-06-06 by Kevin Feasel

Adam Bertram explains what hash tables are and why they’re useful:

Notice that each of the keys is unique. This is required in a hash table. It’s not possible to add two keys with the same name. Below I’ve defined the SomeKey2 key twice.

We’ve just talked about creating a hash table and adding key/value pairs at creation time. It’s also possible to add keys to an already created hash table in a number of different ways, either through dot notation, enclosing the key in brackets, or by using the Add() method. Each approach has the same effect.

Hash tables are quite useful in Powershell for storing key-value pairs, like if you’re building a dictionary of configuration settings.

Comments closed

Multi-Database Query Store Data Retrieval

Published 2017-06-05 by Kevin Feasel

Tracy Boggiano wants to get Query Store data for a large number of databases on the same instance and has written a Powershell script to this effect:

In SQL Server 2016, Microsoft introduced to us the Query Store.  This has been proven to be a very beneficial feature especially at my job, saved the day on Christmas Eve. One of the limitations if see is when you have server with several databases on it trying to find the one that has the problem. So until Microsoft gives use a better tool or someone builds one I wrote a quick PowerShell function to query across all the databases and return data to a grid so you can sort and find that troublesome query.

To call it simply provide the server, which metric you want, the top number of queries to return, who many hours to look back, and the minimum execution count.

This is one of two issues I have with Query Store, the other being that its data gets saved to the primary filegroup without any recourse.  Click through for the script.

Comments closed