Category: Database

Database

An Update to SQL Server Management Studio 18 is Available

This week we see an update for SQL Server Management Studio, version 18.1. This release is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017.

What’s New in this Release

  • Database diagrams – Database diagrams were added back into SSMS. If you did not know they removed this from the 18.0 release. For more details, see Database Diagrams.
  • SSBDIAGNOSE.EXE – The SQL Server Diagnose command line tool was added back into the SSMS package.
  • Integration Services (SSIS) – Support for scheduling SSIS package, located in SSIS Catalog in Azure or File System, in Azure. There are three entries for launching the New Schedule dialog, New Schedule… menu item shown when right-clicking the SSIS package in SSIS Catalog in Azure, Schedule SSIS Package in Azure menu item under Migrate to Azure menu item under Tools menu item and “Schedule SSIS in Azure” shown when right-clicking Jobs folder under SQL Server agent of Azure SQL Database Managed Instance.

For details about what’s new in this release, please see the SSMS release notes.

Getting the Update

Head over to https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and go to the Download SSMS 18.1 section.

Version Information

  • Release number: 18.1
  • Build number: 15.0.18131.0
  • Release date: June 11, 2019

NOTE: SSMS 18.1 is the latest general availability (GA) version of SSMS. If you have SSMS 18.0 (GA) installed, installing SSMS 18.1 upgrades it to 18.1. If you have an older preview version of SSMS 18.0 installed, you must uninstall it before installing SSMS 18.1.

Supported SQL Offerings

This version of SSMS works with all supported versions of SQL Server 2008 – SQL Server 2019 preview and provides the greatest level of support for working with the latest cloud features in Azure SQL Database and Azure SQL Data Warehouse.

Additionally, SSMS 18.x can be installed side by side with SSMS 17.x, SSMS 16.x, or SQL Server 2014 SSMS and earlier.

Enjoy!

References

Download SQL Server Management Studio (SSMS)

SQL Server Management Studio – Changelog (SSMS)

DatabaseDeveloper

SQL Server Management Studio (SSMS) 18.0 now Generally Available

SQL Server Management Studio v18.0 is now generally available and is the latest version that provides support for almost all feature areas on SQL Server 2008 through to 2019 preview.

If you have a preview of SSMS 18.0 installed, you will need to uninstall before you can install SSMS 18.0 GA. SSMS 18.0 will also not replace an older version of SSMS but will run side by side with it.

image

Release Highlights

Here is a brief list of is new in SSMS 18.0.

  • Support for SQL Server 2019. SSMS 18.0 is the first release to be fully aware of SQL Server 2019 (compatLevel 150).
  • SSMS 18.0 is based off the Visual Studio 2017 Isolated Shell.
  • Smaller download size.
  • Accessibility improvements.
  • Ability to install to a custom folder.
  • Requires .NET Framework 4.7.2 or greater.
  • Support for High DPI (by default).
  • Added integration for Azure Data Studio.
  • Still no official support for Dark Mode. Please see my article on how to enable Dark Mode for SSMS.
  • Database Diagrams has been removed. Seriously it’s gone! You will need to install a previous version of SSMS (like 17.8.1) to get this functionality.

Summary

Dark mode is still not available and remains a feature you have to manually enable. It’s also weird that they dropped support for the Database Diagramming tool without any warning or guidance to replace it. This was a gem of a tool inside of SSMS and was simple to use for visualizing database relationships, and documentation. You will need to rely on an older version of SSMS or use another tool. Other than that it looks like a great update to SSMS.

For more details on what’s new, bug fixes and deprecations, please take a look at the detailed release notes.

To get started download SSMS 18.0 (GA) now.

Enjoy!

References

AzureDatabase

February release of Azure Data Studio is now available

image

The February release of Azure Data Studio is now available to download and includes the following key highlights:

  • Introducing Admin pack for SQL Server
  • Auto-sizing columns in results
  • Notebook UI improvements
  • Added Profiler filtering
  • Added Save as XML
  • Added Data-Tier Application Wizard improvements
  • Updates to the SQL Server 2019 Preview extension
  • Turned on results streaming by default
  • Deploy scripts
  • Bug fixes

