Category: Windows 10

PowerShell PSObjects – Think outside the box!

Posted on by admin

Working with PSObjects is essential to anyone using PowerShell. Especially, when there’s a need to extend the object to provide useful information.

Remember, the best way to get familiar with the PowerShell object(‘s)… (AKA PSOBject, .NET Object) is to use the ‘Get-Member’ Cmdlet.

Learn-By-Example

Creating custom properties on an existing PSObject:

1. Add a row property using Custom Expression: @{ label = ‘xxcolnamexx’; expression = { “xxxany code logic here..xxx” }; }

2. Add two new columns using the Add-Member command: “SystemName” and “UserName“ – using existing PowerShell session environment variables: $env:COMPUTERNAME, and $env:USERNAME.

Building our Sample PSObject

Let’s create a PSObject using the following *command:

$myWindowsfilesList = Get-ChildItem C:\Windows -Recurse -ErrorVariable flerr;
$myWindowsfilesList.count

*Note: Please ignore the errors and we’ll get back to it later. Errors are saved in the $flerr variable for later viewing.

## – List a small range of rows without the “Row” Property:
$myWindowsfilesList[0..5]
$myWindowsfilesList[1000..1005]

Now, with the help of PowerShell Custom Expressions, we’ll be providing a ‘row’ property to the existing output object. The new ‘Row’ property will be included in the “Select-Object” command and at the end of the process, will be saved as a new PSObject: “$myNewWindowsfilesList”.

Remember to initialize a global variable counter used for the row property.

To build the Custom Expression for the ‘Row’ property, see the line below:

@{ l = 'row'; e = { "[" + $global:row.tostring("D") + "]"; ++$global:row }; }

Try the new Code below:

## - 
## - To preserve the new 'row' column as part of the PSobject. by saving it as a new object.
## -
$myWindowsFilesList = dir c:\Windows -Recurse -ErrorVariable flerr -ErrorAction SilentlyContinue;
$row = 0; $myNewWindowsfilesList = $null;
$myNewWindowsfilesList = $myWindowsfilesList `
| Select-Object @{ l = 'row'; e = { "[" + $global:row.tostring("D") + "]"; ++$global:row }; }, `
name, mode, lastwritetime, length;

## - verify the 'Row' column has been added:
$myNewWindowsfilesList | Get-Member -MemberType Properties;


## - List a small range of rows:
$myNewWindowsfilesList[1633.. 1645] | Format-Table;

Add custom property using Add-Member

Add columns “SystemName” and “UserName“, to an existing new object using the “Add-Member” cmdlet within the ‘foreach{..}’ statement.

## - 
## - Using "Foreach" command to Add two new columns: "SystemName" and "Username"
## - to an existing PSObject.
## - 
[int32]$i = 0
foreach ($r in $myNewWindowsfilesList)
{ 
   ## - The following Add-member adds a property to an existing PSObject:
   $r | Add-Member -NotePropertyName "Systemname" -NotePropertyValue $env:COMPUTERNAME;
   $r | Add-Member -NotePropertyName "username" -NotePropertyValue $env:UserNAME;

   ## - Below code will show the progress bar:
   $percomplete = ($i / $myNewWindowsfilesList.count) * 100
   Write-Progress -Activity "Counting $i times" -PercentComplete $percomplete;
   ++$i
};

Displaying member properties and sample data with added columns:

## - verify the 'Row', 'computername', and 'username' columns has been added:
$myNewWindowsfilesList | Get-Member -MemberType Properties;

## - List a small range of rows with the new columns:
$myNewWindowsfilesList[1633.. 1645] | Format-Table;

Here’s where the PowerShell magic happened, as you would think that the Add-Member command would only affect only the row “$r” variable. The main PSObject “$myNewWindowsfilesList” has been updated. There’s no need to save the PSObject with a different name.

Recap

The combination of using ‘Custom Expressions’ with the Add-Member cmdlet gives great flexibility for building customized PSObjects. Go ahead and copy/paste this code, make your own properties, extend your PSObjects, and start thinking outside of the box.  Make it fun!

Bonus

In the script code you’ll find a few techniques you’ll want to pay attention to:

1. String “.NET formatting” for the ‘row’ column.

2. Use of the “-ErrorVariable” with the “-ErrorAction” parameters – prevents the errors or exceptions to be displayed in the console. For more information, use the command:
  Get-Help about_CommonParameters -ShowWindow;

3. Using the “Write-Progress” Cmdlet to display a progress bar while going thru adding the new columns. For more information, use the command:
Get-Help Write-Progress -ShowWindow;

4. “Get-Member” Cmdlet information, use the command:
Get-Help Get-Member -ShowWindow;

5. Displaying the saved errors from the $flerr variable;

## - 
## - BONUS - Check how many errors were saved, list a few of the messages
## -
$flerr.count
$flerr[50 .. 54].exception.message

PowerShell is a powerful scripting language. Enjoy and Keep Learning!
Own your future!

Handling SQL Server Connection strings with PowerShell Secret Management

Posted on by admin

Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.

Where do I start?

