In the past, installing the SQL PowerShell cmdlets module required either installing SQL Server Management Studio (SSMS) or the SQL Server database engine. This resulted in a large footprint, especially when you only needed the cmdlets on a separate server for remote automation of SQL tasks. Additionally, updating SSMS to get the latest cmdlets was cumbersome. However, with the release of the SqlServer PowerShell module in the PowerShell Gallery, a new era of SQL PowerShell capabilities has begun.

The SqlServer module, the successor to the SQLPS module, can now be installed outside of the SQL Client Tools or SQL Server Engine. This opens up multiple opportunities for customers. One of the top use cases is deploying 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.

To install the SqlServer PowerShell module from the PowerShell Gallery, simply open PowerShell as Administrator and run the following command:

Install-Module SqlServer

Once the module is installed, you can view a full list of the available cmdlets by running the following command:

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

When the GridView control pops up, you can use the Filter bar to search for specific cmdlets you may be interested in.

Best Practices for Installing the SqlServer Module

When installing the SqlServer module directly from the PowerShell Gallery, it is recommended to open PowerShell as Administrator. This ensures a smooth installation process. However, if you don’t have administrator access, you can still install the module under your own account using the following command:

Install-Module SqlServer -Scope CurrentUser

Keep in mind that installing the module as a non-administrator user has some limitations. Other users on the machine won’t be able to see the module, including SQL Server Agent. If multiple people use the machine, they will have to follow the same install process, resulting in multiple copies of the SqlServer module at different version levels. Additionally, if an administrator later installs the module using the recommended method, your user account will not load the central copy of the module, but instead load the copy you downloaded yourself.

Deploying the SqlServer Module

Once you have saved the SqlServer module from the PowerShell Gallery, you can copy the module folder to your instance of SQL Server that doesn’t have an internet connection. It is recommended to place the module in the C:\Program Files\WindowsPowerShell\Modules\ directory to make it available to all users on the machine. Make sure to open Windows Explorer or Windows PowerShell as ‘Administrator’ to have the necessary rights to write the files into the directory.

To verify that PowerShell can see the module, use the following command:

Get-Module -ListAvailable -Name SQL*

Calling SqlServer Cmdlets

Starting with PowerShell 3.0, cmdlets can be called without explicitly importing their respective modules. This auto-import feature can cause issues if the machine already has one or more versions of the SQLPS module installed, as both modules share many of the same command names.

To avoid name conflicts, it is recommended to include an explicit call to import the SqlServer module at the beginning of your script. Alternatively, you can add the following line at the top of all your scripts:

#Requires -Modules SqlServer

Doing this will prevent the auto-loading of the SQLPS version of the cmdlets and ensure that the SqlServer version, with its new features and fixes, is used.

Improved Navigation

Thanks to feedback from the community, navigation of the SQLServer Provider has been improved. The SQLServer Provider now includes additional properties that Data Professionals find useful in their everyday work. These improvements can be seen at the Database, Table, XEvent, and Analysis Services Dimensions levels.

Now Includes SSAS Cmdlets

The SqlServer module includes the original 11 cmdlets from the SQLASCMDLETS module, as well as 2 new SSAS cmdlets. This consolidation allows for a more streamlined experience when working with SQL Server Analysis Services.

New Cmdlets

In addition to the improvements mentioned above, the SqlServer module also introduces 5 new cmdlets. Two of these cmdlets are for working with SSAS, and the other three are for working with Availability Groups. To see a list of all the commands available in this new version of the SqlServer module, run the following command:

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

We hope this guide has helped you understand how to download and deploy the SqlServer module from the PowerShell Gallery. With this module, you can now automate SQL tasks more efficiently and take advantage of the new features and improvements it offers.