For a list of the complete updates, refer to the release notes.

Download Azure Data Studio now!

References

https://cloudblogs.microsoft.com/sqlserver/2019/02/13/the-february-release-of-azure-data-studio-is-now-available/

Database

Installing Extensions in SQL Operations Studio

Just like VS Code, extensions provide more functionality to SQL Operations Studio. These extensions can come from Microsoft or the community.

Adding Extensions to SQL Operations Studio

1. Open the Extensions manager by going to the View menu and selecting Extensions. After clicking on the Extensions menu item, the Extensions navigation icon shows up on the left side. I’m not sure why this isn’t always available like it is in VS Code.

image

2. Browse and select an available extension. At this time there are 9 extensions available to choose from. 

image

3. Click on the green button to install the desired extension. In my case I’m trying to install RedGate SQL Search which is a tool I use in SQL Server Management Studio. This will download the extension but if you try to double click and run it, the installation will fail. Instead you need to install it from SQL Operations Studio.

4. From SQL Operations Studio, press Ctrl/Cmd+Shift+p, and type Extensions: Install from VSIX and then press enter.

image

5. You should then see a notification in the bottom right corner of SQL Operations Studio indicating the extension has successfully been installed.

image

Enjoy!

References

What is Microsoft SQL Operations Studio

Installing SQL Operations Studio

Extending the functionality of SQL Operations Studio

DatabaseProductivity

How to Enable Dark Theme for SQL Server Management Studio

Updated Feb. 21, 2018 – Each time you install a newer version of SSMS, like 17.5 that released last week, you will need to go and update the configuration file and comment out the Dark theme references as described below.

If you’re like me and you like to use the Visual Studio Dark theme and wish you could use this theme for SQL Server Management Studio (SSMS), unfortunately, there is no official support at this time (keep bugging Microsoft for this feature please). Currently SSMS 2016 and SSMS 17 support both the Blue and Light themes.

If you weren’t aware, SQL Server Management Studio is built on the Visual Studio shell, so it does support the Dark theme but it’s currently disabled due to “unfinished work” with various parts of the application like the Object Explorer and Output panes.

Here is my current SSMS 17 using the Light theme:

image

To enable the Dark theme follow these simple steps

1. Close down all running instances of SSMS

2. Open Windows Explorer and browse to the following location to change the configuration file ssms.pkgundef

    • For SSMS 2016: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio
    • For SSMS 17: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio

3. Type the name of the file into the search box at the top right corner and then right click to edit this file. Note: You will need to edit this file with Administrative rights.

image

4. Now search the file for the following line // Remove Dark Theme and then comment out each line in this section by using // at the start of each line in this section as shown below:

image

5. Save the file and now re-launch SSMS. After restarting SSMS, go to Options you will see the Dark them listed. Select the Dark theme and then press the OK button.

image

Your SSMS should now be using the Dark theme, similar to what my SSMS looks like now after enabling this theme. Ah this is much better!

image

For the most part its dark but where it fails is in the Object Explorer and Output panes as you can see here:

image

There is a way to go into Options and tweak the colors for the Output pane (results grid and messages), but that is extremely tedious and not worth the effort in my opinion.

Alternate Solutions

Now I’m not sure why this is not fully supported yet, especially for the Object Explorer and the Output panes. People keep asking for this feature and release after release it’s still not properly supported.

Now if you look at Visual Studio, there is a SQL Server Object Explorer and when you run a query, the results window (text and grid) supprt support the dark theme as shown below. So this is an alternate method if you don’t want to change SSMS.

image

For those of you that are not developers and use SSMS for interacting with your SQL Servers and want to use a dark theme editor, you can install a bare bones instance of Visual Studio 2017 with just the database tooling by selecting the Data storage and processing workload.

image

Summary

Hopefully, a future update to SSMS will enable the Dark theme out of the box and until then this is a temporary solution that may or may not work for you. For me, I spend most of my time working with SQL queries that I don’t need Output or Object Explorer visible, so I just collapse them.

