Checklist for troubleshooting compilation of DSC Configuration in Azure Automation

I was recently working on a solution where a DSC Configuration block was not compiling in Azure Automation. This solution included Node Data, DSC Resources, and custom Composite Resources. The configuration would compile perfectly fine locally but not when published to Azure Automation. The other challenging aspect was that other composite resources within the same module were compiling fine.

Unfortunately Azure Automation doesn’t provide very detailed information for troubleshooting DSC Compilation errors. It actually will only show you the first level failure, which when your using a DSC Composite Resource it means you will simply receive an error that the composite resource failed to import, but the actual cause could be related to a problem with an underlying resource used by that composite resource.

So based on my experience I have come up with the following troubleshooting checklist when working through DSC Compilation errors in Azure Automation.

Troubleshooting Checklist

1. Check the exception details output by the DSC Configuration compilation job that is in the suspended state. Either within the Azure Portal like the following screen shot or via PowerShell by the Get-AzureRmAutomationDscCompilationJobOutput cmdlet.
   
   
   
   Depending on the exception reported the next steps may vary. In the above screenshot it is reporting that a Composite Resource has failed to import.
2. Can you compile the configuration locally?
1. If yes, can you upload the MOF to the DSC Node Configurations in the Azure Automation account?
3. Are all required Modules referenced by your Configuration(s):
1. Uploaded to your Azure Automation account
2. Up-to-date (see next point though)
3. Match the required version by your Configuration block or Composite Resource
4. If it is a Composite resource that is failing, are all Composite resources within your module affected or is it just a subset?
5. If it is a Composite Resource, extract the configuration from the failing Composite Resource and place it directly in a Configuration block. Compile that configuration block in Azure Automation and review the output as this will provide more granular details about the specific resources used by that configuration block.
6. Try simplifying the DSC Configuration block to reduce the number of DSC Composite resources or other resources being compiled to help narrow down the culprit

You should also read the "Common errors when working with Desired State Configuration (DSC)" section in the official documentation https://docs.microsoft.com/en-us/azure/automation/automation-troubleshooting-automation-errors

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Installing SqlServer PowerShell module from PowerShellGallery.com

With the release of SQL Server Management Studio 17.0 (SSMS) the SqlServer PowerShell module has finally been decoupled and now can be installed independently. To take full advantage of this change I recommend using PowerShell 5.0 (if your on Windows 10 or Server 2016 then this is your default).

Full details of the SSMS 17.0 updates https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

The official installation details for SqlServer module are at
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module but they are a bit light on currently so this post will walk through the process in more detail. You can also find the public listing of the SqlServer on PowerShellGallery.

Firstly check what modules you have installed.

Get-Module -Name SQL* -ListAvailable

If you have installed the SQL Server DBEngine then you should have SQLPS for that particular version. On my server I upgraded SSMS 2016 to SSMS 16.0 so I have both 130 and 140 versions installed.

What I don't currently have installed is the SqlServer PowerShell module, and my SQLPS module hasn't been updated with the upgrade of SSMS to version 17.0

So let's download and install the latest SqlServer module.

First, check your PowerShellGet is configured for the public gallery (the default setup).

Get-PSRepository

You should have the PSGallery repository setup. By default it will be untrusted, this will just mean you get prompted before installing any modules from it. It is recommended public repositories should not be marked as trusted, but internal private repositories can be to make the installations easier.

If you don't have any other repositories configured then you won't have to supply the repository name to any future commands, but if you do then you can simplify the module searches by specifying the repository name. This is very useful if you want to host private custom copies of the publicly available modules on a private repository.

Now you can search for the module in the gallery to check what the most recent version is available

Find-Module -Name SqlServer

NOTE: If prompted to continue enter 'y'

Add the -AllVersions parameter to list all the available versions. Currently there is only one version in the gallery but this will change overtime.

To install the module from the gallery run Install-Module. Use the Scope parameter to install for either CurrentUser or AllUsers based on your needs. With PowerShell 5.0 it is also possible to install versions side-by-side. In my case I am installing the module for all users and requiring a specific version (the current one).

To avoid the error message like "A command with name '' is already available on this system" I will also use the parameter -AllowClobber.

Install-Module -Name SqlServer -Scope AllUsers -RequiredVersion 21.0.17099 -AllowClobber

Now check that the module is available for use on the server with

Get-Module -Name SqlServer -ListAvailable

Notice that the path is now one of the common $PSModulePath paths. This is also part of the improvements that these changes bring.

Now going forward to update the module you can use the Find-Module and Update-Module cmdlets. Look out for my post on automating that process next month.


Happy PowerShell'ing


Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Cmder, enhance your Windows Terminal Console experience

One of the best new tools I have been using lately is Cmder. A Terminal Emulator for Windows which is great for a number of reasons:

• Tabbed terminals 
• Multiple languages can be emulated
• Fully customisable
• and best of all Quake mode

If you haven't seen it yet check it out http://cmder.net

