Azure Data Studio (ADT) is tool for managing SQL Server databases on-prem and Azure SQL databases. There are multiple reasons to use Azure Data Studio. Seems like Microsoft did not want to rewrite its nice and powerful tool SMSS, so they decided to develop new one from scratch with focus on DevOps style of work. After spending some time with it i came up with 6 reasons why use Azure Data Studio.
1. Lightweight and Cross platform
Firstly, ADT installation size is ~90mb against ~540mb for SSMS. Azure Data Studio has exact minimum of tools built in from scratch for possibility to work, gather and visualize data. However, toolbox can be adjusted depending on your work specific. Secondly, ADT opens 3 times faster then SSMS and, moreover, it saves your opened tabs. Thirdly, Now developers are not dependent on OS anymore. Linux and Mac OS are supported.
2. Multiple Language support
ADT supports list of languages – SQL, PowerShell, Python, Spark, Scala scripts. What that mean for us? Now you do not need to have couple of windows open and dance between them because now you can switch between languages in just one click. Moreover, ADT has integrated terminal which supports such tools like PowerShell, sqlcmd, bash, bcp, ssh.
3. Notebooks
In my opinion, this is one of the most powerful feature. Notebooks are formatted text and code blocks which can be formatted as you wish. You dont like how it looks? No problem, just switch to “Split View” and edit text in HTML way. But don’t forget about markdown language. Power of notebooks can be useful in several cases.
For instance – notebooks can be used as instructions for disaster recovery (DR). Required steps can be powered by pictures and code. Since PowerShell is supported you are almost fully covered. You can even easily transform your existing code into well formatted notebook with pictures and text – just click “Export as Notebook”.
Second example – Run queries on foreign environment :). You prepare notebook and ask client to run it on his environment (where you do not have access) and save results in notebook to send it back to you.
Third example – make impressive presentation with notebooks. You can even copy full article from internet and paste it with all formatting into notebook. Just try this one out – copy this article into ADT notebook.
4. Coding support
When you as DBA want to quickly write some T-SQL code it is not always easy to remember syntax and table definition from heart. That is where IntelliSense and Peek Definition comes into play. First one, gives templates and hints for working with database objects. This is very useful, for instance, when you want drop database, but can not remember definition (do not drop any production db here). Second feature is the one which allows to check any object definition on the go in code.
For those who are not DBA (as i am) would be interesting to store and deploy code in GIT. ADT in this case is way to go, because have good integration with version control. For instance, you can store database definition code in GIT project. After adding changes to code you can either publish them straight to database or just generate script.
5. Customizable dashboards
By double click on server or database in ADT you may see some predefined dashboards. Why not to add some custom chart here? First of all, write and run your query. This particular query selects top 10 biggest database objects by size.
SELECT top 20 sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as SizeMB
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
order by SizeMB desc
GOSave query on computer as file. After query execution, click on “chart”. On chart tab choose “Horizontal bar” as chart type; “Vertical” for data direction; mark “Use column names as labels”. Now click “Create insight”. We have got JSON code which can be pasted into Manage -> Settings -> Data -> Dashboard -> Database:Widgets -> Edit in settings.json. Now when double click on database should get something like this
6. Extensions
Azure Data Studio toolbox can be powered by many different extensions which simplify daily work. Extensions does not need any additional pre-configuration. Installation happens in one click. One of extensions is SandData which allows to quick visualize query data. Lets take same query from previous example and execute it, but this time query top 100 objects.
What next?
I don’t think that ADT will replace SSMS, at least in first near future. It does not have such things like Actual Plan, Security management, different kind of Wizards. But there are several reasons to use Azure Data Studio because it may be useful when you use different OS then Windows; need make quick data visualizations; want to have one tool for running you code. Moreover, this tool and its extensions are continuously improving, so we can expect increase of feature. I guess that before you start work with this nice tool you probably want to move registered servers from SSMS into Azure Data Studio.
