Why You Want to Use the Latest SQL Server Management Studio Release

We have two main built-in tools for interacting with a SQL Server database platorm: SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT). This post is about SSMS and why you may want to install the latest release...even if you're not ready to upgrade to SQL Server 2016.

SSMS is now a standalone tool which is being updated on a monthly basis for bug fixes and new features. This is big because it allows the tooling team to be speedier with releases. The latest download file is easily located on the web.

Frequently you'll see it referred to it as SSMS 2016, though technically speaking it is "version-less" now. It's sometimes referred to now as SSMS 16.x which correlates with the release numbering at this time. If you are checking your Help>About menu in SSMS to check what's installed, you'll need to translate the build number shown in Help>About to the release number which can be found in the changelog:

SSMS is now based on the Visual Studio 2015 shell. This gives it some performance improvements and removes the dependency on installing .NET Framework 3.5 as a prerequisite. **For this reason, you will want to make sure Visual Studio 2015 is installed first followed by SSMS and SSDT.**

SSMS is supported for managing SQL Server 2008 through 2016 (except for SSIS instances which sadly still require a version-specific SSMS at the time of this writing). If you manage numerous servers on different versions, this unification is fantastic. There is partial support for managing pre-2008 instances. And, of course as you'd expect, the newest SSMS release supports various new features in SQL Server 2016 such as Query Statistics, Live Query Plans, Compare Showplan, Security Policies for row-level security, and so on with all the new 2016 goodies we have. 

SSMS also supports managing Azure components such as Azure SQL Database and Azure SQL Data Warehouse, as well as hybrid cloud features such as StretchDB and Backup to URL. This additional unification is really, really handy.

SQL PowerShell updates are also shipped with SSMS. In order to take advantage of this monthly release cadence, the latest and greatest SQL PowerShell module is now called "SqlServer" whereas the the legacy module, which does *not* have the newest cmdlets released since mid-2016, is "SQLPS". More info here: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/.

If you'd like to follow and vote for new feature requests, the is a SQL Server Community Trello board here: https://trello.com/b/M9NmFPfv/sql-server-management-studio-2016-enhancements.

Finding More Information

You Might Also Like...

Why You Should Use a SSDT Database Project For Your Data Warehouse

Overview of Azure Automation

Microsoft BI Integration for the Four Primary Report Types

We are currently experiencing a major evolution of the MSBI reporting toolset in accordance with the Microsoft BI Roadmap announced in October 2015. This diagram depicts current state the way I understand it.

(Updated as of mid-June 2017)

Also, here's a short video walking through the diagram:  http://www.blue-granite.com/blog/video-overview-of-microsoft-bi-reporting-tool-integration. 

  • Analytical reports:  produced in Excel
  • Interactive reports: produced in Power BI Desktop
  • Paginated reports: produced in Reporting Services (SSDT) or Report Builder
  • Mobile reports: produced in the Mobile Report Publisher (previously Datazen)

The four primary report types are in the process of becoming more deeply integrated into:

  • The Power BI Service (a cloud service), and
  • Power BI Report Server (on-premises portal option - includes 3 report types)
  • Reporting Services portal (on-premises portal option - includes 2 report types; requires SQL Server 2016 in native mode). 

As you'll see on the above diagram, every report type isn't compatible everywhere yet though I expect we'll see that over time. As things evolve, I'll keep the diagram updated. Hope you find it useful.

Following is how each of the report types look in the SSRS portal and Power BI when this post was originally written. Since then, there have been quite a few changes (i.e., Power BI Reports can only be rendered in Power BI Report Server -- not in a traditional SSRS portal).

Getting Started with SQL Server 2016

If you've been thinking there's a ton of new stuff in SQL Server 2016, then you'd be right. It is huge. Here's a brief recap of each of the most valuable features & enhancements that I wrote up over here: http://www.blue-granite.com/sqlserver.

In terms of some hands-on time to play around in an isolated environment, there's an easy way to become acquainted with the new features.

1. Create an Azure Virtual Machine with SQL Server 2016 Image

Create an Azure virtual machine using the template for CTP 3.3 (Update: now RTM as of 6/1/2016). 

A few things to be aware of in this virtual machine:

  • Integration Services, Analysis Services (MD mode), Reporting Services, Data Quality Services, and Master Data Services are installed and running (though not everything is fully configured, such as SSRS).
  • Local NT service accounts are used for each service. If you plan to use this VM in a completely isolated way then it’s ok. Typically, however, we utilize domain-based service accounts.
 
  • IE Enhanced Security Configuration is on. If you intend to do the next step from within the VM, you'll want to turn it off in Server Manager. (My VM will only be for testing purposes, and will only contain sample data, so I'm less concerned about this than with a normal environment.)
 
  • Both data and log are defaulted to F: drive (which is the only data drive), and TempDB is on the C: drive. Since this is just a temporary environment for learning purposes, for it's ok that it doesn't follow best practices regarding use of physical drives. 
 

Overall, the objective with the VM is to test out features, but not evaluate performance aspects.

When the VM has been created, you can connect to it through RDP session (or from your local SSMS client):

 

2. Restore Sample Databases

There are AdventureWorks sample databases available which already have some new features implemented such as Always Encrypted, Row Level Security, Dynamic Data Masking, etc. You can find these 2016 sample DBs here: https://www.microsoft.com/en-us/download/details.aspx?id=49502 .

There are three items to be downloaded: Two relational DBs, and a zip file of samples.

SQL2016Samples.jpg
 

Within Management Studio, you want to restore these backup files to the SQL Server instance running in the virtual machine via the GUI or T-SQL, whichever you prefer. 

 

As an alternative to AdventureWorks, you can download the new Worldwide Importers Inc samples which are available here:  https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v0.1.

3. Work with the Samples

Now we’ve arrived at the fun part. The zip file that came with the AdventureWorks samples contains the following samples:

  • Advanced Analytics (R Services)
  • Always Encrypted
  • Data Masking
  • In-Memory Analytics
  • In-Memory OLTP
  • JSON
  • Polybase
  • Query Store
  • Row-Level Security
  • Stretch DB
  • Temporal

The ReadMe.txt file refers you to which file is the starting point within each subfolder:

For example, here’s the items in the Advanced Analytics:

As you can see, the samples are a great way to begin getting familiar with the new features very quickly.

One final comment: Don’t forget to stop your VM when you are finished to avoid incurring charges. You can also use Azure Automation to stop one or more VMs at a scheduled time. I wrote a bit about that here:  How to Stop an Azure VM on a Schedule.

 

The VM comes with Analysis Services in multidimensional mode installed. If you want to install tabular mode, or another feature, you can find the setup.exe file to run SQL Server setup located at C:\SQLServer_13.0_Full within the VM.

Finding More Information

BlueGranite - Overview of SQL Server 2016 Features

MSDN - SQL Server 2016 Technical Documentation