Press "Enter" to skip to content

Category: Powershell

The Importance Of Powershell

Published 2017-01-20 by Kevin Feasel

Kevin Hill explains why he’s advocating that DBAs learn Powershell:

2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:

1 – Its very useful for admin at the O/S level.

At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way.  This means we need to be able to manage the modest server farm we have.  Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset.  So…PS to the rescue!

Read on for the other reason.  I think the relatively poor Powershell tooling with SQL Server (with respect to other groups like Exchange) limited general acceptance, but they’ve made some big improvements over the past year and there are some sharp minds in the community working to make Powershell even more important for DBAs.

Comments closed

ApplicationName On Invoke-SqlCmd2

Published 2017-01-20 by Kevin Feasel

Andy Levy improves the Invoke-SqlCmd2 cmdlet:

I decided to change this around so that it no longer uses string formatting, but instead a SqlConnectionStringBuilder. I had a couple reasons for this:

  • It will eliminate redundant code. There are several common elements in each of the ConnectionStrings above. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.

  • It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.

This is something I’d like to see make it to the main cmdlet.

Comments closed

Reading Extended Event Data From Powershell

Published 2017-01-10 by Kevin Feasel

Dave Mason builds a Powershell script to parse Extended Events information:

Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some Posh-specific notes.

Read on for the code.

Comments closed

Deleting SSAS Partitions

Published 2017-01-06 by Kevin Feasel

Chris Koester shows how to use TMSL and Powershell to delete an Analysis Services tabular model partition:

The sample script below shows how this is done. The sequence command is used to delete multiple partitions in a single transaction. This is similar to the batch command in XMLA. In this example we’re only performing delete operations, but many different operations can be performed in sequence (And some in parallel).

Click through for a description of the process as well as a script to do the job.

Comments closed

Always Encrypted With Powershell

Published 2017-01-06 by Kevin Feasel

Jakub Szymaszek shows how to configure Always Encrypted support from Powershell:

Note: In a production environment, you should always run tools (such as PowerShell or SSMS) provisioning and using Always Encrypted keys on a machine that is different than the machine hosting your database. The primary purpose of Always Encrypted is to protect your data, in case the environment hosting your database gets compromised. If your keys are revealed to the machine hosting the database, an attacker can get them and the benefit of Always Encrypted will be defeated.

That’s a good warning.

Comments closed

Loading SMO In Powershell

Published 2016-12-23 by Kevin Feasel

Chrissy LeMaire shows how to load SMO with full names when you don’t know which version is installed:

In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as “Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”.

Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.

I get that LoadWithPartialName is sketchy, but the solution that Chrissy has to do seems overly complicated to me.  Nonetheless, those are the rules of the game now, I suppose.

Comments closed

New Powershell And SQL Server Previews For Linux

Published 2016-12-21 by Kevin Feasel

Max Trinidad notes that there are new versions of Powershell and SQL Server previews available for Linux users:

To download the latest PowerShell Open Source just go to the link below:

https://github.com/PowerShell/PowerShell

Just remember to remove the previous version, and any existing folders as this will be resolved later.

To download the latest SQL Server vNext just check the following Microsoft blog post as the new CTP 1.1 includes version both Windows and Linux:

SQL Server next version Community Technology Preview 1.1 now available

Max has additional links and resources in that post as well.

Comments closed

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