This method works for both SSMS 2016 and SSMS 17.

Enjoy!

References

https://www.sqlshack.com/setting-up-the-dark-theme-in-sql-server-management-studio/

Database

Free ebook – SQL Server Internals: In-Memory OLTP, Inside the SQL Server 2016 Hekaton Engine

SQL Server Internals: In-Memory OLTP eBook cover

A new free ebook by Kalen Delaney on the internals of SQL Server 2016 In-Memory OLTP has been published by Simple Talk (redgate), which introduces and explains how SQL Server 2016 In-Memory OLTP engine (a.k.a. Hekaton) works. This book goes into detail about the in-memory engine and will help you with migrate existing databases and tables over to Hekaton for beyond imaginable performance.

“The SQL Server 2016 In-Memory OLTP engine (a.k.a. Hekaton) is designed to exploit terabytes of available memory and high numbers of processing cores. It allows us to work with memory-optimized tables and indexes, and natively compiled stored procedures, in addition to the disk-based tables and indexes, and T-SQL stored procedures, that SQL Server has always provided.”

Working with Hekaton in-memory data is very accessible using T-SQL and SSMS. However there are some limitations and this book goes into detail about what’s possible and what’s not and how to get around the issues.

Download your the free ebook here.

Enjoy!

References

https://www.red-gate.com/simple-talk/books/sql-books/sql-server-internals-in-memory-oltp/

https://www.microsoft.com/en-us/sql-server/sql-server-2016

Database

An Update to SQL Server Management Studio is Available

image

This week we see an update for SQL Server Management Studio, version 17.2. This release is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017.

image

What’s New in this Release

  • Multi-Factor Authentication (MFA)
  • The connection dialog box now supports the following 5 authentication methods:
    • Windows Authentication
    • SQL Server Authentication
    • Active Directory – Universal with MFA support
    • Active Directory – Password
    • Active Directory – Integrated
  • Database import/export for DacFx wizard can now use Universal Authentication with MFA
  • ADAL managed library used by Azure AD Universal Authentication with MFA was upgraded to version 3.13.9
  • A new CLI interface supporting Azure AD admin setting for SQL Database and SQL Data Warehouse
  • Output window has entries for queries run during expansion of Object Explorer nodes
  • Enabled View designer for Azure SQL Databases
  • Changes to the default scripting options for scripting objects from Object Explorer in SSMS
  • Added support for National Clouds in "Restore from URL"
  • QueryStoreUI reports now supports additional metrics (RowCount, DOP, CLR Time etc.) from sys.query_store_runtime_stats
  • IntelliSense is now supported for Azure SQL Database
  • Security: connection dialog will default to not trusting server certificates and to requesting encryption for Azure SQL Database connections
  • General improvements around support for SQL Server on Linux
  • Performance Dashboard server report now available as a default report
  • Showplan node search allows searching in plan properties. Easily look for any operator property such as table name

For the full list of changes, see SQL Server Management Studio – Changelog (SSMS).

Getting the Update

Head over to https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and you will find two links for installation. The first links downloads and installs SSMS 17.2 where as the second link will update an existing 17.x installation.

This update will not upgrade or replace SSMS versions 16.x or earlier. SSMS 17.x will install side by side with previous versions. If you have multiple SSMS versions installed than the latest is labeled as Microsoft SQL Server Management Studio 17 and has the following icon:

image

Starting the Installation

Installation is quick and easy…

sshot-8

sshot-9

sshot-11

After the installation is complete, when you open up SSMS 17.2 and look at the About dialog you will see the following components updated:

image

Sadly we still don’t have an official Dark theme for SQL Server Management Studio. I recall reading comments from the SQL team last year that they needed to do work on all the dialogs before releasing this theme. If the base SSMS IDE supports the Dark theme than just release it You can work on the dialogs as needed and provide them in future updates.

Enjoy!

References

Download SQL Server Management Studio (SSMS)

SQL Server Management Studio – Changelog (SSMS)