The newest release of 2016 SQL Server Management Studio was released recently. This release continues to integrate support for Azure features as the adoption rate for cloud computing rises and the lines begin to blur between on-prem and cloud thanks in part to the SQL Server 2016 Stretch Database feature that allows an enterprise to host data with a lower rate of modification and access in Azure on low-cost storage to free up more expensive local storage and to improve IO and buffer management by removing the "stretched" data from query plans where relevant. We are seeing this Azure support from on-prem with the following new features from the August release:
- New authentication option 'Active Directory Universal Authentication'. This is a token-based authentication mechanism driven by Azure Active Directory that supports multi-factor, password, and integrated authentication mechanisms. Learn more about Active Directory Universal Authentication.
- Support for Azure-Resource Manager (ARM) type storage accounts in the Backup and Restore wizards.
- New 'Create database' dialog to streamline creation of Azure SQL databases in SSMS.
In this release we're also starting to see enhancements for PowerShell interaction with SQL Server:
- New PowerShell cmdlet 'New-SqlColumnMasterKeySettings' that adds support for creation of column master keys for arbitrary providers and key paths.
- New 'Get-SqlLogin' and 'Remove-SqlLogin' cmdlets to help perform SQL Server login management using PowerShell (Microsoft Connect item #2588952).
- Support for sending Database Console (DBCC) commands using SQL Server Analysis Services PowerShell cmdlets.
There are also enhancements to the Database Tuning Advisor (DTA)* as it pertains to new functionality in SQL Server 2016:
- Improvements in Database Engine Tuning Advisor (DTA) to support automatically reading a workload from the SQL Server Query Store.
- Improvements in Database Engine Tuning Advisor (DTA) to display index recommendations for clustered columnstore indexes, non-clustered columnstore indexes, and rowstore indexes.
*Whenever I mention DTA I always add the caveat to take any recommendations from the DTA with a high level of scruitiny before implementation and always test those recommendations thoroughly before implementing in a production environment.
Additionally we're finally starting to see some visible enhancements to SQL Server Management Studio itself with initial beta support for high-resolution displays which has been an open Connect item for some time now. If you've ever encountered a Management Studio dialog box with either code that runs off the boundaries of the dialog box or a compressed command button in a dialog box then you're familiar with these issues. We had been told that this would be fixed once SQL Server Management Studio was ported to a new shell and that took place earlier this year. It's nice to see Microsoft follow through on this frustration point in the SQL Server toolbox. There is also an interesting filtering function that has come to the Object Explorer in SQL Server Management Studio 2016. On those larger consolidated instances you can now apply filtering on the Databases node of Object Explorer. While this is what I would consider a limited functionality improvement it's a step forward in adding functionality in the graphical user interface (GUI) that should have been integrated years ago. Most GUI improvements we've seen in SQL Server Management Studio since it was released alongside the 2005 release of SQL Server have been only to support new features - we've not seen many GUI improvements not tied to functionality in the underlying product in over 11 years.
This release also includes bug fixes, mainly to clear up issues in support of new features of SQL Server 2016 such as AlwaysEncrypted (bug fixes in how Management Studio interacts with AlwaysEncrypted - not issues within the SQL Server 2016 platform) and some fringe crash and connectivity issues within Management Studio.
All that being said I've been witness to conversations between the Microsoft Data Platform MVPs and the SQL Server Program Group that makes me wary of upgrading to this release at the moment. I always advocate for installing new software in a test environment first before proceeding to your production or crucial environment. In the case of SQL Server Management Studio and any other tools that final, crucial environment is usually our laptops or workstations. We all know how difficult it can be to uninstall applications from these environments. If you have access to a test virtual machine I'd recommend evaluating there first and if you don't see any issues proceed with installing elsewhere.
It should also be noted that with this release Microsoft will now be using a numbering scheme for future SQL Server Management Studio releases. That being said this is officially recognized as the 16.3 release.
For a full list of improvements and bug fixes contained within the 16.3 release of SQL Server Management Studio please view the official documentation here. To download the 16.3 version of SQL Server Management Studio you can do so via this link.