I do a lot of work with PowerShell, Git and usually have many things on the go at once. What I really like about Cmder is that you can enable Quake mode which hides the terminal window at the top of your monitor screen and allows a keyboard short cut (Ctrl+`) to show/hide it. Making it very quick to open it over the top of other applications your working in (e.g. VS Code) and run commands. For example, when I am working on development projects I usually have VS Code open to edit the source code files and then I will have a PowerShell or Bash terminal emulated in Cmder for all my Git commands on the repo. Of cause you could just use the Git feature of VS Code in this case, but I usually always find there is some conflict I have to resolve at the Git console so just prefer to work in that. Plenty of other reasons I use Cmder too.

To get you started here is my quick setup steps which will set it up at least with Quake mode and your favorite terminal as default.

1. Download and install Cmder from http://cmder.net
2. Open Cmder.exe from the location you extracted it to.
3. Click on the 3 bar menu item in the bottom right
   
   
4. Select Settings
   
   
5. I have a Surface Book as my laptop which comes with a very large resolution (3000x2000) so I found I needed to change the "Size & Pos" settings, but obviously this is personal preference.
   
   
6. Click the Main > Quake style option in the setting menu. Ensure "Quake style slide down" is enabled and any other options as you require.
   
   
7. Select the Startup option from the settings menu and select the desired default task in the "Specified named task" drop down.
   
   
   
   This will give you the option of setting one of the default tasks as your default startup terminal. Such as Cmd.exe, PowerShell, Bash, etc. If you require anything more specific then use the Tasks section to define your own custom task. This is handy too for extending the functionality of Cmder, like setting up a SQLCMD terminal or specific PowerShell terminals for different technologies (e.g. Azure, SQL, Exchange, etc).
8. If you want to have Cmder start at logon, something I have found increasingly important as I grew to depend on Cmder for my terminal use. Select the Integration > Default term option in the settings menu. Then check the "Register on OS Startup" option. NOTE: For this setting to work you also need to set the "Force ConEmu as default terminal for console applications", and you will be prompted to do so if you don't check it first. This setting will have no effect without that setting also enabled.
   
   
9. Once you have configured the required setttings, such as Task Bar minimisation and other desired behaviors, click Save Settings.
10. Now return to your other applications, when you need a terminal press Ctrl+` (that is Ctrl plus tilt character) and the cmder terminal will load.

For documentation of any of the setting see: https://conemu.github.io/en/TableOfContents.html#settings

Some handy keyboard shortcuts, for the full list see http://cmder.net

Ctrl+` : Show/Hide the cmder terminal

Ctrl+Numer : Switch to that active terminal tab

Shift+Alt+Number : Fast new tab (e,g, Shift+Alt+1 for Cmd.exe or Shift+Alt+2 for powershell)

For the official documentation see https://conemu.github.io/en/TableOfContents.html

Happy terminal emulating

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

New SQL Server Builds API.... improve your health checks with version and support information

Things have been a bit busy for me personally over the last few months but that is another story. What I have done is use some of this time to get back to a little project I started a while back.

Introducing the SQL Server Builds API..... providing version information including release dates, and known support life-cycle information.

Overview

The SQL Server Builds API provides SQL Server Version information and known support life-cycle information. As it is a personal project of mine the data is currently manually maintained based on known sources. The main objective of the tool is to enhance health check and project planning type processes, it is not intended for licensing or other legal auditing processes.

In prior roles and particularly in my current role one of the most common checks I perform on a SQL Server is the version and also the patching process. To aid in this what I would do is run reports based off my own custom database of version numbers to check and validate the version of the instance being checked.

Querying the API
To query the API it is pretty simple. The base URI is http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions

The API takes two parameters

• Version - This is a string representation of the version of your SQL Instance returned by SELECT SERVERPROPERTY('PRODUCTVERSION') e.g. '12.00.2342.00'
  
• MissingUpdates - A boolean value (True/False) of if to include any updates released since the current patch level in the results

So if I wanted to query for information about the version number 12.00.2342.00 my completed URI would be something like:

http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions?Version=12.00.2342.00&MissingUpdates=false

NOTE: The MissingUpdates param defaults to false so you could omit it from the URI above.

Where the data comes from

The official source of truth with life-cycle data has to be Microsoft Support Life-cycle (https://support.microsoft.com/lifecycle), and this API/tool is by no means trying to replace that. In fact if you are doing any official auditing for licensing or any other legal means you MUST use that source over this API's data. At least perform your own validation of the data for each record.

Most DBA's and SQL admins would refer to http://sqlserverbuilds.blogspot.com.au. I am no different and I would previously refer to that site as well. So, it is only natural that it is still one of the sources I go to for building my API's database.

The database behind this API is currently manually maintained, and even if it was somewhat automated mistakes can happen. If you find I am missing any updates, or if there is any incorrect data please make sure you reach out and let me know.

Suggestions for using the API

The API could be used in any number of tools and checks. Here are just some I can think of:

• Custom health check scripts
• Enhance on-premise CMDB data
• PowerBI reports
• Other custom Applications that interface/manage SQL Server instances

PowerShell Examples

#1 - Get version information (static version number)

# Set the version number to test
$VersionNumber = '12.00.2342.00' # SQL 2014

# Call the API to get the version information
$VersionData = Invoke-RestMethod -Uri "http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions?Version=$($VersionNumber)";

# at this point simply return the $VersionData variable for the raw info
$VersionData

# Want to improve your Health Check script, calculate the health of Support Status
if ($VersionData.ExtendedSupportEnd -le (Get-Date)) {$SupportStatus = 'Critical'}
elseif ($VersionData.MainstreamSupportEnd -le (Get-Date)) {$SupportStatus = 'Warning'}
else {$SupportStatus = 'Ok'}

# format the output data string
$OutputData = @"
Instance = $($Instance.Name)
Version = $($VersionData.Version)
Product = $($VersionData.Product)
Branch = $($VersionData.Branch)
Update = $($VersionData.Update)
MainstreamSupportEnd = $($VersionData.MainstreamSupportEnd)
ExtendedSupportEnd = $($VersionData.ExtendedSupportEnd)
SupportStatus = $($SupportStatus)
"@

# Return the hashtable
ConvertFrom-StringData -StringData $OutputData;

#2 - Get Missing updates (static version number)

# Set the version number to test
$VersionNumber = '11.00.2342.00' # SQL 2012

# Call the API to get the known missing updates (released since current version)
$VersionData = Invoke-RestMethod -Uri "http://sqlserverbuildsapi.azurewebsites.net:80/api/SQLVersions?Version=$($VersionNumber)&MissingUpdates=$true";

# return the output, it is already formated as an Array
$VersionData

# or as a table
$VersionData | Format-Table -Property Version,Branch,Update,@{Name="Released";Expression={Get-Date $_.Released -Format 'yyyy-MM-dd'}},Description -AutoSize

Other Examples
There are more examples in my GitHub repository - https://github.com/Matticusau/SQLServerBuildsAPI-Examples

Including:

• Simple static value examples like above
• Both Single and Multiple Instance examples with live version number data direct from a SQL Instance
• c# Windows Form client

Just want to see it in action, I have an MVC form available at https://sqlserverbuildsportal.azurewebsites.net

Where is the source code for the API

At this time I am not making the API opensource.

Want to partner with me

This is a personal project. If you have a similar project or a project that you would like to use this API in please reach out and lets improve the world together.

If you have any questions around this API please make sure you comment or reach out to me through any of my contact means.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

PowerShell: Finding commands (functions) with Risk Mitigation capabilities

Yesterday I was talking to a workshop about implementing Risk Mitigation capabilities into your own Advanced Functions in PowerShell. This got me thinking about how to identify the commands that have that capability enabled. More importantly the configuration of the ConfirmImpact setting.

Background:
If you don't know PowerShell uses a number of inputs when determining how the Risk Mitigation capabilities are applied.
For the WhatIf capability it is purely the presence of the -WhatIf switch parameter.
For the Confirm capability it is a combination of the ConfirmImpact value in the [CmdLetBinding()] attribute and the $ConfirmPreference variable value. Or the presence of -Confirm parameter

The following command we show all commands with the RiskMitigation capability.

Get-Command | Where-Object {$PsItem.Parameters.Keys.Count -gt 0 -and $PsItem.Parameters.ContainsKey('WhatIf')}

We can extend this further to retrieve the ConfirmImpact value however this is not exposed through a Parameter on the command object and can only be retrieved from the source definition. Unfortunately for CmdLets this is not exposed as they are compiled. This will work though for Functions so you can check your own commands etc.

Get-Command -CommandType Function | `
   Where-Object {$PsItem.Parameters.Keys.Count -gt 0 -and $PsItem.Parameters.ContainsKey('WhatIf')} | `
   Select-Object -Property Name,CommandType,@{Name="Impact";Expression={[Regex]::Match($PSItem.Definition, "(ConfirmImpact='(?<impact>.{1,})'{1})").Groups["Impact"].Value}}

This pipeline command uses the RegEx type accelerator and Match static member but similar could be achieved with the -Match operator.


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

PowerShell ISE add-on to toggle collapsible sections in all files

UPDATE 03/03/2016: It's amazing how quickly this went from a simple script to within an hour yesterday becoming a Module and now released on PowerShellGallery and GitHub. Check out the instructions here to install this.
---------------

One feature I am hoping to see in the PowerShell ISE is the state of collapsed sections to be maintained for "recent" files. It is just annoying when you have a very large file that you are regularly working on to have to collapse all the sections you had previously collapsed each time you open it up..... anyway you can vote on that idea here.

If you didn't know there is a keyboard shortcut CTRL+M which will toggle the collapsed sections for the current file. Handy little gem that one. We can also programmatically invoke this method with:

$psISE.CurrentFile.Editor.ToggleOutliningExpansion()

This got me thinking, I can easily write an function that itterates through each open Tab/File and collapses it. Even better I can wrap that up into an add-on for the ISE.

I've added a menu item and also the keyboard shortcut CTRL+SHIFT+M to implement this.

Save the following code into your Profile script and enjoy!

#requires -Version 4
<#
    Script:     ISEColapseAllFiles.ps1
    Author:     Matt Lavery
    Created:    02/03/2016
    Version:    0.0.1
    
    Change History
    Version    Who          When           What
    --------------------------------------------------------------------------------------------------
    0.0.1      MLavery      02/03/2016     Initial Coding
    
#>

<#  
    .SYNOPSIS
        Toggles the state of all expandable sections in all open files within the ISE

    .DESCRIPTION
        Toggles the state of all expandable sections in all open files within the ISE
        Works across PowerShell Tabs.
        Implements the keyboard shortcut CTRL+SHIFT+M and a menu item in the Add-ons menu.
#>
function Set-ISECollapseAllFiles
{
    [CmdletBinding()]
    Param()

    Foreach ($psISETab in $psISE.PowerShellTabs)
    {
        Write-Verbose "PS Tab: $($psISETab.DisplayName)";
        foreach ($psISEFile in $psISETab.Files)
        {
            Write-Verbose "PS File: $($psISEFile.DisplayName)";
            $psISEFile.Editor.ToggleOutliningExpansion()
        }
    }
}

# remove the existing menu item if it exists
if ($psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.DisplayName.Contains('Toggle Colapse All Files'))
{
    $psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Remove(($psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.GetEnumerator() | Where-Object DisplayName -EQ 'Toggle Colapse All Files'));
}
# add the add-on menu
$psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Add('Toggle Colapse All Files',{Set-ISECollapseAllFiles},"CTRL+SHIFT+M");

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Generating Certificates for Desired State Configuration lab/test environments

A colleague pointed out to me today that it has been a long time since I blogged, and you know what he is right as I have mostly been posting tweets about interesting news. Given I was taking some colleagues through setting up a Desired State Configuration lab environment and ran into an issue with the certificates we were trying to use, and to make it worse the issue is something I have faced in the past but forget due to time lag, this seems like a great topic to blog about.

Now a search in your favorite search engine would show that this is not an uncommon problem but there isn't many who answer it plain and simple.

Do not use New-SelfSignedCertificate to generate a certificate for testing DSC deployments (encrypting credentials) on Windows 2012 R2 or you will most likely receive the error:

The private key could not be acquired.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], Ci
   mException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost

Solution #1: Download and use the script/function from Script Center - https://gallery.technet.microsoft.com/scriptcenter/Self-signed-certificate-5920a7c6
 

Solution #2: Generate the certificate with MakeCert.exe from the Visual Studio Command Prompt.

For example, to make a certificate called DSCDemo.

makecert.exe -r -pe -n "CN=DSCDemo" -sky exchange -ss my -sr localMachine

Further info on makecert can be found at https://msdn.microsoft.com/library/bfsktky3%28v=vs.100%29.aspx

The background, I was trying to setup a DSCPull server with configuration for installing a SQL Server Instance using xSqlPs. This required a Credential to be stored in the config for the 'sa' account, which clearly needed to be encrypted as I didn't want to use plain text passwords in the DSC Configuration. We tried all sorts of methods for exporting the certificate from the DSCPull but eventually the clue was in the fact that even on the DSCPull server the certificate reported no Private Key data in the properties.

PS C:\Users\mattl> get-item Cert:\LocalMachine\my\{thumbprint} | fl *
...
Extensions               : {System.Security.Cryptography.Oid, System.Security.Cryptography.Oid,
                           System.Security.Cryptography.Oid, System.Security.Cryptography.Oid}
FriendlyName             : DSCDemo
IssuerName               : System.Security.Cryptography.X509Certificates.X500DistinguishedName
NotAfter                 : 2/25/2017 4:02:31 AM
NotBefore                : 2/25/2016 3:42:31 AM
HasPrivateKey            : True
PrivateKey               :
PublicKey                : System.Security.Cryptography.X509Certificates.PublicKey
...
Issuer                   : CN=DSCDemo
Subject                  : CN=DSCDemo

Again the clue here was the fact that the Private Key isn't even displayed on the server where it was generated, so we know there is nothing wrong with the export/import, but actually a problem with the way it was generated.

A couple of searches and I found the blog post I used last time I faced this issue, yep isn't that annoying. Turns out this is a problem with the New-SelfSignedCertificate cmdlet and when you generate the certificate with MakeCert.exe as per above the Private Key data is visible on the server it is generated on and also on the server it is imported on (as long as you export the private key data too).

Hope this helps a few others, or at least helps me remember next time I face this problem before I waste a few hours trying to figure it out again ;)
 

BTW in other news did you see the post that WMF 5.0 (aka PowerShell v5) RTM has been re-released. Happy days.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Announcing the release of my SQL Server DSC Resource Module mlSqlPs on PowerShellGallery and Github

I've been automating the installation of SQL Server components for 10+ years now so it's no surprise I am a fan of PowerShell and Desired State Configuration (DSC). Given my background in large, high volume, and diverse environments, the main objective I always think about is how the process can be easily replicated time and time again but also easily adapted to fit the needs of diverse configurations.

Unfortunately to date I have found the xSqlPs DSC module falls just short of these needs, but that was also the intent of the DSC Resource Kit Waves so that it would provide the "starting blocks" for organizations to customize and extend the resources. I had already started talking with the team about how to improve the SQL resources, but now that the DSC Resource Kit modules has been made open source on github this opens the door for the community to contribute into the standard set of resources. All changes I mention below are pending pull requests back into the main branch of the xSqlPs resource on Github.

The PowerShell Team is also working on their nuget gallery at PowerShellGallery.com, which is going to greatly assist with the new PowerShellGet feature in PowerShell 5.0. The gallery is still in preview however I am starting to contribute to it and this module will be available there (link below).



To provide these features to everyone straight away I have also launched my own version of the SQL DSC module called mlSqlPs. This will allow me to get changes out quickly, while also working to merge the best of my work back into the main branch.

Version 1.0.0.0 of mlSqlPs DSC Module provides the following resources and enhancements.

• xSqlServerInstall - Enhanced
  Installs SQL Enterprise on target machine.
  This resource has been enhanced to provide capabilities for aligning to SQL best practices (e.g. Data, Log, TempDb paths)
• xSqlHAService
  Enables SQL high availability (HA) service on a given SQL instance.
• xSqlHAEndpoint
  Configures the given instance of SQL high availability service to listen port 5022 with given name, and assigns users that are allowed to communicate through the SQL endpoint.
• xSqlHAGroupconfigures
  An SQL HA group. If the HA group does not exist it will create one with the given name on given SQL instance and add the HA group database(s) to local SQL instance.
• xWaitForSqlHAGroup
  Waits for an SQL HA group to be ready by checking the state of the HA group of a given name in a given interval till either the HA group is discoverable or the number of retries reached its maximum.
• xSqlAlias - New
  Configures Client Aliases in both native and wow6432node paths. Supports both tcp and named pipe protocols.

The module contains a help file in both HTML and MD format, as well as some Sample files.

You can get the module at the following locations:

PowerShell Gallery
https://www.powershellgallery.com/packages/mlSqlPs/

GitHub - Want to contribute let me know
https://github.com/matticusau/mlSqlPs


Are you running Windows PowerShell 5.0? You can also get the module with the new Install-Module cmdlet.

#Search for the module on the default gallery
Find-Module -Name mlSqlPs -Repository PSGallery;

#Install the module from the gallery
Install-Module -Name mlSqlPs -Repository PSGallery;

#Get the list of resources
Get-DscResource -Module mlSqlPs;

And there you go, create your configuration, push or pull the mof to the node and watch the magic.


Keep checking the GitHub repository and PowerShell Gallery for updates on this module.


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

PowerShell: Identifying Strongly or Weakly Typed Variables

PowerShell is a loosely typed language, which means we can run something like this and PowerShell will automatically select the data type for us.

$var = 123;
$var.GetType();

Results in: System.Int32

And if we go and change the value to a string, PowerShell just naturally changes the datatype as required for us.

$var = "string";
$var.GetType();

Results in: System.String 

Now if you are like me and grew up in a strict language, then you may prefer to Strongly Type your variables. There are actually very good reasons for doing this as it guarantees the data type you will be working with, particularly from user input (although there are input validation methods you can use in addition to this too).

To strongly type a variable we prefix the variable name (at creation) with the desired type.

[string]$var = "string";

Now this what I would consider good coding practice. So how do we determine if variables have been strongly typed. The following script will create an array to hold all known system variables, because we don't want to mess around with them. Then it will use Get-Variable and look at the Attributes property of a variable to determine if the type conversion was set. Now this method could also be used for some of the other attribute options (e.g. range validation, etc)

<#
This script can be used to check for Strongly and Weakly typed variables defined within the session
Things to note:
- Variables created in child scopes which have ended will not be visible
- Some System Variables may appear in the list
#>

#create an array to hold all the known system variables (variables from other modules are not included here)
$SystemVars = @('$'
    , '?'
    ,'^'
    ,'true'
    ,'false'
    ,'args'
    ,'Error'
    ,'ErrorView'
    ,'ExecutionContext'
    ,'FormatEnumerationLimit'
    ,'HOME'
    ,'Host'
    ,'input'
    ,'ConfirmPreference'
    ,'ConsoleFileName'
    ,'DebugPreference'
    ,'ErrorActionPreference'
    ,'MaximumAliasCount'
    ,'MaximumDriveCount'
    ,'MaximumErrorCount'
    ,'MaximumFunctionCount'
    ,'MaximumHistoryCount'
    ,'MaximumVariableCount'
    ,'MyInvocation'
    ,'NestedPromptLevel'
    ,'null'
    ,'OutputEncoding'
    ,'PID'
    ,'profile'
    ,'ProgressPreference'
    ,'PSBoundParameters'
    ,'PSCommandPath'
    ,'PSCulture'
    ,'PSDefaultParameterValues'
    ,'PSEmailServer'
    ,'PSHOME'
    ,'psISE'
    ,'PSScriptRoot'
    ,'PSSessionApplicationName'
    ,'PSSessionConfigurationName'
    ,'PSSessionOption'
    ,'PSUICulture'
    ,'psUnsupportedConsoleApplications'
    ,'PSVersionTable'
    ,'PWD'
    ,'ShellId'
    ,'StackTrace'
    ,'SystemVars'
    ,'VerbosePreference'
    ,'WarningPreference'
    ,'WhatIfPreference'
)

#view all variables and report if Weakly or Strongly typed
Get-Variable -Exclude $SystemVars | Select Name, @{Name='TypedState';Expression={
    If ($_.Attributes -match 'System.Management.Automation.ArgumentTypeConverterAttribute') {'Strong'} else {'Weak'}
}};

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Two new PowerShell v5 features worth waiting with bated breath or becoming an early adopter for

Recently there were a two new PowerShell v5 Preview features which IMHO are well worth waiting with bated breath for..... or definitely becoming a WMF 5.0 early adopter for :)

The first is the ISE extension for finding and installing modules. This feature takes advantage of the new PowerShellGet module. Best of all the PowerShellGet functionality helps our community overcome the module management problem, and this feature provides an easy to use GUI for discovering modules on community or company repositories.

ISE Module Browser - A new way to manage your PowerShell modules
http://blogs.msdn.com/b/powershell/archive/2015/02/23/ise-module-browser-a-new-way-to-manage-your-powershell-modules.aspx


The second is a static code analyser for PowerShell scripts and modules. Taking a plugin written by a colleague the PowerShell team have now included the functionality in the WMF 5.0 framework (Feb release). This analyses your script against known patterns and practices. Well worth running against your scripts even just to satisfy curiosity, I know I will be.

PowerShell Script Analyzer: Static Code analysis for Windows PowerShell scripts & modules
http://blogs.msdn.com/b/powershell/archive/2015/02/24/powershell-script-analyzer-static-code-analysis-for-windows-powershell-scripts-amp-modules.aspx

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Importing SQLPS module results in Warnings

Update (June 2015): After originally posting this back in Nov, and debugging the issue earlier in the year I finally got around to recording my findings and updating this blog post. Hopefully this post also provides some insight into methods for Debugging PowerShell when faced with Provider issues.

I've been meaning to blog about this for a while now. If you do any work with PowerShell, SQL Server and Azure then chances are you have noticed that when you import the SQL module it reports the errors:

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' failed with the following error: The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' failed with the following error: The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)
...
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with the following error: The RPC server is unavailable.
(Exception from HRESULT: 0x800706BA)
WARNING: The names of some imported commands from the module 'sqlps' include unapproved verbs that might make them less
 discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose
parameter. For a list of approved verbs, type Get-Verb.

The following Modules are affected by the issues described within this blog.

Module	Version	Notes
SQLPS	1.0	SQL 2014 release
SQLASCMDLETS	1.0	SQL 2014 release
Azure	0.8.8	Azure SDK June 2014 Release

Firstly a warning, do not modify any of the files mentioned in this blog as I will be exploring the code which runs when you import a module, but these are signed and any modifications may break the module functionality.

People generally notice this error when they run:

Import-Module SQLPS

What happens when you run this command is PowerShell loads up the Module Mainfest C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.PSD1


This references a number of other code bases.

Firstly a number of nested modules, most significantly both "Microsoft.SqlServer.Management.PSSnapins.dll" and "Microsoft.SqlServer.Management.PSProvider.dll" which load up the SQLPS provider and set the PSDrive path to the path of PS SQLSERVER:>

It also loads up the script file SqlPsPostScript.PS1 in the same path..... It is the code in this script where the warning is generated from (but this is not the cause).

This script has a step which is designed to import the SQLASCmdlets module and it is this step which is throwing the warnings. However, this isn't really to do with the loading of the SQL module but due to the Get-Module command which is executed.

#Load the SQLAS Module
$m = Get-Module -ListAvailable | where {$_.Name -eq "SQLASCmdlets"}
if($m -ne $null) { Import-Module $m -Global }

After a bit of troubleshooting I found that the problem even happens if you just run Get-Module -ListAvailable from within the SQLPS provider.

However if you run Get-Module -ListAvailable from another Provider (e.g. FileSystem) this does not return the error.

So the culprit is within the way the SQLPS Provider (via the PSDrive) implements standard cmdlets. You can see this even with Import-Module cmdlet when it specifically tries to work with the Azure module when the path is set to SQLSERVER:\.

Now typically this is where most would get stuck as the SqlPs and Azure modules are Binary Modules (aka it loads .dlls) so the general public cannot dig into the code.

However, here we can leverage PowerShell debugging to go deep into the code stack. So the actions that I took next were:

1) Use the -Debug switch parameter on the Import-Module

Import-Module -Name SqlPs -Debug

2) Use Trace-Command

Trace-Command -Expression {Import-Module SQLPS} -FilePath C:\ImportModuleTraceCommand.txt -Name * -Option All

Using the output captured by these commands we can actually trace the problem down further in the stack.

Firstly by looking at the Debug output we can see the following function calls just before the WMI errors.

---------------------------------------------------------------------------
DEBUG: ItemExists: SQLSERVER:\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
DEBUG: ItemExists: SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.S
qlDatabase.Types.ps1xml' failed with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA
)
---------------------------------------------------------------------------

This tells us that the module loading is calling the ItemExists function to test a path for a file it is told to load (and we already know that the path is coming from the definition of the Azure module).

Next we can look at the Trace-Command data.

If you compare the logical code variations between the two extracts you can clearly see that the first resolves the path to the FILESYSTEM provider but the second resolves the path to the SQLSERVER provider. If we look even more closely we see a function call output by "Path is DRIVE-QUALIFIED" which returns true on the first and false on the second, and because it returns false on the second it then sets the SQLPROVIDER to go checking for the paths off the base drive which in this case is SQLSERVER:, and eventually fails with "ERROR: Item does not exist".


Successful loading of types file:

PathResolution Information: 0 :                                                     Resolving MSH path "C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml" to MSH path
...
PathResolution Information: 0 :                                                         Path is DRIVE-QUALIFIED
LocationGlobber Information: 0 :  WriteLine                                                           result = True
LocationGlobber Information: 0 :  WriteLine                                                           Drive Name: C
SessionState Information: 0 :  WriteLine                                                           Drive found in scope 1
LocationGlobber Information: 0 :  WriteLine                                                           path = Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           result=Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           result=C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
SessionState Information: 0 :  WriteLine                                                           result = C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           basePath = C:\
...
PathResolution Information: 0 :                                                         DRIVE-RELATIVE path: Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
PathResolution Information: 0 :                                                         Drive: C
PathResolution Information: 0 :                                                         Provider: Microsoft.PowerShell.Core\FileSystem
...
PathResolution Information: 0 :                                                         RESOLVED PATH: C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml




Unsuccessful loading of types file:
PathResolution Information: 0 :                                                 Resolving MSH path ".\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml" to PROVIDER-INTERNAL path
...
PathResolution Information: 0 :                                                     Path is DRIVE-QUALIFIED
LocationGlobber Information: 0 :  WriteLine                                                       result = False
LocationGlobber Information: 0 :  WriteLine                                                       path = Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       result=Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       result=SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
SessionState Information: 0 :  WriteLine                                                       result = SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       basePath = SQLSERVER:\
...
PathResolution Information: 0 :                                                     DRIVE-RELATIVE path: Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
PathResolution Information: 0 :                                                     Drive: SQLSERVER
PathResolution Information: 0 :                                                     Provider: Microsoft.SqlServer.Management.PSProvider\SqlServer
...
PathResolution Information: 0 :                                                     RESOLVED PATH: SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
...
PathResolution Information: 0 :                                                             ERROR: Item does not exist: Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml




At this point I new there was something different happening in the implementation of a PathResolution process.

Taking in everything I had investigated I could conclude that the SQLPROVIDER was implementing the ItemExist function differently to the FileSystem provider. In particular the SQLPROVIDER was checking the given file path and trying to match it to the SQLSERVER: drive, and in this case because the next part of the path starts with "\Sql\....." this would cause it to think it is trying to get the path of a SQL Server in which it would then attempt to connect to the name (in our case the file name) as a Server Name to retrieve the instance details...... hence the WMI errors.

To the best of my knowledge this issue has been around since an update to the Azure module in early 2014, as they change the folder structure they were using. At the time of writing this post, both initially and subsequent updates, the problem still exists.

If you want to help get this resolved please vote on the MS Connect item: https://connect.microsoft.com/SQLServer/feedback/details/1420992/import-module-sqlps-may-take-longer-to-load-but-always-returns-warnings-when-the-azure-powershell-module-is-also-installed#



 Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

A look at how PowerShell handles Case Sensitivity

I was asked today but an attendee at one of my PowerShell workshops about why sorting data by Case did not appear as expected. So I thought I would dig a bit deeper into that.

As we know PowerShell is Case Insensitive by default..... well most things. The CmdLets Select-Object and Get-Unqiue actually seem to be Case Sensitive..... so it's almost everything by default.

When it comes to sorting data with Sort-Object and the -CaseSensitive switch parameter you may also be surprised that this does not order in the way you might think.

Lets take a fairly obvious array of strings to start with by just changing an individual character at a time:

$Strings = "aabbcc","aabbcC","aabbCc","aaBbcc","aAbbcc","Aabbcc","aabBcc";
$Strings | Sort-Object -CaseSensitive;

This sorts as you would expect like:

aabbcc
aabbcC
aabbCc
aabBcc
aaBbcc
aAbbcc
Aabbcc

However if we take a more complicated array of strings where we not only increase an individual character but we also have different order of characters or different characters all together within the strings like this:

$Strings = "abc", "acc", "adc", "aec", "afc", "agc", "ahc","aBc", "aCc", `
"aDc", "aEc", "aFc", "aGc", "aHc", "Abc", "Acc", "Adc", "Aec", "Afc", "Agc", "Ahc";
$Strings | Sort-Object -CaseSensitive;

Then the behavior changes to what you may not expect and results in:

abc
aBc
Abc
acc
aCc
Acc
adc
aDc
Adc
aec
aEc
Aec
afc
aFc
Afc
agc
aGc
Agc
ahc
aHc
Ahc

Notice the fact that not all the strings starting with lowercase 'a' are together?

So what is happening here, well it seems that first all strings are sorted regardless of case (i.e. all the strings with 'AEC' appear together). Then PowerShell starts evaluating each character within the string individually. So in the case of the 3 strings "aec","aEc", "Aec", the all lowercase version wins outright, but then the next string with the first lowercase 'a' wins and finally the string with uppercase 'A' is displayed..... but why do these all beat 'afc" well because even though they all start with 'a', 'ae' beats 'af', and most importantly regardless of case.

Lets look at a slightly more complicated example to highlight this more:

$Strings = "abc", "acc", "adc", "aec", "afc", "agc", "ahc","aBc", "aCc", `
"aDc", "aEc", "aFc", "aGc", "aHc", "Abc", "Acc", "Adc", "Aec", "Afc", "Agc", "Ahc", `
"aab","abb","Aab","Abb","ABb", "AbB";
$Strings | Sort-Object -CaseSensitive;

Which returns:

aab
Aab
abb
Abb
AbB
ABb
abc
aBc
Abc
acc
aCc
Acc
adc
aDc
Adc
aec
aEc
Aec
afc
aFc
Afc
agc
aGc
Agc
ahc
aHc
Ahc

Play around with this, try sorting file names in C:\Windows\System32 with both lower and upper cases, and you will see this behavior.

So why is this important, well if you are just using CmdLets then it is only going to be the sorting in case sensitive when you will really notice this behavior. However if you are using the Case Sensitive Operators, particularly the -CLT, -CLE, -CGT, -CGE then you are definitely going to need to care about this as the outcome may not be as you expect. For example.

#You would expect the string 'abc' to be lower than 'ABb' due to the lowercase first character 'a', but in fact this returns False.
"abc" -CLT "ABb"

Which returns False

In fact there is no difference between the above and this as it too (as expected) will return False, but this help to highlight how PowerShell is applying it's logic.

"abc" -LT "abb"

So in summary, Case Sensitivity Ordering only applies when strings have the exact same characters, because PowerShell (or more accurately .Net) is first ordering by the characters regardless of case, and then sorting by case within that group of common characters.


If you want to know what CmdLets support the CaseSensitive parameter then use these statements

#find the other cmdlets with CaseSensitive param
Get-Module -ListAvailable; Get-Command -ParameterName CaseSensitive;

# If you don't want to list the available modules first (to read them into the session) then use this
Get-Command | %{try {if ($_.Parameters.Count -gt 0 -and $_.Parameters.Keys.Contains("CaseSensitive") -eq $true){"Found in: $($_.Name)"}} catch {} finally {}}

Just to extend this investigation further I fired up Visual Studio, created a basic Windows Form with a List Box and the following code.

//create the array
String[] sStrings = new string[] {"abc", "acc", "adc", "aec", "afc", "agc", "ahc","aBc", "aCc", "aDc", "aEc", "aFc", "aGc", "aHc", "Abc", "Acc", "Adc", "Aec", "Afc", "Agc", "Ahc", "aab","abb","Aab","Abb","ABb", "AbB"};

//Sort the values
Array.Sort(sStrings);

//add the strings to the list box 
listBox1.Items.Clear();
listBox1.Items.AddRange(sStrings);

This outputs the values in the same order as above, as in:

....
Aab
abb
Abb
AbB
ABb
abc
aBc
Abc
acc
aCc
Acc
....

UPDATE: According to one of my .Net Programmer  mates (thanks Steve), this is all known as the Lexicographical order....



Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

The evolution of SQLPS CmdLets

The following table contains a list of all of the CmdLets and Functions released with SQLPS over the last few versions and highlights the evolution of the PowerShell support in SQL Server.

SQL 2008 R2	SQL 2012	SQL 2014
SQLSERVER:	SQLSERVER:	SQLSERVER:
Decode-SqlName	Add-SqlAvailabilityDatabase	Add-SqlAvailabilityDatabase
Encode-SqlName	Add-SqlAvailabilityGroupListenerStaticIp	Add-SqlAvailabilityGroupListenerStaticIp
Invoke-Sqlcmd	Backup-SqlDatabase	Add-SqlFirewallRule
	Convert-UrnToPath	Backup-SqlDatabase
	Decode-SqlName	Convert-UrnToPath
	Disable-SqlAlwaysOn	Decode-SqlName
	Enable-SqlAlwaysOn	Disable-SqlAlwaysOn
	Encode-SqlName	Enable-SqlAlwaysOn
	Get-SqlCredential	Encode-SqlName
	Invoke-PolicyEvaluation	Get-SqlCredential
	Invoke-Sqlcmd	Get-SqlDatabase
	Join-SqlAvailabilityGroup	Get-SqlInstance
	New-SqlAvailabilityGroup	Get-SqlSmartAdmin
	New-SqlAvailabilityGroupListener	Invoke-PolicyEvaluation
	New-SqlAvailabilityReplica	Invoke-Sqlcmd
	New-SqlCredential	Join-SqlAvailabilityGroup
	New-SqlHADREndpoint	New-SqlAvailabilityGroup
	Remove-SqlAvailabilityDatabase	New-SqlAvailabilityGroupListener
	Remove-SqlAvailabilityGroup	New-SqlAvailabilityReplica
	Remove-SqlAvailabilityReplica	New-SqlBackupEncryptionOption
	Remove-SqlCredential	New-SqlCredential
	Restore-SqlDatabase	New-SqlHADREndpoint
	Resume-SqlAvailabilityDatabase	Remove-SqlAvailabilityDatabase
	Set-SqlAvailabilityGroup	Remove-SqlAvailabilityGroup
	Set-SqlAvailabilityGroupListener	Remove-SqlAvailabilityReplica
	Set-SqlAvailabilityReplica	Remove-SqlCredential
	Set-SqlCredential	Remove-SqlFirewallRule
	Set-SqlHADREndpoint	Restore-SqlDatabase
	Suspend-SqlAvailabilityDatabase	Resume-SqlAvailabilityDatabase
	Switch-SqlAvailabilityGroup	Set-SqlAuthenticationMode
	Test-SqlAvailabilityGroup	Set-SqlAvailabilityGroup
	Test-SqlAvailabilityReplica	Set-SqlAvailabilityGroupListener
	Test-SqlDatabaseReplicaState	Set-SqlAvailabilityReplica
		Set-SqlCredential
		Set-SqlHADREndpoint
		Set-SqlNetworkConfiguration
		Set-SqlSmartAdmin
		Start-SqlInstance
		Stop-SqlInstance
		Suspend-SqlAvailabilityDatabase
		Switch-SqlAvailabilityGroup
		Test-SqlAvailabilityGroup
		Test-SqlAvailabilityReplica
		Test-SqlDatabaseReplicaState
		Test-SqlSmartAdmin

NOTE: This list was compiled from executing "Get-Command -Module SQLPS" (or similar) on the relevant versions.

In addition to this the SQLPS.exe Utility was introducted in SQL 2008 and is still available however it has been marked for depreciation in future releases. I have always recommended PowerShell peeps to use SQLPS Module which was introducted in SQL 2012 and still maintained today. The main reason that this has been my recommendation is that the SQLPS utility loads a PowerShell v2 shell and because I often use cmdlets other than just the SQL ones in my scripts I find it more efficient to develop for the latest release.