Getting Ready for PowerShell 7.1 (GA)

This November, PowerShell 7.1 (GA) will become available, as well as PowerShell 7.2 Preview version. And it will come with some interesting features.
If you want more information on these upcoming releases, check out the following two videos:

* Taking your automation to the next level with PowerShell 7

* PowerShell Unplugged – Challenge Edition

Both videos will give you enough information about the history and what’s coming in PowerShell 7.1.
I guarantee that you won’t be disappointed.

But wait! There’s more. Don’t forget to check out any of the existing modules found in the PowerShell Gallery, such as:

* Microsoft.PowerShell.SecretManagement and Microsoft.PowerShell.SecretStore

* Microsoft.PowerShell.GraphicalTools and Microsoft.PowerShell.ConsoleGuiTools

* Microsoft.PowerShell.UnixCompleters

Remember, PowerShell has become the cross-platform automation tool of choice for Windows, Linux, and macOS.
It’s never too late to get on the PowerShell bandwagon!

Fixing the SQL Server Configuration SERVERNAME function value after renaming the computer name

You own a personal computer, and you want to learn about SQL Server. Then, you probably installed the SQL Server Developer Edition, which is free. But, there will be times when you may decide to change the computer name. And, because you have already installed SQL Server, you will find there are a few things in the SQL Server configuration that will need to be corrected.

The first sign of something wrong!

In my case, I renamed my computer name from ‘DESKTOP-K6AKFTA‘ to ‘MAXTLAPTOP1‘ with SQL Server previously installed. Then, while executing an SSIS SQL job, the job failed to execute giving me the following error:

Keep in mind, the User-id was added to the SQL Server security logins group with the previous computer name. Here is where you’ll find that something is not right.

Fixing the Computer Name for the User-Id

Use the SQL Server Object Explorer and navigate to the “Security\Logins” folder. Here you can find the local user with the wrong machine name.

To correct the User-Id, just right-click and select “Rename” to change the computer name.

image

Next, right-click in the User-Id, select “Properties“, and then go to “Securables“. Here you’ll find the SQL Server Instance Name is wrong.

image

This needs to be fixed!

Check the current SQL Server Configuration Server Name

To verify the SQL Server configuration system name use the “@@SERVERNAME” function by executing the following T-SQL script:

## - Execute the following T-SQL command:
--
-- Get SQL Server first time "Installed" Servername with Instance Name:
-- Note: Assuming this is run after the system has been renamed.
--
Select @@SERVERNAME CurrentSvrNameWithSqlInstanceName;

--Output:
CurrentSvrNameWithSqlInstanceName
DESKTOP-K6AKFTA\MSSQL2K19A

As stated in the Microsoft documentation, this function will display the value saved after the SQL Server installation. This value is will not be updated if the computer gets renamed.

By executing the above T-SQL script you’ll notice the return will not match the new computer name.

Fixing the SQL Server Configuration Server Name

To fix the SQL Server Configuration “@@SERVERNAME” function value you’ll need to use two of the Security Stored Procedures: “sp_dropserver” and “sp_addserver“. Below, I created a Dynamic SQL (T-SQL) Script solution to take care of this issue:

First, using the “sp_dropserver” to drop the server name:

--
-- Drop the wrong SQL Server Configuration @@Servername:
--
Declare @TSql1 nvarchar(max);
Declare @cfgSvrNameWithSqlInstName nvarchar(50);
Set @cfgSvrNameWithSqlInstName = (Select @@SERVERNAME);
Set @TSql1 = 'sp_dropserver ' + '''' + @cfgSvrNameWithSqlInstName + '''';

print @Tsql1;

exec sp_executesql @TSql1;
GO

Next, I use the “sp_addserver” to add back the correct server name:

--
-- Fix the SQL Server Configuration @@SERVENAME with SqlInstance Name:
--
Declare @TSql2 nvarchar(max);
Declare @fixSvrNameWithSqlInstName nvarchar(50);
Set @fixSvrNameWithSqlInstName = (Select convert(nvarchar, SERVERPROPERTY('servername')));
Set @TSql2 = 'sp_addserver ' + '''' + @fixSvrNameWithSqlInstName + ''''+ ', local';

