I got the following error when running the DeployCommerceDatabase task during Experience Commerce 9 Update 2 installation:

SQL Server Provider for Windows PowerShell is not installed.

image001-2

In order to fix this issue, I tried the following command in PowerShell to install the SqlServer PowerShell module:

Install-Module -Name SqlServer

However, this did not work, and I got the same error. The next step was to look at the actual deploy script (SIF.Sitecore.Commerce.1.2.14\Modules\DeployCommerceDatabase\DeployCommerceDatabase.psm1). I could see that it was adding the SQL Server Provider as a Snapin based on the version found in:

HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps

After inspection the registry, I found that I did not have that entry:
image002-1

As a last resort, I uninstalled SQL Server Management 2017, installed 2016, and installed SqlServer PowerShell module. Unfortunately, this did not do the trick either, and I still couldnโ€™t see the registry entry after installing the SqlServer PowerShell module.

It turns out if you donโ€™t have SQL Server 2016 installed on your computer and you donโ€™t want to install it (e.g. connecting to a remote SQL server), you need to install Powershell Tools from the Microsoft SQL Server 2016 Feature Pack:

image003-1

After installing it, I can see that I now have the correct PowerShell module:
image004

Note: If you get the same error, and have SQL Server 2017 installed instead of SQL Server 2016, have a look at this post from Marc Bastiaansen.