Category: Powershell

Get started

The article will include the following topics:

Get the list of services using PowerShell in C#.
How to send SSIS Parameters to PowerShell using the script task.
How to use the PowerShell.addscript function.
How to invoke a PowerShell script in C#

I think this is a fairly limited scenario—if you’re going to have to write C# code anyhow, you can do this same work in C#. I suppose that it would be most useful in cases where you have to call common Powershell cmdlets rather than writing your own .NET code.

Comments closed

ADO.Net In Powershell Core

Published 2017-07-17 by Kevin Feasel

Max Trinidad shows how to use Powershell Core on Windows or Linux to run T-SQL queries:

There’s a catch!

Nothing is perfect yet! Using the .NET Core version of System.Data, there’s a known issue with the datarow class. It seems it’s building the data results as string list of values without the column information.

But, there’s always a way to make thing work adding some extra code to work around this issue and reconstruct the data the way we want.

Max points out a couple of issues that exist today, but they’re getting resolved.

Comments closed

Copying Databases With dbatools

Published 2017-07-17 by Kevin Feasel

Mike Robbins has a video showing how to copy databases from several SQL Server instances using dbatools:

The video starts out by checking the default instance of SQL Server on a server named SQL17 to see if any user databases exist. Then the names of five different SQL Servers are piped to ForEach-Object. Within the ForEach-Object loop, $_ is a variable for the current object. It’s translated to each individual server name as it iterates through the list of SQL Servers, copying the user databases to SQL17. Only one user database exists on each of the source SQL Servers. The databases are backed up to the specified network share and restored to the destination server. The network share and any sub-folders that are specified must already exist. The account that SQL Server runs as on each of the servers must also have access to the network share. The names of the SQL Servers used in the demo correspond to the version of SQL Server they’re running. The SQL05 server is running Windows Server 2008 (non-R2) and does not have any version of PowerShell installed which means the Copy-SqlDatabase function is extremely versatile.

Click through to watch the video and see how quickly you can get going.

Comments closed

Deploying Reporting Services Reports With Powershell

Published 2017-07-17 by Kevin Feasel

Claudio Silva has a post covering Reporting Services Powershell cmdlets:

In this post I will share with you the request and how I have automated it saving a lot of time. Just to keep you interested, I went from 23 and a half minutes to 3 (your mileage may vary depending on the number of objects/actions that you need to do).

The request

Create new folder “FolderB”
We need to deploy a copy of the reports and data source to a new folder (“FolderB”). You should get the existing ones from the folder “FolderA” on the same server.
Then you have to change the datasource to point to the database “dbRS” with the login “ReportingUser”
Finally we need to change the data source for each report to match the new datasource pointing to database “dbRS” created on last step.

Click through for the code. Claudio even has a one-minute video showing his work in action.

Comments closed

Using SMO On Linux

Published 2017-07-14 by Kevin Feasel

Richie Lee explains how to get the SQL Tool Service running on Linux:

So, to briefly sum up, to use SMO on Linux, you need to do the following:

Install .NET Core 2.0

Install PowerShell beta 2

Install SQL Tool Service

You can use PowerShell from the Terminal, but I prefer something like an IDE so this is optional:

Download Visual Studio Code
Install PowerShell plugin
Change settings file to point explicitly to PowerShell beta 2.

Read the whole thing.

Comments closed

Integrating dbatools Into VSCode

Published 2017-07-10 by Kevin Feasel

Andrew Pruski shows how to integrate dbatools Powershell commands into Visual Studio Code:

The first dbatools commands that I looked at are: –

Get-LastGoodCheckDb

Get-LastBackup

These commands do exactly what they say on the tin. Pretty standard stuff for DBAs but what’s cool is how we can use Visual Studio Code to quickly and easily check that all our databases are being backed up and have a recent (good) CHECK DB.

I’m going to setup two scripts to run the dbatools commands against my SQL instances via Visual Studio Code Tasks.

Read on to see how Andrew did it.

Comments closed

Checking For Instant File Initialization

Published 2017-07-06 by Kevin Feasel

Klaas Vandenberghe shows how to use Powershell to determine whether Instant File Initialization is turned on:

