We as developers are accustomed to using integrated development environments (IDEs) in our software projects. Visual Studio has been a major player in the IDE space for many years, if not decades, and has allowed developers to use the latest software development processes to further improve quality and efficiency in software projects. Server management on the other hand, has generally been a second-class citizen for many products in the past. In general, this focus can be understood, if not agreed with. IDEs are tools that design and create software which can generate revenue for a business, whereas management tools generally only offer the benefit of some sort of cost savings, rather than direct revenue generation.
The SQL Server Tools of the past (pre-SQL 2005) were very much focused on fulfilling the requirements of being able to manage and query SQL Server instances and databases, but received no great investments in making the tools "comfortable", or even enjoyable to use. Advanced IDEs were firmly in the application development domain and application developers knew that databases were a storage system at best and therefore required no elegant tooling to work with them.
Luckily for us, the advent of SQL Server 2005, along with the release of the .NET Framework encouraged some people at Microsoft to invest a little more time and resources into providing an improved interface for both developers and DBAs for database and data management purposes. SQL Server Management Studio (SSMS) was born and unified the functionality of two legacy tools: Query Analyzer and Enterprise Manager. Anyone who has worked with SQL Server since the 2005 release will recognize the application regardless of whether they are using the 2005 release, or the latest 2016 build.
There have been several different names and releases of the second tool in this chapter: SQL Server Data Tools (SSDT), going back to SQL Server 2005/2008, when the tool was known as Visual Studio Database Projects (also known as Data Dude). The many incarnations of this tool since SQL Server 2005 have been focused on the development of database projects. SSDT has many of the tools and interfaces known to Visual Studio users and allows a seasoned Visual Studio user to quickly familiarize themselves with the tool. Particularly interesting is the improved ability to integrate database and business intelligence projects into source control, continuous integration and automated deployment processes.
In this chapter, we will be exploring:
- Changes in the release management of SQL Server tools (SSMS and SSDT)
- New SSMS features and enhancements
- Using live query statistics
- New developer tools for the R language
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.