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.

VS Code running PowerShell – Debug 1/3

I recently went back to try using VS Code. Now that I’m fully invested with PowerShell Open Source in Ubuntu Linux 16.04.1 Desktop.  During trails and errors I found at least three ways to execute PowerShell:

1. VS Code – PowerShell Extension and Debug feature.
2. VS Code – Code Runner Extensions for PowerShell.
3. VS Code – Terminal session.

VS Code Extensions

First, we need to add a couple of extensions:
1. PowerShell
2. Code Runner

In below image, we are installing Python Extensions.  In Linux, having Python installed will be of great benefit.

powershellandpythonextensions

These are a most-to-have extensions to work with PowerShell. We are going to use the Debug feature.

Using VS Code Debug

First, we are going to use VS Code debug option to run PowerShell Out-Of-The-Box. This way we can be use debug to execute and step thru the PowerShell script code.

Open the folder were the scripts are going to be stored. The first time using the Debug it will ask to create the “launch.json” which is needed in order to execute and debug the script.  Accept the default values as you can change them later if needed.

launchjson

By the default, in Windows OS, it will execute Windows PowerShell ver 5.x.  In Linux, it will run the PowerShell Open Source. Keep in mind, I’m on a Linux system.

Next, is to create a new “settings.json” file, go to VS Code menu click on “File | Preferences | User Settings“.  In order to execute PowerShell Open Source, which is in a different folder, we need to create a “settings.json” file with the following code:

settingsjson_01

So, you’ll ended up with two *.json files in your script folder.  Then, you can customized these file to work with your script code when it gets debug.

setlaunchjson_04

Bonus Note: On a Windows System, if you want to customize VS Code to use PowerShell v6.0.0-alpha.x, just add the following line in the “settings.json” file:

changepowershelexepath

Next blog post, I’m going to cover “VS Code – Code Runner extension