Sometimes we want to apply a filter to an array or other collection of objects, but keep both the items that pass the filter and those that fail it. Instead of cycling twice through the collection, there’s a one-step method.

Instant File Initialization is a privilege assigned in the local security policy. Here’s some explanation by MSSQL Tiger Team.
There’s a lot to tell about it, but I’m not going to do that here. Let’s just assume it’s a good thing to assign that privilege to the account with which the SQL Service runs.

Klaas explains how to use Powershell filtering with Where-Object and the Where method for people new to Powershell, and then uses this to figure out if IFI is enabled.

Comments closed

Spell Checking With Visual Studio Code

Published 2017-07-06 by Kevin Feasel

Andy Levy shows how to create a custom dictionary for a programming language in Visual Studio Code:

But as you can see from the marketplace page there, by default this plugin doesn’t know PowerShell. In my user settings file settings.json, I added PowerShell to the cSpell.enabledLanguageIds section so it’s always recognized:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

"cSpell.enabledLanguageIds": [

        "c",

        "cpp",

        "csharp",

        "go",

        "javascript",

        "javascriptreact",

        "json",

        "latex",

        "markdown",

        "php",

        "plaintext",

        "powershell",

        "python",

        "text",

        "typescript",

        "typescriptreact",

        "yml",

        "powershell"

    ],

And with that, VSCode was giving me green squiggles under lots of words – both misspelled and not. Code Spellchecker doesn’t understand PowerShell in its default setup, it doesn’t have a dictionary for it. Just to get things started, I added a cSpell.userWords section to my settings.json and the squiggles started disappearing.

It’s an interesting post, so read the whole thing.

Comments closed

Test The DBATools Beta

Published 2017-07-05 by Kevin Feasel

Chrissy LeMaire wants you to test the beta of dbatools:

Before the official release of bagofbobbish to master and the PowerShell Gallery, we need help finding bugs. Then, we’ll need some time to resolve those bugs. Hopefully this can be done before community members show off dbatools at a few key SQLSaturdays around the world this Saturday, July 8th.

We would really appreciate it if you would download the beta from GitHub and (in a test environment) see if you can find anything that doesn’t work as expected.

If you find any bugs, please file a report on GitHub. You can also reach out to us in the Slack channel.

Currently, there aren’t any webpages for the commands listed in this post, but all commands have help, so when you need help, simply type Get-Help commandName -Examples or Get-Help commandName -Full.

Get testing. There are a lot of new commands, so if you haven’t checked out dbatools in a while, give it a go. Also, congrats to Rob Sewell for his newly minted MVP status.

Comments closed

Creating A Powershell Module

Published 2017-06-30 by Kevin Feasel

Rob Sewell has a two-parter. First, he looks at the SQL Server Diagnostics API:

The Diagnostic Analysis API allows you to upload memory dumps to be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix.

There is also the Recommendations API to view the latest Cumulative Updates (CU) and the underlying hotfixes addressed in the CU which can be filtered by product version or by feature area (e.g. Always On, Backup/Restore, Column Store, etc).

I have written a module to work with this API. It is not complete. It only has one command as of now but I can see lots of possibilities for improvement and further commands to interact with the API fully and enable SQL Server professionals to use PowerShell for this.

This alone is quite interesting. But then Rob shows how to turn this into a module, complete with tests:

I have been asked a few times what the process is for creating a module, using Github and developing with Pester and whilst this is not a comprehensive how-to I hope it will give some food for thought when you decide to write a PowerShell module or start using Pester for code development. I also hope it will encourage you to give it a try and to blog about your experience.

This is my experience from nothing to a module with a function using Test Driven Development with Pester. There are some details missing in some places but if something doesn’t make sense then ask a question. If something is incorrect then point it out. I plan on never stopping learning!

There are many links to further reading and I urge you to not only read the posts linked but also to read further and deeper. That’s a generic point for anyone in the IT field and not specific to PowerShell. Never stop learning. Also, say thank you to those that have taken their time to write content that you find useful. They will really appreciate that.

If you’re interested in developing professional-grade Powershell modules, this is a great starting point.

Comments closed