The post on the SQLServer blog at TechNet by the SQL Server Tools Team today made me jump out of my seat.
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
In one release there are twenty-five new CMDLets for the new sqlserver module
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.
So SQLPS will still continue to work but will not be updated and will not contain the new CMDlets or the future new CMDlets.
So what new things do we have?
This month we introduce CMDLETs for the following areas:
- Always Encrypted
- SQL Agent
- SQL Error Logs
Chrissy LeMaire has written about the new SQL Agent cmdlets
Aaron Nelson has written about the new Get-SqlErrorLog cmdlet
Laerte Junior has written about Invoke-SQLCmd
All four of us will be presenting a webinar on the new CMDlets via the PowerShell Virtual Chapter Wed, Jul 06 2016 12:00 Eastern Daylight Time If you cant make it a recording will be made available on YouTube on the VC Channel https://sqlps.io/video
Always Encrypted CMDlets
That leaves the Always Encrypted CMDLets and there are 17 of those!
| 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-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. |
That seems to cover setting up Always Encrypted with Powershell , removing it and getting information about it. When the new SSMS update is dropped you will be able to start using all of this new functionality.
Just remember Import-Module sqlserver
CALL TO ACTION
Microsoft are engaging with the community to improve the tools we all use in our day to day work. There is are two Trello boards set up for YOU to use to contribute
https://sqlps.io/vote for SQLPS sqlserver PowerShell module
https://sqlps.io/ssms for SSMS
Go and join them and upvote YOUR preferred choice of the next lot of CMDlets
We have also set up a SQL Community Slack for anyone in the community to discuss all things related to SQL including the Trello board items and already it seems a good place for people to get help with 150+ members in a few days. You can get an invite here https://sqlps.io/slack
Come and join us