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.

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.

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 inPowerShell 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
Get-PSDrive

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

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!!