Getting the latest Tools for PowerShell SQL Server Automation

You all know how important is to have the tool that can make our life easy do our system administration, and become a hero in our organization. Here’s a startup helper guide to get you going with some PowerShell and SQL Server tools.

What is available for automation!

For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)

Here are some important PowerShell Modules to use for SQL Server management scripting:

  • *SQLServer – This module currently can be use on SQL Server 2017 and greater.
  • *DBATools – This a community supported module that will work with SQL Server 2000 and greater.
  • DBAReports – Supports for Windows SQL Server.
  • DBCheck – Support for Windows SQL Server.

*Note: This module is coming popular in cross-platform systems (non-Windows)

All of the above module can be downloaded from the PowerShell Gallery from the PowerShell console using the Install-Module cmdlet.

Install-Module -Name SQLServer -Force -AllowClobber;

Now, when working with older versions of SQL Server (2008->2017), you will find the SQLPS module is loaded during the SQL Server installation.

Just remember, since SQL Server 2017, Microsoft has change the PowerShell SQLPS module to SQLServer module downloadable from the PowerShell Gallery. This module is not available in PowerShell Gallery, only available during the SQL Server installation.

When PowerShell SQL Server Module can’t provide a script?

It won’t hurt to install the SQL Server Management Objects (SMO) library in case you want to be creative and start building your own SQL PowerShell scripts. This library is already available cross-platform, meaning that it will work in Windows, Linux and MacOS environments.

In this case, you can install the SQL Server SMO library “Microsoft.SqlServer.SqlManagementObjects” from the PowerShell Console using the Install-Package cmdlet.

Install-Package -Name Microsoft.SqlServer.SqlManagementObjects -AllowPrereleaseVersions;

Wait! There is more

As you already know, to manage SQL Server in Windows environment, we use the SQL Server Management Studio. But, this
application won’t work cross-platform.

So, the cross-platform option available is Azure Data Studio (February edition):

Don’t forget to include for following extensions:

What about Python?

By now you should already know that Python has been around for many year as cross-platform interpreted object-oriented high-level language. And, its popularity keeps increasing.

I would recommend to take a look at the Anaconda Distribution, and specifically the one with the latest version of Python (v3.7).

Download Anaconda for data science platform:

This installation will include *All* Python packages available to build an application.

And, Python can interact with PowerShell too!

Ah finally Containers!

Yes! Containers has become popular and can’t be ignored. It can be use in both Windows, Linux and any cloud environments. Go ahead to learn how to work and manage Docker containers.

Docker site to Download the Docker CE.

Don’t forget to check Docker Hub to find the latest Docker Container images available for download. And, you will need to create an account before downloading images.  The image below shows how-to search for the SQL Server image.

In Summary

As technology will keep improving, make sure stay up-to-date. This give us the opportunity to improve our job position and be of value for the organization that hire us.

Don’t forget to look for the nearest technology event in your areas, as this is the opportunity to learn for free and gain invaluable knowledge.

Creating Multi-Dimension Arrays with PSObjects

While working on my book, on my chapter about working with objects, I went through a series of examples on using PSObject and Hash Tables which made me realized that creating NoteProperties is a thing of the past.  Thanks to the “-Property” parameter whe using the New-Object which allows you to use your Hash Table content and build your object Property member type.

New-Object PSObject –Property [HashTable]

Take a look at this previous blog post by the PowerShell Team: http://blogs.msdn.com/b/powershell/archive/2009/12/05/new-object-psobject-property-hashtable.aspx

So, while answering the forum post about “NoteProperties on an ArrayList”, I found myself creating a Multi-Dimension Array on a PSObject.    In my example, I’m trying the create two different Hash Tables, $o1 and $o2, containing the same property with some values.  Then, I’m adding them to the my $a1 PSObject variable using the “+=” operator and this have allow me to create a Multi-Dimension array.

Here’s the code:

## Creating multi-dimension array

[Array] $a1 = $null

$o1= @{ Num = “12”,”34″}

$a1 = new-object PSObject -Property $o1

foreach($i in $a) { $i.num }

$o2 = @{Num = “2”,”4″}

$a1 += new-object PSObject -Property $o2

$a1

foreach($i in $a1) { $i.num }

# Display one of the Dimension Element:
$a1[1].num[1]      # will return value = 4

$a1[0].num[1]      # will return value = 34

Here’s the image with the results:

image

Now, let’s take a look at the $a1 list of member types using Get-Member:

image

Yes, although we use the “-Property” in the New-Object, it will create the NoteProperty for you. 

So would you rather code using Add-Member:

Add-Member -InputObject $o1 -Type NoteProperty -Name Num -Value 12

Or, use the Hash Table concept:

$o1= @{ Num = “12”,”34″}

Well, just let your imagination go!!

Invoke-SQLCmd minor issue running some SQL Stored-Procedures…