To install the Powershell Secret Management module, execute the following series of one-liners in a PowerShell prompt to install the latest version from the PowerShell Gallery:

Note: This module has finally reached GA (Generally Available) status.

## - install from the PowerShell Gallery both: SecretManagement, and SecretStore modules:
Install-Module Microsoft.PowerShell.SecretManagement, Microsoft.PowerShell.SecretStore

## - Register the vault with a given name:
Register-SecretVault -Name SecretStore -ModuleName Microsoft.PowerShell.SecretStore -DefaultVault

Now, we got the default “SecretStore” vault created. The vault password will ask once you start adding secrets.

The following cmdlets make it easy to manage your vault:

## - Module: Microsoft.PowerShell.SecretManagement
Get-Secret
Get-SecretInfo
Get-SecretVault
Register-SecretVault
Remove-Secret
Set-Secret
Test-SecretVault
Unregister-SecretVault

## - Microsoft.PowerShell.SecretStore
Get-SecretStoreConfiguration
Reset-SecretStore
Set-SecretStoreConfiguration
Set-SecretStorePassword
Unlock-SecretStore

Note: By-Design. There can only be one vault available.

Take your time to learn these commands.

Let the fun begin

Let’s cut down to the chase and see how this works. I’m going to proceed to create my secret SQL Server connection string values.

Keep in mind, secrets management supports five types of objects: byte[], String, SecureString, PSCredential, and Hashtable. By DEFAULT, the secret will be stored as a ‘SecureString‘ object.

Be creative! Why not store my connection string(s) as a hash table object containing my credential information in the following way:

## - Create hashtable object containing the SQL Connection String:
[hashtable]$MysqlCred01 = @{SqlName = "localhost,1445";Sqlusr = "sa"; SqlPwd = '$MyPwd01!';};

## - This is to veryfy the hashtable object was Properly created:
$MysqlCred01.GetType();
$MysqlCred01

Next after creating the hashtable object, is to save it in the vault with the following command “Set-Secret“:

## - Storing the secret in the vault:
Set-Secret -name MysqlCred01 -secret $MysqlCred01

Note: the first time you store a secret value to the vault, you’ll be prompted for a password.

As you save more secrets, use the following command “Get-SecretInfo” to list what you have in the vault:

## Displaying all stored secrets:
Get-SecretInfo

Now, to get your secret from the vault and use it in PowerShell:

## - Pulling the secret out of the vault into PowerShell variable as plain text:
$MysqlhashCred01 = Get-secret -name MysqlCred01 -asplaintext

## - Accessing hash table values:
$MysqlhashCred01.SqlName
$MysqlhashCred01.Sqlusr
$MysqlhashCred01.SqlPwd

You will notice that eventually, your access will time-out locking you out of the vault. Here’s you use the following command “Unlock-SecretStore” to temporarily unlock the vault:

## - Unlocking the vault to access your secrets providing the vault password:
Unlock-SecretStore -Password '$yourpwd!'

Now, the “Unlock-SecretStore” command is useful for script automation. when you want the script to quickly access the vault. You’ll need to do the following:

## - Unlocking the vault for automation:
Unlock-SecretStore -Password $(ConvertTo-SecureString -String '$yourpwd!' -AsPlainText);
Get-SecretInfo

This way SecretStore vault will not prompt for a password.

Implementing Secret in a GUI Application

Here’s an example of implementing secret in one of my SAPIEN PowerShell Studio GUI applications that check for SQL Server Database Index Fragmentation.

This is a multi-form Window application that where you can select a connection string stored in your SecretStore vault. then you can select the Database and click on the “Start-Job” button to list the status of Database index fragmentation. In this sample application, I can connect to both my local SQL Server and 2 of my Azure SQL Databases.

If you work with PowerShell, both SAPIEN’s Primalscript and PowerShell Studio is a good tool to have for any Administrators and DevOps. Try them out!

For more information

1. Secret Management Blog post.

2. Secret Management in Github. (Post any bugs and/or feedback here)

3. SecretStore in Github. (Post any bugs and/or feedback here)

Have a GREAT SQL PowerShell Day! This is the way!

PowerShell Working with SQL Logins Name

Posted on by admin

This blog post is following “Changing a SQL Server Login name with T-SQL“. Let’s take advantage of the .NET SMO framework assembly object model Namespaces with PowerShell to change a Windows account in a SQL Server Logins.

Remember to download the latest version of PowerShell.

There’s nothing wrong in using SSMS (SQL Server Management Studio) as our GUI application to manage our SQL Server engine. But soon, you will have the need to use PowerShell scripting for automating some daily tasks. And, Trust me! It will save you time.

Both Microsoft and the SQL Server Community provide you with some of the Awesome tools, such as the following PowerShell modules: SQLPS, SqlServer, Secretmanagement, and DBATools.

Let’s begin with creating  a list all SQL users on our SQL Server using the DBATools module  “Get-DBAuser” command:

Get-DBADBUser -SqlInstance 'localhost,1433'

As you can see, this command returns a lot of information you can export and dissect in many way.

