How do you manage SQL Server?
Azure Data Studio is the new (ish) open-source, cross-platform tool from Microsoft to manage and interact with your SQL Servers or PostgreSQL instances. If you haven’t used it yet, I recommend you check it out (https://docs.microsoft.com/en-us/sql/azure-data-studio).
Once upon a time, we had two tools to manage SQL Server:
- Query Analyser
- Enterprise Manager
The first was for queries (funnily enough). The second was for management tasks, such as taking GUI backups. The second was also named by a friend and ex-colleague as merely "The Mangler”. That might just have been relating to my TSQL though, who knows?
Microsoft then produced SQL Server Management Studio (SSMS) as your one-stop shop for all SQL Server needs. Firstly installed via media as client tools, then latterly via an independent download with release cadence of its own.
It’s now as comfortable as an old pair of slippers, and you don’t want to change. But would you walk out on Centre Court with a wooden racket? You cannot be serious.
Five reasons you should download and experiment with Azure Data Studio today:
Azure Data Studio supports notebooks, my favourite feature. You can add both code and markdown to describe, enrich and document your work for yourself and to share with others.
- Do you have runbooks for tasks such as disaster recovery? Notebooks are perfect here, where you can annotate, comment and add links to communicate your process. Sure, you can add comments to SQL files, but markdown is a far better experience.
- Unlike plain old SQL files, Azure Data Studio saves the results of your queries along with the code. Maybe you have a helpdesk function where you send scripts for customers to run and troubleshoot. They can then send you the notebook, including the results—a great option to have.
- Another perfect use case for notebooks is training and presentations. Take your audience through the notebooks, add markdown, and run each code cell in isolation.
- You even have the option to change the notebook kernel from SQL to other languages such as Spark, Python or PowerShell. So you have total flexibility to choose your language of choice and the best tool for the job. You are limited to one kernel per notebook, so you can’t mix and match SQL and PowerShell within the same notebook, for example.
Here is a screenshot to demonstrate markdown. See the headings, bold, hyperlinks and italics. You can also see the result of running the command, that the notebook saves in JSON format.
Open a new query and type “sql”. An Intellisense list appears showing all the snippets you can choose. The Intellisense works in a wonderfully helpful fashion. Type “sqlcol” and the list filters to:
So the filtering works even if your combination of letters doesn’t appear consecutively.
Hit your snippet of choice to get the desired code block without the monotony of typing it out manually.
Create additional bespoke snippets via the Command Palette (Ctrl + Shift + P). Choose “Configure User Snippets” then follow the instructions in sql.json.
Azure Data Studio is extendable with many options to expand your environment and user experience. Examples are:
- SQL Server Agent
- SQL Server Profiler (I know, I know, will this ever be deprecated?).
- Query History
- There are many more including schema compare, whoisactive and tools from companies such as Redgate and SentryOne.
Hit the extensions icon which is bottom left of this screenshot to install:
Install the SandDance extension to turn your plain old result-sets into appealing visuals and graphically represent your data and find patterns. Run your query as usual, then hit the visualiser icon to see charts such as:
- SQL Server
- Azure SQL DB
- Big Data Clusters
You have the option to connect to SQL Server at either the database (see master below) or instance level (localhost below).
The list goes on. You can group connections as you choose and colour-code the groups for easy navigation. Hit the connections tab to view, configure and add new connections. Here is a screenshot:
There were five features of Azure Data Studio I love. There are many more, so give it a try and see what you think.
Azure Data Studio has a modern, clean look and feel. Microsoft has based Azure Data Studio on Visual Studio Code, so if you’re used to that tool, the environment will feel familiar.
However, you are going to need that trusty old wooden racket for some time to come. There are many tasks, such as managing Analysis Services, not supported in Azure Data Studio (yet). The SSMS GUI wizards are also not available to you in Azure Data Studio. However, Microsoft updates Azure Data Studio with remarkable regularity with features and extensions added all the time. Every SQL Server professional should install, update and use both tools and be excited about what is to come.