First time I notice something strange with “Invoke-SQLCmd”, I was when executing the system Stored-Procedure “SP_Who2” and got the following error message:

PS C:\Users\Max> $sqlWho = Invoke-Sqlcmd “SP_Who2”
Invoke-Sqlcmd : The pipeline has been stopped.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users\Max>

I was trying to create a collection of object with the results from my “SP_Who2” and got the error stating “.. A column named ‘SPID’ already belongs to this DataTable..”.   So, I end up scripting out the SP_Who2, finding there are two columns with the same name ‘SPID’ and PowerShell didn’t like it.  I made the change to rename one of the columns to be SPID2 and save the T-SQL script to my PowerShell script file. 

And, the next time the ran my “Invoke-SQLCmd”, I had no problems and got my results so I could manipulate my .NET objects.

So, this is to make you aware that you will experience this minor issues when executing some of the SQL system stored-procedures.  And, for those who wonder… is this a BUG??  I really don’t think so!!  Because, the issue is in some of the system stored-procedures.  Should I submit this issue to the SQL Team to fix all stored-procedures generating columns with the same name?  This could be a major and unnecessary task.  Anyway, you were served!!

Happy PowerShelling!!

PowerShell Modules – How to create Aliases for my functions?

There’s tons of information about PowerShell Modules on the internet.  Then I realized, in my PowerShell Modules, How do I create Aliases for my functions?  I couldn’t find any information on the internet about Aliases in modules.  So, thanks to Ed Wilson (Microsoft Script Center), he gave me a hint in one of his articles: http://blogs.technet.com/b/heyscriptingguy/archive/2010/02/21/hey-scripting-guy-february-21-2010.aspx

I’m assuming you already know how to create a module *psm1 file. Well, don’t worry!  The following sample script will get you started.  And, if you want to convert a PowerShell script to a Module file, is as simple as copy the *ps1 to *psm1 file.  Basically, that’s it!!

So, before I created my module file, I first created a PowerShell script “HelloWorld.ps1” which contains the following code:

## NAME: HelloWorld.ps1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)
           Write-Host "Hello World!!.." $arg
};Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"

After verifying that my function works, then I copy/rename my PowerShell script to PowerShell module file “HelloWorld.psm1” and saved it in my new module folder name “HelloWorld”.

Now, here’s what I found when creating my Aliases for my module:

  1. In the *psm1 file, I could use either: Set-Alias or New-Alias but not both when buidling my manifest.  One catch, I discovered, if I use the ‘-Description’ parameter in either Cmdlets, the function description won’t load with the module.  (its a possible bug)
  2. The last line in the *psm1 file need to have the Cmdlet “Export-ModuleMember” (a shown below) to include the Aliases.
  3. I think is a good practice to named you PS Module script the same way your Module folder.
  4. Create the Module folder name and place the module *psm1 file in it.

Here’s sample script code:

## PS Module Name: HelloWorld.psm1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)    ?
   Write-Host "Hello World!!.." $arg
};
#-- Set-Alias will work but can't be New-Alias
#Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"
New-Alias -Name shw -value Show-HelloWorld -Description "Sample Hello World in module"

#-- Need to be included at the end of your *psm1 file.
export-modulemember -alias * -function *

Now, we need to create our new module folder under our users documents “..\WindowsPowerShell\Modules” called “HelloWorld”.   Using our PowerShell console, we need to create our PowerShell manifest *psd1 file using the following line and pressing enter to all other prompts:

New-ModuleManifest -Path 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1' -ModuleToProcess 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'
PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\Win...
' -ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\...

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:

Keep in mind, to start using New-ModuleManifest, you only need to use two parameters: “-path” – location to store your manifest file and “-ModuleToProcess” – location of your module file.

Don’t be afraid to skip the prompts, you can always go back into your recently build manifest file *psd1 and manually filled the information.  After successfully running the “New-ModuleManifest” and created the manifest file “HelloWorld.psd1”, I can proceed to load our new module using “Import-Module HelloWorld”.  If there were any errors, I would need to go back to file my *psm1 module file.

I’m are ready to test the new “HelloWorld” module executing the “Import-Module HelloWorld” im the PowerShell Console:

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1'
-ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:
PS C:\Users\Max>
PS C:\Users\Max> Import-Module HelloWorld
PS C:\Users\Max> Get-Alias Shw

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           shw                                                 Show-HelloWorld

PS C:\Users\Max> shw
Hello World!!..
PS C:\Users\Max> shw Max
Hello World!!.. Max
PS C:\Users\Max>

Now, I can either use my new Cmdlet function “Show-HelloWorld” or just use the Alias “shw” to execute my function.  That’s it!!  I’ve just created, loaded and tested a new module in our PowerShell Console.  There’s room to grow here!! 

Remember, Don’t TAP OUT!!  Happy PowerShelling!!