Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
New Features in SQL Server 2016 PowerShell
Using PowerShell, you can navigate through the SQL Server database schema and management hierarchies, as well as run queries, set up replication and Availability Groups, and run backups and restores. PowerShell can be great option for managing multiple servers and remote systems.
Michael Otey
September 15, 2016
3 Min Read
PowerShell has become an important management option for SQL Server professionals. While the initial implementation didn’t support all of the different SQL Server management features, Microsoft has been rapidly evolving SQL Server’s PowerShell support. Now, almost all SQL Server management functions are supported by PowerShell.
Using PowerShell, you can navigate through the SQL Server database schema and management hierarchies, as well as run queries, set up replication and Availability Groups, and run backups and restores. PowerShell can be great option for managing multiple servers and remote systems.
New PowerShell Module
Without a doubt, the biggest PowerShell change in SQL Server 2016 is the new PowerShell module that Microsoft has introduced as a part of SQL Server Management Studio (SSMS) 2016. The SQL Server PowerShell module that ships with SSMS has changed from SQLPS to SqlServer. With SQL Server 2016 Microsoft has also added a new EXE wrapper that SSMS uses to instantiate the SQL PowerShell environment. The new module will be installed to %Program FilesWindowsPowerShellModulesSqlServer, which means that no updates to $env:PSModulePath are required. To take advantage of the new modules, any PowerShell scripts that contain Import-Module SQLPS need to be changed to be Import-Module SqlServer to take advantage of the new functionality. The older sqlps module is still present
Invoke-SqlCmd Cmdlet Improvements
The Invoke-SqlCmd, which is used to execute T-SQL queries, now supports the OutputAs parameter. The new parameter enables you to specify DataRows, DataTables or DataSet as the object type to return.
New Cmdlets
As you would expect with a new PowerShell module, there are also a number of new cmdlets. The SQL PowerShell cmdlets work with all supported versions of SQL Server, as long as the version of SQL Server supports the specific feature set. The following is a list of the new cmdlets in SQL Server 2016 Powershell.
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 -- Creates 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.
HPE and Microsoft are the underwriters of this article.
About the Author
You May Also Like