print @TSql2;

exec sp_executesql @TSql2;
GO

Then, to verify the change works, use the following T-SQL statement:

Select @@SERVERNAME

Or, in SSMS, use the SQL Server Object Explorer, navigate to the “Security\Logins” folder, right-click in the User-Id, select “Properties“, and then go to “Securables“. Then, you’ll see the server name has been fixed.

Now, can this be automated? Yes, we can use PowerShell to automate this process.
That’s the next step.

Stay tuned for more!

Installing PowerShell 7 in Ubuntu 20.04

Everyone knows, that sometime soon, Microsoft will support the PowerShell installation in Ubuntu 20.04. But, in the meantime, there’s another way. And, this also applies to WSL (Windows Subsystem for Linux) Ubuntu 20.04.

It is the way!

First, you must follow the instructions for installing .NET Core for Ubuntu 20.04 from the Microsoft Documentation: https://docs.microsoft.com/en-us/dotnet/core/install/linux-ubuntu#2004-

Basically, the following commands will install both the .NET Core SDK and the Runtime components:

## Install the SDK

wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb

sudo apt-get update; \
sudo apt-get install -y apt-transport-https && \
sudo apt-get update && \
sudo apt-get install -y dotnet-sdk-3.1

## Install Runtime

sudo apt-get update; \
sudo apt-get install -y apt-transport-https && \
sudo apt-get update && \
sudo apt-get install -y aspnetcore-runtime-3.1

sudo apt-get install -y dotnet-runtime-3.1

So, after the .NET Core gets installed, then proceed to install the “.NET Global” tool:

## - Install .NET Interactive:
dotnet tool install --global Microsoft.dotnet-interactive

## Install PowerShell Global:
dotnet tool install --global PowerShell

Almost there! There’s one more step we need to do. If you try executing PowerShell, the system can’t find the program.

To resolve the issue of PowerShell not found, we need to add the path to the .NET Global Tools components so that PowerShell can start.

In my case, I open my VIM editor using “sudo” so I can modify the “~/.bashrc” file.

## Add .NET Tools path in Bashrc
$ sudo vim ~/.bashrc
## - Add path to .NET Tools:
export PATH=$PATH:~/.dotnet/tools
:wq

## - Refresh session after updating bashrc:
$ source ~/.bashrc

At this point, now you can start using PowerShell 7 in Ubuntu 20.04.

But, how to update PowerShell?

Simple! The following two commands will update .NET Tools when the update becomes available:

dotnet tool update -g --add-source "https://dotnet.myget.org/F/dotnet-try/api/v3/index.json" Microsoft.dotnet-interactive
dotnet tool update -g powershell

And, the following command will confirm the latest version of both the .NET Tools installed in the system:

dotnet tool list --global

Now, go ahead and have fun with PowerShell.

Happy PowerShelling!!

Available in the Microsoft Store – PowerShell Preview

Yes! If you haven’t noticed by now, PowerShell Preview is available for download from the Microsoft Store.
Just do a search for “PowerShell

It just takes less than a minute to install.

One thing you’ll miss from installing Powershell Preview using the MSI installation! That is, setting the additional options.

After the installation from the Microsoft Store, the PowerShell Preview application settings can be found under the Windows 10 Settings “Apps & features” section.

Then, click on the “Advanced options” to see additional information or make any changes to the application.

Now, the next time there’s an update to the PowerShell Preview, Windows 10 will take care of it automatically.

Happy PowerShelling!

Updating your .NET Tools components

Have you installed any of the .NET Tools? Such as “.NET Interactive” and “PowerShell Global“, then you’ll need to remember, to update these tools manually.

These tools give you the ability to use create Jupyter Notebook using Python Kernel but also with C#, F#, and PowerShell 7 kernels.

Check Current Version

First, need to list which .NET Tools are currently installed by using the following command:

dotnet tool list --global

In this sample, I opened a PowerShell 7 console and executed the command.

Manual Update

To update the tools, use the “dotnet tool …” command as follows:

1. To update the “Microsoft .NET Interactive” tool to the latest version:

