SQL PowerShell: July 2016 update
This post was authored by Ayo Olubeko, Program Manager, Data Developer Group.
The July update for SSMS includes the first substantial improvement in SQL PowerShell in many years. We owe a lot of thanks for this effort to the great collaboration with our community. We have several new CMDLETs to share with you, but firstly, there is a very important change we had to make to be able to ship monthly updates to the SQL PowerShell component.
Historically, SQL Server’s PowerShell components are included in both the SSMS (tools) installer as well as with the SQL Server engine install. In order to be able to ship SQL PowerShell update, we had to change the identity of the SQL PowerShell module as well as the wrapper known as SQLPS.exe. This change has an impact to scripts doing Import-Module.
This month we introduce CMDLETs for the following areas:
- Always Encrypted
- SQL Agent
- SQL Error Logs
Additionally, we have made some nice improvements to Invoke-SqlCmd and the SQL provider.
New SQL PowerShell module
As alluded to above, in order to ship monthly updates, we have created a new SQL PowerShell module as well as have introduced a new wrapper EXE that SSMS uses to instantiate the SQL PowerShell environment. The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required. Additionally, if you happen to have a script that is using a 3rd-party or community version of a module named SqlServer, you should add use of the Prefix parameter to avoid name collisions.
The motivation for these changes is that the tooling components are being moved to be “application local” and not share any components with the SQL Server engine. This is an important step to enable monthly tooling updates while not negatively impacting the components setup and updated by the SQL Server setup program.
SSMS has been updated to integrate with SQLTOOLSPS.exe rather than SQLPS.exe. Hence, if you launch PowerShell from within SSMS, it will launch PowerShell and configure the session with the new SQL PowerShell module. It is advised to avoid using these EXE wrappers; they exist for legacy reasons within SSMS and are likely to be removed in a future monthly update.
The new version of SQL Server PowerShell included with SSMS does not update the version of PowerShell used by SQL Server. This means that scripts executed by SQL Agent will not be able to use the new CMDLETs. Updates to SQLPS (the version used by SQL Agent) will be done through the traditional SQL Server update mechanisms; more specifically, major changes will be done as part of the next major version of SQL Server as it becomes available.
New CMDLETs
In the July SSMS update, you will find several new CMDLETs. Once again, we owe thanks to our SQL PowerShell community leaders for helping us prioritize these investments. The CMDLETs all provide help within PowerShell for detailed information. As with the majority of the features in SSMS, the SQL PowerShell CMDLETs work against all supported versions of SQL Server. In some cases, such as Always Encrypted, the CMDLETs obviously only work on versions of SQL Server that support that specific feature set.
| CMDLET | Description |
| Add-SqlAzureAuthenticationContext | Performs authentication to Azure and acquires an authentication token. |
| Add-SqlColumnEncryptionKeyValue | Adds a new encrypted value for an existing column encryption key object in the database. |
| Complete-SqlColumnMasterKeyRotation | Completes the rotation of a column master key. |
| Get-SqlColumnEncryptionKey | Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name. |
| Get-SqlColumnMasterKey | Returns the column master key objects defined in the database, or returns one column master key object with the specified name. |
| Invoke-SqlColumnMasterKeyRotation | Initiates the rotation of a column master key. |
| New-SqlAzureKeyVaultColumnMasterKeySettings | Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault. |
| New-SqlCertificateStoreColumnMasterKeySettings | Creates a SqlColumnMasterKeySettings object referencing the specified certificate. |
| New-SqlCngColumnMasterKeySettings | Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API. |
| New-SqlColumnEncryptionKey | Crates a new column encryption key object in the database. |
| New-SqlColumnEncryptionKeyEncryptedValue | Produces an encrypted value of a column encryption key. |
| New-SqlColumnEncryptionSettings | Creates a new SqlColumnEncryptionSettings object that encapsulates information about a single column’s encryption, including CEK and encryption type. |
| New-SqlColumnMasterKey | Creates a new column master key object in the database. |
| New-SqlCspColumnMasterKeySettings | Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI). |
| Remove-SqlColumnEncryptionKey | Removes the column encryption key object from the database. |
| Remove-SqlColumnEncryptionKeyValue | Removes an encrypted value from an existing column encryption key object in the database. |
| Remove-SqlColumnMasterKey | Removes the column master key object from the database. |
| Set-SqlColumnEncryption | Encrypts, decrypts or re-encrypts specified columns in the database. |
| Get-SqlAgent | Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server. |
| Get-SqlAgentJob | Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent. |
| Get-SqlAgentJobHistory | Returns the JobHistory present in the target instance of SQL Agent. |
| Get-SqlAgentJobSchedule | Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job. |
| Get-SqlAgentJobStep | Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job. |
| Get-SqlAgentSchedule | Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent. |
| Get-SqlErrorLog | Retrieves the SQL Server Logs. |
| Set-SqlErrorLog | Sets or resets the maximum number of error log files before they are recycled. |
Invoke-SqlCmd improvements
Invoke-SqlCmd now supports an OutputAs parameter (or its alias -As). This parameter allows you to specify DataRows, DataTables or DataSet as the object type to return. These types map to the .Net types you find in System.Data. DataRows is the default, and corresponds to the old behavior.
Additionally, we added the ConnectionString parameter which allows the script author complete control over the connection context. This unlocks new capabilities such as connecting to SQL Azure using Azure Active Directory authentication.
SQL PowerShell provider enhancements
The SQL PowerShell provider now properly supports the WhatIf and Confirm parameters. This allows you to see the potential impact of a script operation and have the ability to confirm an operation before it is executed.
