New Features and Best Practices of SQL Server PowerShell

By: Aaron Nelson | Updated: 2017-08-01 | Comments (1) | Related: > PowerShell

Problem

In the past, to install the SQL PowerShell cmdlets module you either had to install SQL Server Management Studio (SSMS) or install the SQL Server database engine. This situation causes a large footprint in the cases where you just wanted to have the cmdlets available on a separate server, so that SQL tasks could be automated and kicked off remotely. Furthermore, in the case of SSMS 16, you had to re-install the latest update of SSMS just to get the latest cmdlets. In this tip we cover some of the new things in PowerShell.

Solution

The SQL Server PowerShell module, SqlServer, the successor to the SQLPS module, is now available in Microsoft’s PowerShell Gallery. This marks the beginning of a new era of SQL PowerShell capabilities. While making the SqlServer PowerShell module installable outside of the SQL Client Tools or SQL Server Engine may seem like a small step, it opens up multiple doors of opportunity for our customers to do more.

Among the top use cases this now enables, is that customers can now deploy the SqlServer module to any supported version of SQL Server instances, including running SQL Server on Windows Server Core, for use in SQL Server Agent Job steps (by using a Job Step Type of cmdExec). We will explain the steps to accomplish this scenario in this post, but we want to make note that PowerShell 5.0 is now a prerequisite for deploying the SqlServer PowerShell module.

PowerShell 5.0 is the default PowerShell version in Windows 10 and Windows Server 2016. It’s also supported in previous versions of Windows, including Windows 7 and Windows Server 2008 R2. To install PowerShell 5.1 on previous versions of Windows, visit the Install and Configure WMF 5.1.

Download from the PowerShell Gallery, Install Where You Need

With the release of the SqlServer PowerShell module in the PowerShell Gallery you can now download these tools to any machine. Installing SQL Server Management Studio or SQL Management Objects is no longer necessary.

To install the SqlServer PowerShell module from the PowerShell Gallery simply open PowerShell (either PowerShell.exe or the PowerShell ISE) as Administrator and run the following command:

Install-Module SqlServer

Once the SqlServer module is installed on your machine you can run the following, to see a full list of the 95 PowerShell cmdlets that are now available in the module.

Get-Command -Module SqlServer -CommandType Cmdlet | Out-GridView

When the GridView control pops up you can use the Filter bar at the top to look for specific cmdlets you may be interested in, like “AvailabilityGroup”.

Exploring all SQL PowerShell cmdlets using Out-GridView

Best Practices for Installing the SqlServer Module

When installing the SqlServer module directly from the PowerShell Gallery it is best to open PowerShell as Administrator to perform the Install-Module process. There is an option to change the Scope to CurrentUser for those of users without Administrator rights to allow the module to install. While using -Scope CurrentUser to install a module for a Gallery is certainly a common practice when developing and testing PowerShell commands, it has the potential to lead to unexpected behavior in Production environments that we want to make you aware of first.