dotnet tool update -g --add-source "https://dotnet.myget.org/F/dotnet-try/api/v3/index.json" Microsoft.dotnet-interactive

Completion message: (As of 07/30/2020, 16:20 PM)

Tool ‘microsoft.dotnet-interactive’ was successfully updated from version ‘1.0.136102’ to version ‘1.0.137901’.

2. To update PowerShell Global tool to the latest version:

dotnet tool update --global PowerShell

Completion message: (As of 07/30/2020, 16:20 PM)
Tool ‘powershell’ was successfully updated from version ‘7.0.2’ to version ‘7.0.3’.

*Note: If you have installed Anaconda, a manual update will be needed.

Keep in mind, these tools are not managed by Windows Update. So, you need to periodically run the update yourself.

This also applies to WSL 2 (Windows Subsystem for Linux).

More Information

WSL 2 – PowerShell Update-Help cmdlet is not working

Just recently I discovered, when executing the Update-Help cmdlet in WSL 2, that it doesn’t do anything.

Behavior: Run with no progress bar and no error messages at the end of the process. 

I have reported in the PowerShell Github repository and it will be addressed to the proper product group. This is on Windows 10 Version 2004, including Windows 10 Insider edition.

There are two workarounds to this issue:

Workaround #1

The workaround is shown below, thanks to Aditya Patwardhan (Microsoft PowerShell Developer) who provide the hint.

There are two Linux Bash environment variables that need to be updated: LANG and LC_ALL.

Check the current values using the echo command and, in my case, it shows:

## Current values:
(base) maxt@sapien01:~$ echo $LANG
C.UTF-8
(base) maxt@sapien01:~$ echo $LC_ALL
-EMPTY-
(base) maxt@sapien01:~$

Use the following “export” commands to change their values to be “en_US.UTF-8“:

(base) maxt@sapien01:~$
(base) maxt@sapien01:~$ export LC_ALL='en_US.UTF-8'
(base) maxt@sapien01:~$ export LANG='en_US.UTF-8'
(base) maxt@sapien01:~$
(base) maxt@sapien01:~$ echo $LC_ALL
en_US.UTF-8
(base) maxt@sapien01:~$ echo $LANG
en_US.UTF-8
(base) maxt@sapien01:~$

This will fix the issue temporarily during your WSL session, and the Update-Help will work properly.

For now, it may be needed to add these “export …” lines to the “~/.bashrc” file until the fix is available.

Workaround #2

Simply use the “Update-Help” specifying the UIculture:

Update-Help -uiculture en-us

That’s it!!

Keep PowerShelling!

Creating the PowerShell User Profile in Linux

In WSL, as on any Linux distribution, there’s no PowerShell User Profile file(“Microsoft.PowerShell_Profile.ps1“). So, it needs to be created manually.

Creating the profile folder

This profile is stored in the user home configuration folder “~/.config/powershell” folder.

But, the “powershell” folder doesn’t exist, it needs to be created in the configuration folder:

From the bash prompt, follow these steps:

1. Make sure you are in the user home folder:

pwd
cd /home/yourUserFolder

2. Verify the PowerShell folder doesn’t exist:

ls ~/.config

3. Change to the configuration folder:

cd ~/.config

3. Create the “powershell” folder, and assign permissions:

cd ~/.config
mkdir powershell
chmod 755
ll

Creating Microsoft.PowerShell_profile file

1. Using your Linux editor, create the Microsoft.PowerShell_Profile.ps1 file, and add code to the file: (Below using “vim” editor)

sudo vim /home/yourUserFolder/.config/powershell/Microsoft.PowerShell_profile.ps1
-> Write-Host "Welcome to PowerShell in Linux" -foreground 'Yellow';
-> Import-Module Microsoft.PowerShell.UnixCompleters
-> Import-UnixCompleters
-> Write-Host "UnixCompleters is loaded!" -foreground 'Yellow';

5. When done, save changes and exit “vim’ editor by typing:

:wq

Testing the PowerShell Profile

Open PowerShell and the “Welcome to PowerShell in Linux” with any other text will be displayed. At the same time, anything else in the profile will be executed.

Now, you can add more commands to the file when needed.

Keep on PowerShelling!

