Installing and updating SQL Server tools

The very beginning of our journey with SQL Server starts with the installation process. In previous versions of SQL Server, the data management and development tools were delivered together with the SQL Server installation image. As such, if a developer wanted to install SSMS, the setup of SQL Server had to be used to facilitate the installation.

As of SQL Server 2016, Microsoft made the very smart decision to separate the management tools from the server installation. This is not only a separation of the installation medium, but also a separation of the release process. This separation means that both products can be developed and released without having to wait for the other team to be ready. Let's take a look at how this change affects us at installation time.

In the following screenshot, we see the SQL Server Installation Center screen. This is the first screen we will encounter when running the SQL Server setup.exe provided in the installation image. After choosing the Installation menu point on the left, we are confronted with the generic installation options of SQL Server, which have only minimally changed in the last releases. The second and third options presented on this screen are Install SQL Server Management Tools and Install SQL Server Data Tools. If we read the descriptions of these options, we note that both links will redirect us to the download page for either SSMS or SSDT. This is the first clear indication that the delivery of these tools has now been decoupled from the server installation.

Figure 3.1: SQL Server Installation Center

After clicking on Install SQL Server Management Studio, you should be redirected to the download page, which should look like the following screenshot:

Figure 3.2: SQL Server Management Studio download page

The download page offers us the latest production version of SSMS on the main page, together with any beta versions or community technology preview (CTP) of the software. We are also able to see details of the current release, view and download previous releases, and find information on change logs and release notes on the left of the web page.

Figure 3.3: SQL Server Management Studio Setup Dialogue

After downloading the desired version of SSMS, we can run the installation just the same way as with previous versions of the tool. The next immediately noticeable difference to previous versions is the installation program itself. SSMS 2016 is based on Visual Studio 2015 Isolated Shell and as such, uses similar color schemes and iconography to Visual Studio 2015.

Once the installation has completed, we can start SSMS and are greeted with a similar starting screen to all previous versions of SSMS. The subtle differences in the application are exactly that, subtle. The splash screen at application start shows the SSMS is now "powered by Visual Studio"; otherwise there are no major indications that we are working in a tool based on Visual Studio. The interface may feel familiar, but the menus and options available are solely concentrated on working with SQL Server.

Previously, SQL Server and the SQL Server Tools were packaged together, leading to bug fixes and feature additions to the tools having to be bundled with Cumulative Updates (CU), Service Packs (SP), or general version releases of the SQL Server product. Through the decoupling of the applications SSMS and SSDT from SQL Server, we no longer have to wait for CUs, SPs, or version releases of SQL Server before we can receive the required/requested features and fixes for SSMS and SSDT. The SQL Server tools team has taken immediate advantage of this and has made regular releases to both SSMS and SSDT since the general release of SQL Server 2016. The initial release of SSMS 2016 was in June 2016 and there have been subsequent update releases in July 2016, August 2016, September 2016, and December 2016. Each release has included a range of bug fixes and feature additions and are much more rapidly deployable when compared to previous versions of SQL Server and SSMS.

A further advantage of the separation of the data tools from the server product is the reduced overhead of managing the installation and updating the tools in a network. The process of updating an already installed SSMS installation is demonstrated in Figure 3.4 where we see that a Check for Updates... option has been included in the Tools menu of SSMS. Further to this, the separation of the tools as a standalone installer will reduce the administrative overhead in larger organizations where software is deployed using centralized management software. Where, previously, the larger ISO image of a SQL Server installation was required, now a smaller standalone installer is available for distribution.

Figure 3.4: Check for Updates in SQL Server Management Studio

We also have the option to request that SSMS automatically checks for updates at application start. This will create a notification balloon message in Windows if a new version is available. A sample notification on a Windows 10 machine can be seen in the following screenshot:

Figure 3.5: Update notification for SQL Server Management Studio.

Once the update check has been opened, SSMS connects to the update systems of Microsoft and performs checks against the currently installed version and the latest downloadable release of the software. If updates have been found, these will be offered via the update mechanism, as shown in Figure 3.6. We are also able to decide whether the automated update check should be performed or not.

Figure 3.6: SQL Server Management Studio Update Checker

These enhancements to the install and update process are not mind-blowing, especially considering that these features have been available in other products for years or even decades. However, these are the first main improvements which have to do with the switch from a standalone application to an application based on the extremely successful Visual Studio framework.