PowerShell Core – How to install SQLServer Module (Preview) in WSL – Ubuntu 18.04

You all have been following Aaron Nelson blog post on Invoke-Sqlcmd availability Cross-Platform in the SqlServer module then you all are probably have proceeded to download the PowerShell SqlServer.

At the same time, on March 5th,  PowerShell Core 6.2.0-rc1 (Release Candidate) was made available for download.
Go and get it!

The thing is, in order to use the Invoke-SqlCmd cmdlet, you need to use PowerShell Preview version 6.2.0-rc1 (or greater).

Now, SqlServer Module can be easily installed in all platforms but I found out that it won’t install in Windows 10 WSL Ubuntu 18.04.

So, What the issue with Windows 10 WSL – Ubuntu 18.04?

Normally, when working with modules in PowerShell Core, I always use the following Cmdlets: Uninstall-Module to remove the module and then Install-Module with the “-AllowPrerelease” parameter. This will work flawlessly, but I found out that it won’t installed it in WSL – Ubuntu 18.04.

I don’t know why but it was installing the non-preview version SqlServer module 21.1.18080.   So, the following PowerShell Core command line will force the installation of SqlServer module version 21.1.18095-Preview.

Install-Module sqlserver -RequiredVersion 21.1.18095-preview -AllowPrerelease -Force

Now, we can start writing PowerShell Core SQL Server scripts in our Windows Subsystem for Linux – Ubuntu 18.04.

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.