Getting Started – UnixCompleters Module for PowerShell in Linux

Yes! This module has been around for a while and it’s a great helper for completing bash commands in PowerShell.

Get it from the PowerShell Gallery: Microsoft.PowerShell.UnixCompleters

Installing the module

To install the UnixCompleted module manually execute the following command:

Install-Module -Name Microsoft.PowerShell.UnixCompleters

This module includes the following cmdlets:

Import-UnixCompleters
Remove-UnixCompleters
Set-UnixCompleter

Then, import the module by typing:

Import-Module Microsoft.PowerShell.UnixCompleters

Follow by running the cmdlet “Import-UnixCompleters” to load the module:

Import-UnixCompleters

Now, let’s use the ‘df‘ Linux command, which displays the amount of disk space available, to test this module:

df --

After typing the double-dash, press the tab key twice. The list of parameters will show at the bottom of the command.

Implementation

You can have this module to be loaded from your “PowerShell User Profile” which should be located in the user’s home configuration folder: /home/username/.config/powershell/Microsoft.PowerShell_profile.ps1. Remember! The “PowerShell User Profile” needs to be created manually.

Keep PowerShelling!!

Streamlining SQL Server Management Objects (SMO) In PowerShell 7 (Revised)

It’s been over two years since I touch this topic, so here’s an updated post about using SQL Server Management Object (SMO) on the latest PowerShell Version 7.

Here’s 411 on what’s out there!

For the most part, nowadays you can use SMO to  connect:

1. Windows to Linux.
2. Linux to Windows.
3. Windows to Linux Containers.
4. Linux to Linux Containers.
5. Windows to Windows Containers.
6. WSL to Linux Containers or Windows.

And, of course, will include cloud technologies.

Now, we have to extend our skills thanks to Docker Container.

*Note: Any connection issues connecting from Linux to Windows, can be solved by creating the inbound rule for Linux in Windows Firewall.

Ways to use SMO

There are two ways you could use SMO in PowerShell 7 (cross-platform):

1. Installing the SMO NuGet packages, two packages are requiered:
a. Microsoft.SqlServer.SqlManagementObjects Version 150.18208.0 (as of 03/23/2020)
b. Microsoft.Data.SqlClient Version 1.1.1 (recommended)

2. Installing the PowerShell Module: SqlServer Version 21.1.18221 (as of 03/23/2020)

Keep in mind, once the packages and/or modules are installed, you need to update them manually.

Working with SMO NuGet Packages

To install the Microsoft.SqlServer.SqlManagementObjects package. You first need to verify that Nuget Package Management is registered in PowerShell 7. Execute the following code will do the task of registration:

function Verify-NugetRegistered
{
[CmdletBinding()]
Param ()
# Microsoft provided code: Test Auto sAVCE
# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux/Windows)
if (-not (Get-PackageSource | Where-Object{ $_.Name -eq 'Nuget' }))
{
Register-PackageSource -Name Nuget -ProviderName NuGet -Location https://www.nuget.org/api/v2
}
else
{
Write-Host "NuGet Already Exist! No Need to install." -ForegroundColor Yellow;
};
}; Verify-NugetRegistered;

Now, here’s the tricky part. There’s a known issue when executing the Install-Package cmdlet which will fail to install the package.

The workaround is to download the Nuget.exe CLI and place the executable in the following folder: “C:\Program Files\PackageManagement\NuGet\Packages.”

This is the PowerShell default path for storing Packages, and it may not exist in the beginning. So you may need to manually create the folders.

To install the SMO packages needed, execute the following command in PowerShell 7 prompt as an Admin:

cd 'C:\Program Files\PackageManagement\NuGet\Packages\'
./nuget install Microsoft.SqlServer.SqlManagementObjects -version 150.18208.0
Pause
./nuget install Microsoft.Data.SqlClient -version 1.1.1
Pause

Notice, I included the versions of the packages as of 3/23/2020. These SMO packages will support SQL Server 2019 or older, but keeping in mind the older the SQL Server version the latest features will not apply.

