Microsoft SQL PowerShell Continnium – Max Trinidad

Yes! By now you all heard the news. SQL Server 2016 July update will include 25 new cmdlets for SQL PowerShell under the new module named SQLServer.

Now, you need to know that this only with the installation of the “Microsoft Sql Server Management Studio” (MSSMS) available as an option on the SQL Server 2016 installation disk. Its a separate download.

This link will take you to the following web page: https://msdn.microsoft.com/library/mt238290.aspx

Interesting to know that the SQLPS module hasn’t been renamed (just changed), as its still loaded on the “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS” folder.

But, don’t worry about having SQLPS conflicts with the SqlServer cmdlets. Aparently, SqlServer module will be loaded and somehow the SQLPS is disabled. You’ll find the SQLServer module at “C:\Program Files\WindowsPowerShell\Modules\SqlServer” folder.

Now, during my SQLServer 2016 new instance installation (No MSSMS) on a new image, I notice that SQLPS module get installed. So make sure to test SQL PowerShell if you are not going to install MSSMS.

You can verify all the modules you have installed on your machine by typing this simple oneliner:

get-module -ListAvailable | Select name,path | sort name

See sample results from my machine:

Also, you notice that I got various copy of SQL PowerShell modules including the new one. Its obvious that I got 3 version of SQL Server installed. But, in order to use only the newer module, I got to make sure my PSModule path have the proper folder location names loaded.

To check the values of your PSModulePath type: $env:PSModulePath

Now, if you ever have the need to do some editing to any of these Path values, then you need to open the “Environment Variables” under your “This PC” system properties using File Explorer. (See image below)

I can do the Happy Dance now!! The SQL Server Team has done a GREAT JOB!!!

**List of SQLServer module cmdlets (*NEW) – SQLServer MSSMS July Update**

Add-SqlAvailabilityDatabase
Add-SqlAvailabilityGroupListenerStaticIp
*Add-SqlAzureAuthenticationContext
*Add-SqlColumnEncryptionKeyValue
Add-SqlFirewallRule
Backup-SqlDatabase
*Complete-SqlColumnMasterKeyRotation
ConvertFrom-EncodedSqlName
ConvertTo-EncodedSqlName
Convert-UrnToPath
Decode-SqlName
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Encode-SqlName
*Get-SqlAgent
*Get-SqlAgentJob
*Get-SqlAgentJobHistory
*Get-SqlAgentJobSchedule
*Get-SqlAgentJobStep
*Get-SqlAgentSchedule
*Get-SqlColumnEncryptionKey
*Get-SqlColumnMasterKey
Get-SqlCredential
Get-SqlDatabase
*Get-SqlErrorLog
Get-SqlInstance
Get-SqlSmartAdmin
Invoke-PolicyEvaluation
Invoke-Sqlcmd
*Invoke-SqlColumnMasterKeyRotation
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityGroupListener
New-SqlAvailabilityReplica
*New-SqlAzureKeyVaultColumnMasterKeySettings
New-SqlBackupEncryptionOption
*New-SqlCertificateStoreColumnMasterKeySettings
*New-SqlCngColumnMasterKeySettings
*New-SqlColumnEncryptionKey
*New-SqlColumnEncryptionKeyEncryptedValue
*New-SqlColumnEncryptionSettings
*New-SqlColumnMasterKey
New-SqlCredential
*New-SqlCspColumnMasterKeySettings
New-SqlHADREndpoint
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityReplica
*Remove-SqlColumnEncryptionKey
*Remove-SqlColumnEncryptionKeyValue
*Remove-SqlColumnMasterKey
Remove-SqlCredential
Remove-SqlFirewallRule
Restore-SqlDatabase
Resume-SqlAvailabilityDatabase
Save-SqlMigrationReport
Set-SqlAuthenticationMode
Set-SqlAvailabilityGroup
Set-SqlAvailabilityGroupListener
Set-SqlAvailabilityReplica
*Set-SqlColumnEncryption
Set-SqlCredential
*Set-SqlErrorLog
Set-SqlHADREndpoint
Set-SqlNetworkConfiguration
Set-SqlSmartAdmin
SQLSERVER:
Start-SqlInstance
Stop-SqlInstance
Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
Test-SqlSmartAdmin