Press "Enter" to skip to content

Category: Powershell

Deploying VMs To Azure Using Powershell

Published 2016-12-20 by Kevin Feasel

Rob Sewell shows how to use Powershell to create your own Azure VM instance of the Microsoft data science virtual machine:

First, an annoyance. To be able to deploy Data Science virtual machines in Azure programmatically  you first have to login to the portal and click some buttons.

In the Portal click new and then marketplace and then search for data science. Choose the Windows Data Science Machine and under the blue Create button you will see a link which says “Want to deploy programmatically? Get started” Clicking this will lead to the following blade.

Click through for a screenshot-laden explanation which leaves you with a working VM in Azure.

Comments closed

Using DBATools For Backups And Restores

Published 2016-12-14 by Kevin Feasel

Chrissy LeMaire talks about the dbatools Powershell suite and its cmdlets related to backups and restorations:

Restore-SqlBackupFromDirectory is super useful in a pinch, too, but it’s not quite fleshed out to our standards, so it doesn’t have a corresponding webpage. We expect this will be renamed by the next release.

Again, I usually have all the docs for all of our newly released commands, but I was trynna make it for #tsql2sday.

Check out her post, and then check out dbatools.

Comments closed

Filtering Event Log Entries

Published 2016-12-13 by Kevin Feasel

Kevin Hill gets into Powershell:

What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.

Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉

One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.

1 Comment

Analysis Services Powershell

Published 2016-12-13 by Kevin Feasel

Aaron Nelson is advocating improvements to Powershell cmdlets around Analysis Services:

Frequently when developing updates to an SSAS cube I want to deploy my schema and process the dimension. Sometimes several of dimensions process successfully and then fails on one. At this point I go and correct the error, deploy the new schema, and then I only want to process all of my dimensions except the dimensions which did process successfully the first time. Sometimes this is really easy, but if you have a large number of dimensions this can become cumbersome since the only way to know which dimensions had been processed successfully or to right-click each dimension one at a time and find out, or to have memorized which dimensions had processed successfully on the earlier attempt. There can be a better way, and of course, PowerShell is one of those options. J The only problem is that as things currently stand, PowerShell is not as easy as it could be; the Invoke-ProcessDimension cmdlet doesn’t accept [direct] pipeline input. What is one to do when PowerShell isn’t as easy as it could be? File a Connect item of course!

Check out the Trello board.  It’s been instrumental in helping Microsoft developers get the leverage they need to dedicate time to improving particular aspects of the product.

Comments closed

Switching In Powershell

Published 2016-12-08 by Kevin Feasel

Chrissy LeMaire explain the switch command in Powershell:

Even less code and makes total sense. Awesome. There’s even more to switch — the evaluations can get full on complex, so long as the evaluation ultimately equals $true. Take this example from sevecek. Well, his example with Klaas’ enhancement.

The refrain with switch is, always make sure you cover every case and don’t let cases fall through when you don’t intend them to.  Fortunately, Powershell doesn’t allow fallthrough, so that makes it easier.

Comments closed

Lists And Ranges In Powershell And T-SQL

Published 2016-12-05 by Kevin Feasel

Phil Factor has an interesting post on lists and ranges in two languages:

When a  list like ‘1,3,5,6,9’, or ’12 Jan 2016,14 Jan 2016, 18 Feb 2016’  contains a datatype that can be unambiguously sorted in the order of the values of the datatype, it becomes possible to imply a range. This will trim unwieldy lists significantly if they have a lot of contiguous values.  ‘1,2,3,4,5’ can be expressed as 1-5 (or 1..5). The starting integer is separated from the end integer in the range by a dash sign. This representation rather clashes with the minus sign, represented by the same symbol, but the comma removes the ambiguity. A number followed immediately by a ‘-‘ means that the ‘-‘is a range symbol.  As with SQL’s BETWEEN clause that selects rows, the range includes all the integers in the interval including both endpoints. Because the range syntax is intended to be a more compact form,  it is generally only used where there are at least three contiguous values.

Interesting article.  I recommend checking it out.

Comments closed

Powershell Runspaces

Published 2016-12-05 by Kevin Feasel

Chrissy LeMaire has a pair of posts on runspaces.  First, a post which includes a runspace template:

Runspaces can be intimidating. I had heard about them, took a look at the code and was like “Ah, that looks complex. I’ll learn that later.” Because of this, I repeatedly went over the bulk insert code to familiarize the audience with the functionality that I was eventually going to multi-thread.

Then she shows us how to output results without waiting for all runspaces to complete:

Recently, I had a request to add multi-threading to Read-DbaBackupHeader. This was the first runspace in which I had to output the results from the runspace to the host — usually I just needed commands to run in the background, like with bulk-inserts to SQL Server.

Runspaces seems to be a good option in Powershell for multithreading.

Comments closed

Powershell Remoting To Linux

Published 2016-12-05 by Kevin Feasel

Max Trinidad shows that Powershell remoting works for connecting Linux to Windows and vice versa:

Connecting Linux to Windows

This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.

I need to see what Powershell objects for Linux currently exist; my guess is “not many, if any” but as those start getting fleshed out, I think even the most adamant of grep-sed-awk users will want to pick up at least a little bit of Powershell.

Comments closed

Comparing Active Directory Membership

Published 2016-12-01 by Kevin Feasel

Jana Sattainathan shows how to use Powershell to compare Active Directory group membership for two users:

Before we begin, if you are running Windows 7 or if you do not have the Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.

Let us call the new employee as NEW_USER and the existing employee as OLD_USER. Here is how to use PowerShell to do the comparison.

The retrieval and comparison code is pretty easy; click through to see.

Comments closed

Powershell And Python

Published 2016-11-29 by Kevin Feasel

Max Trinidad mixes two powerful scripting languages:

For this section we previously installed the python module pyodbc which is needed to connect via ODBC to any SQL Server on the network giving the proper authentication method.

The following sample code can be found this link: https://www.microsoft.com/en-us/sql-server/developer-get-started/python-ubuntu

This is probably more useful in larger shops with multiple operations personnel covering different domains, but it’s nice to know that both languages play nice.

Comments closed