Also, these packages doesn’t contain any PowerShell cmdlets, they are meant for developing solution from scratch. For example, below I’m creating an SMO script to connect to a SQL Server providing my SQL authentication, query to get the SQL Server engine version, and manipulate the results from the script.

## - PowerShell 7 loading .NET Core netstandard 2.0 library SMO dll's:
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netstandard2.0);

Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.SmoExtended.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Management.Sdk.Sfc.dll);

## - Prepare login credentials:
$SQLServerInstanceName = 'sapien01,1449';
$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);

## - Sample T-SQL Queries:
$SqlQuery = 'Select @@Version as FullVersion';

## - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);
$GetVersion = $result.tables.Rows;
$GetVersion.FullVersion.Split(' - ')[0];

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, `
@{ label = 'FullVersion'; Expression = { $GetVersion.FullVersion.Split(' - ')[0]; } }, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

The best thing! This Package is supported cross-platform so you can execute the script on any OS.

The beauty of coding with SMO is that everything is documented. Just check the Microsoft Documentation “SQL Server Management Objects (SMO) Programming Guide“.

Working with SqlServer Module

Now, using the SQL Server Module in PowerShell 7 is makes it a bit simple to install. And, it’s supported cross-platform.

Just execute the following command as an Admin:

Install-Module -Name SqlServer -AllowClobber

The latest version contains a total of 66 commands you can use to manage your SQL Server engine.

Now, besides having all of these commands available, in the future, you may have the need to create custom functions.

Here’s the variation of the previous SMO script sample:

## - Import the SqlServer module which it loads all needed SMO assemblies:
Import-Module SqlServer

## - Prepare login credentials:
$SQLServerInstanceName = 'sapien01,1449';
$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);

## - Sample T-SQL Queries:
$SqlQuery = 'Select @@Version as FullVersion';

## - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);
$GetVersion = $result.tables.Rows;
$GetVersion.FullVersion.Split(' - ')[0];

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, `
@{ label = 'FullVersion'; Expression = { $GetVersion.FullVersion.Split(' - ')[0]; } }, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

The differences is quite simple. All SMO assemblies are previously loaded when you import the SqlServer module. So, you don’t have to worry about including the assemblies in the code. Make sure to check all of the commands available that can help you manage the SQL Server.

Additional Tools Available

Now, don’t forget to check other SQL Server community tools that are available, such as:
1. DBATools – SQL SMO PowerShell.
2. MSSql-Scripter – Python-based tool.
3. Mssql-cli – Python-based tool.

And, don’t forget to check out .NET Interactive which brings Jupyter Notebook with PowerShell kernel.

If you want to try .NET Notebook, I suggest to first install Anaconda (Python 3.7) which makes it simple to use in Windows.

If you want to experiment with .NET Notebook without installing anything in your system, then try MyBinder. This is a web-based .NET Notebook that’s run from a container.

Unfortunately, in this scenario, only the PowerShell 7 core modules are available. But at least you will be able to learn the essentials of .NET Notebook.

Go ahead and start using this Amazing technology!

PowerShell 7 GA is Here!

Finally is here, PowerShell 7 GA (Generally Available) is available for download for Windows, Linux, and macOS. Go and get it!

Installation

I suggest to manually uninstall all previous PowerShell versions and remove all existing folders that will be left behind under the “C:\Program Files\PowerShell” folder. This will guarantee a clean installation of PowerShell 7 GA.

This version will replace any previous GA version of PowerShell. In other words, if you already had PowerShell v6.2.4 installed, it will be replaced with PowerShell v7.0. This is by-designed!

You can find more information about PowerShell 7 GA in the following links:

Update your PowerShell Notebook

Also, check out .NET Interactive/PowerShell Notebook, as it has been updated to support the PowerShell 7 Kernel.

If you have previously installed .NET Interactive, to get the latest PowerShell Kernel, run the following command:

dotnet tool update -g --add-source "https://dotnet.myget.org/F/dotnet-try/api/v3/index.json" Microsoft.dotnet-interactive

For more information here, .NET Interactive/PowerShell Notebook.

Wait, there’s more!

Things are getting better! Check out the preview of the ConsoleGuiTools module for PowerShell 7 but, for now, only available for Linux and macOS.

It’s never too late to learn PowerShell!