Now, let’s take this a little further using SMO Object Model Namespaces.

Don’t be scare! in order to start using these SMO Classes. To start, all you need to have installed any of the following PowerShell Modules: SQLPS, SQLServer or DBATools, then execute the “import-Module” command:

## This will load SMO assemblies:
Import-Module SqlServer

Then all necessary SMO Assemblies are loaded and ready to be consumed during your PowerShell session. You can start building your own PowerShell one-liners or scripts/functions command to interact with the SQL Server engine.

Let’s cut to chase, and create a simple PowerShell function “Get-SqlLogins‘ to simply list all my SQL logins in my SQL Server:

## - function_Get-SqlLogins.ps1:

function Get-Sqllogins
{
param
(
[parameter(Mandatory = $true)]
[string]$sqlname,
[string]$uname,
[string]$upwd
)

## - Prepare connection to SQL Server:
$SQLSrvConn = `
new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($sqlname, $uname, $upwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - Get SQL SERVER list of database names:
$global:itm = 0
$SQLSrvObj.logins | Select-Object @{ l = 'itm'; e = { $global:itm; ++$global:itm }; }, name, logintype;

}; 
$sqlname = 'localhost,1433';
$uname = 'sa';
$upwd = '$SqlPwd01!';

Get-Sqllogins -sqlname $sqlname -uname $uname -upwd $upwd

## - End-of-File

Note: Save this code as function_Get-Sqllogins.ps1. 

You can edit this file to run one liner at the time and explore the $SQLSrObj PowerShell object.

Use the following GET-Member(alias gm)command to explore the object content:

## - exploring .NET Objects:
$SQLSrvObj | gm | Out-GridView

This is a good way to learn about your PowerShell objects.  You’ll be surprised by the ton of information you can find for documentation.

Now, try listing all SQL logins names by typing the following:

## - shorthand  to list all values in a object proprety:
$SQLSrvObj.logins.name

So, with a few lines of code, you can quickly get results.

Now, proceeding with looking for the Windows account I want to change the name in the SQL Login.

For this, I need to add line numbers to the PSObject result. This way I can Isolate the Login ID:

$global:cnt = 0
$SQLSrvObj.logins | Select-Object @{ l = ‘cnt’; e = { $global:cnt; ++$global:cnt }; }, name, logintype

For the finale: Changing the SQL Login Name. I’m going to manually do this using SMO PowerShell One-liner:
I found that element #5 is the SQL login I need to change:

## - verify before making the changeto the SQL Login object;
$SQLSrvObj.logins[5]

So far we’ve been working with SMO .NET Objects properties. Here’s where we use SMO .NET methods which affect the object (element#5) I have manually selected using “$SQLSrvObj.logins[5]“:

Last steps for updating the SQL Login name:

Note: Keeping in mind, the actual change starts at the Windows Account level. 

1. The *.Alter() method sets the object ready to be changed:

$SQLSrvObj.logins[5].alter()

2. followup by the *.rename(**string**) method which will affect the name object.

$SQLSrvObj.logins[5].rename('MXTLPT01\Dev01')

3. And, finally we use the *.refresh() to update all logins list with the name change.

$SQLSrvObj.logins.refresh()

AS you can see,  this open to some automation opportunities that can involve Windows Domain with SQL Server Accounts administration.

Don’t forget! always test your scripts. Practice makes a good scripter, and never be afraid of trying new stuff.

SQL PowerShell! It is the way!

Changing a SQL Server Login name with T-SQL

Posted on by admin

It’s been some time since my last blog post! Changing a SQL Server Login name uising  T-SQL.

Everyone who has tried at first, knows that it has its caveats. I couldn’t find a straight answer to the errors I was getting. You would think changing the sql login name should be simple. But, I was using the wrong approach.

First, we are working with “WindowsUser” login type. In SQLServer, I’m trying to fix a Windows User (local account)name  from “dev01” to “devUser01” using the following T-SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

but, in  doing so I got the following error message:

“Windows NT user or group ‘MXTLPT01\DevUser01’ not found. Check the name again.”

Of course, “MXTLPT01\DevUser01” Windows user account doesn’t exist on my system.

So, I created the user I try to run the T_SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\Devtest01]

Nope, it didn’t not work ending with the following message:

“Msg 15098, Level 16, State 1, Line 31
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.”

Basically, It translate to your Windows Account SID Object in SQL Server doesn’t match the object you’re trying to change.

In other word, you need to work with the Windows User originally created in WINDOWS (Locally or Domain), has changed first before executing the T-SQL statement “Alter Login… With Name=…”will be successful:

I ran the statement after the the change took place in my Windows system;

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

This time it works.

Wait! What about using PowerShell?

Check out the next blog post “PowerShell Working with SQL Logins Name”

Happy SQLAdmin!

Getting Ready for PowerShell 7.1 (GA)

Posted on by admin

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!

Available in the Microsoft Store – PowerShell Preview

Posted on by admin

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

Posted on by admin

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

Posted on by admin

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!

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

Posted on by admin

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!

Posted on by admin

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!