If you don’t have administrator access, there is still a way in install the SqlServer module but it comes with two important issues that we have found. First, no other users on the machine will be able to see the module, not even SQL Server Agent (unless it's running under your account). If multiple people use the machine, and they aren't administrators, they will have to follow the same install process, leading to multiple copies of the SqlServer on the machine, possibly at different version levels, depending on when they downloaded it. Second, and much more problematic, if an administrator does come around later and install the SqlServer module using the first method we mentioned (the better one) your user account will not load that central copy of the SqlServer module, it will instead load the copy that you had downloaded yourself. This is due to the default behavior of the PSModulePath.

If installing the SqlServer module as Administrator is not an option, you can use the following command to install it under your own account:

Install-Module SqlServer -Scope CurrentUser

Saving the SqlServer Module Using the 5.0+ NuGet Package

When saving the SqlServer Module you may be prompted to allow download a NuGet package. This is mandatory to complete the process.

You can save the module to any directory you choose, in this example, we will simply use the temp directory at the root of the C:\ drive.

Save-Module SqlServer -Path c:\temp\

Without PowerShellGet and PackageManagement

If you do not have access to the PowerShell Gallery modules (namely, PowerShellGet and PackageManagement), you can also download and save the SqlServer module manually by executing the following from PowerShell

Invoke-WebRequest -Uri powershellgallery.com/api/v2/package/sqlserver -Out c:\temp\sqlserver.zip

This will save a zip file to C:\temp\sqlserver.zip that you can extract and place in Documents\WindowsPowerShell\Modules\sqlserver or Program Files\WindowsPowerShell\Modules\sqlserver. This will allow SqlServer to autoload when one of its cmdlets is executed.

Deploying the SqlServer module

Once you have saved the SqlServer module from the PowerShell Gallery, you can now copy this new folder over to your instance of SQL Server that doesn’t have an internet connection.

When placing the module on a server, it will be best to place it in the C:\Program Files\WindowsPowerShell\Modules\ directory, since this will make it available to all users on the machine.

Note: When placing the module in Program Files, you may need to open Windows Explorer or Windows PowerShell, whichever you are using to perform the copy, as ‘Administrator’ in order to have rights to write the files into the directory.

Once you have placed the module in the directory, you can verify that PowerShell is able to see it using the following command:

Get-Module -ListAvailable -Name SQL*

$Showing the SqlServer module is now available - Description: The best location to store the SqlServer module is in the C:\Program Files\WindowsPowerShell\Modules\ directory.$

Calling SqlServer Cmdlets

Starting with PowerShell 3.0 cmdlets can be called without explicitly importing their respective modules. This auto-import is a very useful feature, however this has the potential to cause issues on a machine that already has one or more versions of the SQLPS module already installed.

This is because, as we mentioned earlier, SqlServer is the successor to SQLPS which means both share many of the same command names.

Handling Auto-Imports

Auto-importing depends on the order of the paths in $env:PSModulePath. The easiest way to avoid name conflicts is to simply include an explicit call to import the SqlServer module at the beginning of your script. Alternatively, if you have a PowerShell script that calls multiple child scripts, you could simply add #Requires -Modules SqlServer at the top of all of your scripts.

Import-Module SqlServer

#Requires -Modules SqlServer

Doing this will prevent a scenario where, due to the order of locations in the $env:PSModulePath, the SQLPS version of the Invoke-SqlCmd cmdlet could be auto-loaded, instead of the SqlServer version of the Invoke-SqlCmd cmdlet, which has several new features & fixes.

Improved Navigation

Thanks to feedback from the community, navigation of the SQLServer Provider has been improved to include by default additional properties that Data Professionals find useful in their everyday work. Examples of these improvements can be found in the Databases, Tables, XEvent, & [Analysis Services] Dimensions, just to name a few.

(Improved navigation at the Database level)

(improved navigation at the Table level)

Now Includes SSAS Cmdlets

The original 11 cmdlets from the SQLASCMDLETS module are now included in the SqlServer module, as well as 2 new SSAS cmdlets that have been added.

New Cmdlets

In addition to the above improvements, the first iteration of the SqlServer module available in the PowerShell Gallery (21.0.17099) also brought 5 new cmdlets. Two of the cmdlets are for working with SSAS, and the other three new cmdlets are for working with Availability Groups.

The five new cmdlets are included below.

Invoke-ProcessASDatabase
Invoke-ProcessTable

Grant-SqlAvailabilityGroupCreateAnyDatabase
Revoke-SqlAvailabilityGroupCreateAnyDatabase
Set-SqlAvailabilityReplicaRoleToSecondary

Again, to find a list of all of the commands available in this new version of the SqlServer module simply run the following command.

Get-Command -Module SqlServer -CommandType Cmdlet | Out-GridView

We hope this has helped you get up to speed with downloading & deploying the SqlServer module from the PowerShell Gallery.

Next Steps

Now that you have the SqlServer module loaded, check out how to use its cmdlets to INSERT data.
Read up on the behavior of PSModulePath.
Find out if any SQL Agent Jobs have failed in the last day, week, or month across all of your SQL Servers.
Visit the SqlServer module on the PowerShell Gallery for the latest release notes.

About the author

Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-08-01