PSCore6 – Creating a Hybrid Cross-platform SQLServer Script

There’s some discussion around scripting on using Windows PowerShell vs PowerShell Core. So, just pick one? No.
Just think about supporting a cross-platform environment. Use both!

Following my recent post on “PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform“, here’s a sample Hybrid-Script for cross-platform use.

Why not!

We all know the next generation (or evolution) of PowerShell is PowerShell Core. That’s it!
You are still using PowerShell, and Windows PowerShell is not going to be dropped nor removed any time soon.

So, why not start working towards, what I call, “Hybrid-scripting”? Powershell Core provides the necessary elements to help with cross-platform scripting.

In it’s basic code form, could look be something like this:

## - Logic Structure for executing either PowerShell Version:

## - Use Set-StrictMode for debug purpose:
Set-StrictMode -Version 5.1

If ($PSversionTable.PSEdition -eq "Desktop") {
"Windows PowerShell"
else {
## - Use Set-StrictMode for debug purpose:
Set-StrictMode -Version 6.1

if ($PSVersionTable.PSEdition -eq "Core") {
If ($IsWindows) {
If ($IsLinux) {
If ($isMacOS) {

Now, let’s apply this code to a practical sample.

Sample Hybrid-Script

In the following sample script, includes Help information, begin-process-end and with try-catch code structure.
At the same time, the script will output the exception to the screen console with the failed line.

Script function name: Get-DBASQLInformation

Function Get-DBASQLInformation {</pre>
This is a cross-platform function to Get SQL Server Information.

This is a cross-platform function to Get SQL Server Information using SQL Authentication.

Enter SQL Authentication UserID parameter.

Enter SQL Authentication Password parameter.

.PARAMETER SQLServerInstance
Enter SQLServerInstance name parameter.

PS> Get-DBASQLInformation -UserID 'sa' -Password '$SqlPwd01!' -SQLServerInstance 'mercury,1433'

Created with: SAPIEN Technologies, Inc., PowerShell Studio 2018 v5.5.152
Created on: 5/25/2018 8:27 AM
Created by: Maximo Trinidad
Organization: SAPIEN Technologies, Inc.
Filename: Function_Get-DBASQLInformation.ps1
[Parameter(Mandatory = $true,
Position = 0)]
[Parameter(Mandatory = $true,
Position = 1)]
[Parameter(Mandatory = $true,
Position = 2)]


## - Internal function:
function GetSqlInfo {
[parameter(Mandatory = $true, Position = 0)]
[parameter(Mandatory = $true, Position = 1)]
[parameter(Mandatory = $true, Position = 2)]
Try {
## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($S, $U, $P);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

catch {
## - Write Exception to Console:
Write-Host `
"Excepion found on line:`r`n$($error[0].InvocationInfo.line)"+ `
"`r`n$($Error[0].Exception)" `
-ForegroundColor Magenta;




## - Cross-platform logic:
If ($PSversionTable.PSEdition -eq "Desktop") {
Write-Host "Windows PowerShell"
GetSqlInfo -U $UserID -P $Password -S $SQLServerInstance;
else {

if ($PSVersionTable.PSEdition -eq "Core") {
If ($IsWindows) {
Write-Host "Windows PScore";
If ($IsLinux) {
Write-Host "Linux PSCore";
If ($isMacOS) {
Write-Host "MacOS PSCore";
## - execute on non-Windows:
GetSqlInfo -U $UserID -P $Password -S $SQLServerInstance;


## - EndBlock (Optional)

The heart of the code are stored in the “Begin” section as a Internal-Function GetSQLInfo(). The internal-function will be only executed if it the criteria for each of the different platforms. The Try-Catch is just to trap the error if the SMO connection failed, or to indicate the SMO .NET wasn’t loaded.

Go ahead! Create a script file, copy/paste this code, and load this function. Give it a try cross-platforms: Windows, Linux, and MacOS.

Remember, SQLServer module is a replacement for SQLPS module. I won’t recommend having both modules installed unless you use the namespace_module/cmdlet to identify which module is going to execute the cmdlet.

So make sure to always test your scripts.

What’s Next!

This function still need to worked on, but is functional enough to test-drive and see the results. So, it be modified to support Windows Authentication. Once you start scripting and building functions, you won’t stop thinking what else can be added.

Just keep working on it and learning from the PowerShell Community.

Go Bold! Learn PowerShell Core!

PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform

If you haven’t heard yet! The SQLServer Module is available for Windows, Linux, and MacOS. Yes!
And, with it,now you can even expand your scripting using .NET SQL Server Management Objects to manage your SQL Server Engine cross-platform.

How to get it!

It’s available in PowerShell Galley. Just run the following command to install the module in Windows PowerShell and PowerShell Core.
Yes, you read it! Install in PowerShell Core for Windows, Linux, and MacOS.

Install-Module -Name SQLServer -Force -Scope AllUsers

What’s in it?

Contains all of the SQL Server Management Objects .NET assemblies that will work in both Windows and non-Windows Systems. At the same time, it contains a total of 63 commands. This will support all existing SQL Server 2017(and older) on your network. Of course, there will be some limitations because there might be some features lacking in older features. But, for most use it will work.

It also includes the ability to provision the SQLSERVER: drive when you import the module.

Import-Module SQLServer


If you care for what SMO .NET Assemblies are installed, execute the following commands:

## - Get the SQLServer Module path:
(Get-MOdule -ListAvailable SQLServer).path

## - List of all SQLServer and Analysis Services DLL's:
dir 'C:\Program Files\PowerShell\Modules\SqlServer\21.0.17262\*.dll' `
| Where-Object{$_.basename -match 'SqlServer|Analysis'} `
| Format-Wide;

## Linux CentOS - Total of SQLServer and Analysis Services DLL's:
(Get-ChildItem '/usr/local/share/powershell/Modules/SqlServer/21.0.17262/*.dll' `
| Where-Object{ $_.basename -match 'SqlServer|Analysis' }).count

Using the SQLServer: Drive

Although, I’m not a fan of using SQLServer: drive. This will allow you to navigate thru the SQL Engine like a file system from the console prompt.

In order to use the drive, it need to be recreated with the proper credentials for cross-platform use.
Below steps will create additional SQLServer: drives to another SQLServer on the *network.

###==&gt;For Windows, Linux, MacOS
Import-Module SqlServer

## - New way for Streamlining Get-Credential:
$MyUserName = 'sa'; $MyPassword = ConvertTo-SecureString '$SqlPwd01!' -asplaintext -force;
$MyCred = [System.Management.Automation.PSCredential]::new($MyUserName, $MyPassword)

## - Creating SQLSERVER: connection to Windows SQLServer:
New-PSDrive -PSProvider sqlserver -root "SQLSERVER:\SQL\sapien01,1451\default" -name MyWindowsSQL -Credential $mycred

## - List all SQLSERVER: Drives:
Get-PSDrive *SQL* | Select-Object Name, Provider, Root;


Note: In this example, I’m using SQL Authentication.

Now, I can navigate thru my SQLServer objects like a filesystem.

## - Change directory to SQLSERVER: drive:
cd MyWindowsSQL:/databases/sampledb1/tables

Wait! Did you notice I’ve created a SQLServer Drive in MacOS? This is Awesome!
By the way, there’s no Docker involved in here. The fun doesn’t stop here!

What about using SMO scripting?

If anyone have been following me recently, everytime I’ve created the SMO script, I always have to load the assemblies before I can connect to the SQLServer.

## - When using the 'Microsoft.SqlServer.SqlManagementObjects'package installed from Nuget
## - Help find and save the location of the SMO dll's in a PowerShell variable: ver.14.17224
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netstandard2.0);

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll);

The above code is not needed if the SQLServer module had been previously imported.
This way you will code less.

Here’s a small SMO script example for getting SQLServer information using SQL Authentication:

## SMO with Import-Module SQLServer
Import-Module SQLServer

## - Prepare connection strings and connect to SQL Server
$SQLServerInstanceName = 'mercury,1433';
$SQLUserName = 'sa'; $sqlPwd = '$SqlPwd01!';

## - Prepare connection to SQL Server:
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

As you can see, there’s no reason why not try and experiment using PowerShell Core with SQL Server.  Next blog post I’ll be creating this script code in the hybrid-script function that can be executed cross-platform. I mean, on any PowerShell version too.

What’s in the future!

Now that PowerShell SQLServer Module is available cross-platform, I will see others Community SQL modules (DBATools, DBAReports) making their way to PowerShell Core. Of course, it will take some before it becomes available!

In the meantime, you can use SMO to build your own PowerShell SQL Scripts. Why not! Go and Expand your horizon!!

Be Bold! Learn PowerShell Core!

PSCore6 – Export-CSV cmdlet difference

Yes! There’s a change, and is a good one. While checking out some recent blog post on Export-Csv command, I took it a little further. And, I ended up finding that the parameters have change in this cmdlet.


Export-Csv – Windows Powershell parameter names:

((Get-help Export-Csv).Parameters)

Export-Csv – PowerShell Core parameter names:

((Get-help Export-Csv).Parameters)

Notice in PowerShell Core, there’s a new parameter: “-IncludeTypeInformation“.

Our prayer answered!

For a long time, in Windows PowerShell, we had to add the parameter “-NoTypeInformation“, so the “#TYPE …” line on the first row of the *CSV would not be included.

So, in Windows PowerShell executing the command without the “-NoTypeInformation” parameter, will produce the following result:

Now, using the same command in PowerShell Core without the “-NoTypeInformation” parameter, will produce a different result:

Moving forward with PowerShell, there’s no need to include the “-NoTypeInformation” parameter. Apparently, seems like the “-NoTypeInformation” parameter is the default when is not use in the cmdlet. So, no changes are needed to any previous developed scripts.

Clean Data

There is one thing we’ve learn thru time, is to always provide clean data. Knowing that a *CSV file is a text data set with columns and rows, always provide the columns name(s). This way the data structure looks nice and well defined.

Here’s just an example of how to manually create a one column list: (PowerShell Core)

## - First add the column name, then add the rows:
'ServerName' | Out-File -LiteralPath c:\Temp\MyServerList.csv;
@('Server1','Server2','Server3') | Out-File -LiteralPath c:\Temp\MyServerList.csv -Append;

## - Display the CSV file on Console:
cat c:\Temp\MyServerList.csv

## - Import the *CSV file into a PSObject:
$csvObj = Import-Csv -LiteralPath c:\Temp\MyServerList.csv;


It is better to use the Out-File cmdlet in this scenario. Also, let say you got multiple *CSV file with the same data structure, meaning the same column name(s). To merge into a single object the following one-liner can solve the problem:

## - Display on screen before creating the PSObject:
(Get-ChildItem C:\Temp\MyServer*.csv).FullName | import-csv

## - Build the PSObject:
$csvAllObj = (Get-ChildItem C:\Temp\MyServer*.csv).FullName | import-csv

Export PSObject to CSV

Now, the PSObject was created, go back and use the Export-Csv to create the *CSV file.

$csvAllObj | Export-csv -LiteralPath c:\Temp\MyPSObjectServerList.csv

cat c:\Temp\MyPSObjectServerList.csv

Finally, a *CSV file was properly created!

Bottom line

The Export-Csv is meant to be use with well-structured PSObjects and is not meant to be use from a text file.

Of course, there are many other ways to get things done with PowerShell and that’s the beauty of it.
There’s always a way!

Be Bold!! Learn PowerShell Core!!

SSMS Version 17.4 no more SQLPS Module

It was just a matter of time, as it was already mention in previous SSMS (SQL Server Management Studio) documentation that SQLPS module was going to be deprecated and replace with the new SQLServer module.

See SSMS download information at:

After SSMS Version 17.4 was release back in December, SQLPS module is no longer available. So, if you try to use the “Start PowerShell” from any of the database object, you’ll get the message “No SQL Server cmdlets found…” popup message.

New SQLServer PowerShell Module

But, no worries! Both the SSMS link and the popup message tell you where to get the new *SQLServer PowerShell module as is a separate installation from the PowerShell Gallery.

PowerShell Gallery SQLServer PowerShell Module, Get here:

One thing to point out, this module is only meant to be use on Windows PowerShell.

In other words, it will not work in PSCore6.

Housekeeping Needed

Now, there’s the chance previous SSMS installations have left the older SQLPS PowerShell Module in the system.

As is shown in the previous image, the variable $env:PSModulePath contains the path to the existing SQLPS module(s).

Either, remove the path manually using PowerShell, or thru the GUI System “Environment Variable“.

Or better yet, if you’re using SAPIEN Technologies, Inc. “PowerShell Studioproduct, the n use the Cache Editor feature to manage your existing PowerShell Modules. Check out the blog post and video about this feature at:

Video featuring PowerShell Studio Cache Editor

Option for PSCore

The only way to use PSCore6 to work with SQLServer cross-platform, is using the SMO (SQLServer Management Objects) for .NETCore, which is available in NuGet. For more information in how to install it, check my blog post at:

The only downside, you need to create the script from scratch. There’s plenty of documentation about SMO to overcome this hurdle. Most important, you are  sharpen your PowerShell scripting skills.

Don’t forget that before install any artifacts from PowerShell Gallery, NuGet, or Chocolatey the console shell need to be open “as an Administrator“.

Be Bold!! Learn PowerShell Core!!

PSCore6 – Version 6.0.1 is out of the oven!

Yes! Go and get while it still hot.  The Microsoft PowerShell Team is making it happen and they are not stopping.

Need to know more about the PSCore Roadmap, the check this link:

Keep mind, that it always take few hour for some of the links to be update.  So, the quick way to download the latest version is to go directly to the “Release“page:

Then, select the OS version for PSCore.

While we await for Ubuntu (or other) Repositories gets the latest update, you can use the debian installation format after downloading the file :

sudo dpkg -i *.deb 

Don’t forget to always update the help documentation using “Update-Help -force” with Administrator privileges.

Also, notice that previously installed PowerShellGalley Modules remains installed.

Be Bold!! Learn PowerShell Core!!


PSCore6 – Nuget Microsoft.SqlServer.SqlManagementObjects latest Package (v140.17218.0) Broken

This is the SMO (SqlServer Management Objects) package use to create PSCore6 scripts to connect and manage SQL Server on Windows, Linux, and Azure.

But today, I found out the latest version “140.17218.0″ is broken. I had to rolled back to use an older version “140.17199.0” to get it to work again.

You can find the information about this package in this link:

This NuGet SMO package version is built on .NETCore 2.0 for PSCore6, and will not install in Windows PowerShell.

Installing SMO Package

To *install the previous SMO package version “140.17199.0“, use the following command:

Install-Package Microsoft.SqlServer.SqlManagementObjects -RequiredVersion '140.17199.0'

*Note: Need to install as an Administrator.

If  the newer SMO version “140.17218.0” is installed then it will not connect. There are no errors, or failures displayed.  (See image)

This issue has been reported to NuGet SMO owners and hopefully will be resolved soon.

Testing SMO in PSCore6

Here’s the PSCore6 script for SMO testing. The script will work in both Windows and Linux.

## - Help find and save the location of the SMO dll's in a PowerShell variable:
$smopath = `
Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netcoreapp2.0)

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

## - Prepare connection and credential strings for SQL Server:
## - (Connection to Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'System01,1451'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Turn ON below for Linux:
## - (Connection to Linux SQL Server multi-instance sample)
# $SQLServerInstanceName = 'LinuxSystem02'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - End of Code

Most Important

In order for this to work, NuGet needs to be installed first. The following *code block will help to check if it’s already installed. And, if not, then it will install NuGet in PSCore6.

# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux)
if (-not (Get-PackageSource | Where-Object{$_.Name -eq 'Nuget'}))
Register-PackageSource -Name Nuget -ProviderName NuGet -Location
Write-Host "NuGet Already Exist! No Need to install."

*Note: Thanks to the SMO guys for providing this code block to get me started testing.

Also, if you already installed the buggy NuGet SMO version, remember to use the following command to uninstall the package:

uninstall-package Microsoft.SqlServer.SqlManagementObjects

I’m hoping this blog post will help in any way.

Be Bold!! Learn PowerShell Core!!

Installing PowerShell Core in Server Core

In the following Virtual machine scenario, I got one Server Core with Active Directory (Build 16299) and Windows 10 (Build 16299) joined to my new domain. Both Build 16299.
On my Windows client I create a shared folder named “SharedFiles”, where I copy over the latest MSI version of PowerShell Core “PowerShell-6.0.0-win-x64.msi”.

Then, on the Server Core I’m going to create a map drive to my Windows client shared folder to then run the MSI installation from Windows PowerShell Console.

Install Steps

Here are the steps on Server Core Command Prompt:

1. Open Windows PowerShell:

## - Start PowerShell:

2. Map network drive to Windows Client shared folder, then change drive:

## - Map network drive:
New-PSDrive -Name "m" -PSProvider "FileSystem" -Root "\\systemname\sharedfolder";

## - Change to drive to "m:" and verify the MSI file is there:

3. At the “m:” drive, execute the installation with the following command:

## - Start installing PowerShell Core:
msiexec /i .\PowerShell-6.0.0-win-x64.msi /q

4. As this is a silent installation, so give it a few minutes, then exit to the command prompt

## - type "exit" to get back to the command prompt

## - Change directory to PowerShell Core, and execute pwsh.exe:
cd "C:\Program Files\PowerShell\6.0.0"

Setting ExecutionPolicy and Update Help

It has become a routine that after first time openning Powershell, that we need to: 1) set the ExecutionPolicy, and update the help documentation:

So, setting the execution policy, pick the one you need. The most common ones are: “RemoteSigned”, “ByPass”, “AllSigned”, “Unrestricted”.

## - Example setting ExecutionPolicy as "RemoteSigned"
Set-ExecutionPolicy -ExecutionPolicy "RemoteSigned";

Then, its very important to update the help documentation. I had the tendency to always include to parameter “-Force“:

## - Updating Help documentation:
Update-Help -Force

And, you’re ready to work with PowerShell Core on Server Core.

Oh! How to uninstall PowerShell Core

Let go a little further. Here are the steps to successfully uninstall PowerShell Core on Server Core.
Open Windows PowerShell, make a list of all installed products using Get-WMIObject command:

## - Listing installed products
get-wmiobject Win32_Product `
Select IdentifyingNumber, Name, LocalPackage `
| Format-Table -AutoSize;

This will confirmed PowerShell Core is installed. Pay close attention to the “IdentifyingNumber” property which is really the product GUID.

Now, we can proceed to uninstall PowerShell Core. There are two way to execute a product MSI uninstall:

1. Providing the installation path with filename:

## - Executing Uninstall MSI silently and write to log file: (Using filename)
msiexec.exe /x "c:\filename.msi" /qn /lv "C:\msilogfile.log"

2. Using the IdentifyingNumber (Product GUID) listed when executing the Get-WMIObject:

## - Executing Uninstall MSI silently and write to log file: (Using Product GUID)
msiexec.exe /x '{11111111-1111-1111-1111-11111111111X}'dir /qn /lv "C:\msilogfile.log"

Image below doing MSI uninstall using GUID:

Both examples, include the use to write to a log file “C:\msilogfile.log“, along with the necessary switches to uninstall and execute silently. (Above switches are lowercase)

Additional Information

Check the following links on:

1. Installing MSI files with PowerShell:

2. Report or check any issues with PowerShell Core:

3. PowerShell Core Reach GA (Generally Available) status:

Be Bold!! Learn PowerShell Core!!

PSCore 6.0.0 – Upgrading/Uninstalling OpenSSH in Windows

This is all done with Chocolatey Package Manager for Windows. The following commands are important to know:

1. Information about the package:
choco info OpenSSH

2. Upgrade installed package:
choco upgrade OpenSSH

3. Remove installed package:
choco uninstall OpenSSH

4. Seach for a package:
choco search OpenSSH

Now, this is very important to know as these packages gets update without any notice and avoiding upgrades could impact the system.

As of the writing of this post, the latest version of Win32 OpenSSH is v0.0.24.0.

## - Chocolatey package information command:
choco info OpenSSH

Keep in mind, all these steps need to be executed with administrator privileges.

Upgrading Win32 OpenSSH

The upgrade process should be enough to get the package to the latest build. Now, I notice my latest upgrade step, I found files that shouldn’t be in the folder.

## - Chocolatey upgrade package command:
choco upgrade OpenSSH

So, take the time to verify and confirm everything is installed as expected. Don’t forget to document and/or save any file, such as: sshd_config.

Uninstalling/Installing Win32 OpenSSH

In the other hand, it doesn’t hurt to uninstall everything, check there are no files left in the folder, and that event the folder was removed. In other words, a clean installation will be perfect.

Before uninstalling, I would recommend a few extra steps, such as:

1. Stop SSH services: sshd and ssh-agent

## - Stopping SSH Services:
Stop-Service sshd
Stop-Service ssh-agent

2. Proceed to uninstall SSH services and change directory:

## - Execute uninstall ssh services script from the OpenSSH folder:
cd c:\

3. Before, doing the uninstall step, make sure there are no process using OpenSSH that may have open file(s).

## - Execute uninstall command:
choco uninstall OpenSSH

Sample of the uninstall error due to an open file:

In this case, just rerun the uninstall step. The goal is to have everything removed including folders.

Now, you’re ready to do a clean installation of Win32 OpenSSH.
Please refer to the following blog post which I have recently added few thing I missed last time:

For information about Chocolatey The package manager for Windows:

Be Bold!! Learn PowerShell Core!!