Yesterday, SQL Server Management Studio 18.0 shipped. Here’s the official announcement, the download page, and the release notes.
Yes, it’s still free, and yes, they’re still adding features. Yes, they removed database diagrams and the debugger. (Update Apr 27: if you want the debugger, use Visual Studio – download.) No, SSMS 18 doesn’t run on Windows 8 or older. Yes, it still has bugs (all software does), but they’ve been working hard on making it less buggy, as evidenced by the release notes:
You, dear reader, are so lucky. Every time there’s a new release of SQL Server or SQL Server Management Studio, you can grab the latest version of SSMS and keep right on keepin’ on. Your job still functions the same way using the same tool, and the tool keeps getting better.
And it’s free. You don’t have to ask the boss for upgrade money. You can just download it, install it, and take advantage of things like the cool new execution plan est-vs-actual numbers (which also cause presenters all over to curse, knowing that they have to redo a bunch of screenshots.)
I spend a lot of time jumping back & forth between SQL Server and Postgres, and lemme just tell you, the tooling options on the other side of the fence are a hot mess. It’s a sea of half-ass, fully-expensive tools with a mishmash of feature coverage. Pick one, and you just pray that the maker keeps updating it and adding new features. Sometimes they don’t. Often, they don’t.
Microsoft isn’t just building one good free tool.
In April 2019, most people who work with Microsoft SQL Server should be using SQL Server Management Studio (SSMS.) It’s free, very robust, and it’s easy to find tutorials that show you how to do what you want to do.
However, for some of us – a very small minority, and I’m in that group – mid-2019 marks the time to add a new tool to your toolkit. Azure Data Studio (ADS) is kinda like SSMS, but for developers. It’s less about management, and more about code authorship. As of this writing, it’s best suited for people who:
- Use Macs or Linux as their daily workstation
- Query both SQL Server and PostgreSQL
- Don’t need to tune those queries to make them go faster
- Need to source-control queries, say with Github
- Want to share some queries in an easy-to-follow-along format (Jupyter notebooks)
- Don’t mind using a less mature, less documented, rapidly changing tool
I’m being exceedingly careful here about marking a narrow audience. People who fall slightly outside of that audience – say, Windows users who need to performance tune queries – may also use ADS, but have a less satisfactory user experience. They might download & install it, use it a few times, and go, “What the hell, this tool is garbage.” The tool just isn’t ready for those users yet – maybe it will be in time, but right now, let’s focus on who it’s ready for.
Azure Data Studio is ready for me.
I’ve used Macs for over a decade, and for the last couple of years, I’ve been splitting time between SQL Server and PostgreSQL. Recently, I’ve been spending more and more time in Azure Data Studio accomplishing my work on both databases.
You still won’t see me using it in most of my training classes or presentations yet. As a trainer, I need to meet my students where they are, and the vast, vast majority of the SQL Server world is still in SSMS – and that’s totally fine. I’m not on a mission to migrate you.
If you have job duties like me, go give Azure Data Studio a shot, and if you need help, join the #azure-data-studio channel in the SQL Server community Slack.
If you’re more mainstream, go get the new release of SSMS 18.
Either way, it’s free, and you’re lucky.
65 Comments. Leave new
I’ve used DataGrip for the last couple of years, I’m in no way using all of its features but I find it really convenient as it connects to just about anything (I’m using it for MySQL/MariaDB and Postgres).
https://www.jetbrains.com/datagrip/
Although pgAdmin 4+ has a strange “in-browser” implementation, it’s still the go-to solution for doing in-depth Postgres work (although the hardcore developers would probably choose the command line over a GUI!)
Greg – yeah, I abhoooooor pgAdmin. I bought DataGrip and it wasn’t bad, but when Azure Data Studio came out (for free!), I couldn’t justify DataGrip anymore. ADS does everything I need that DataGrip did, and you can’t beat the price.
Another worthy consideration might be DBeaver (https://dbeaver.io/download/). The community edition is free. If you would like to use for Cassandra, Mongo, Reddis, etc. you need to purchase enterprise however at $150 a seat it is still on the inexpensive side.
Call me prejudiced, but when I see an app in Java, that’s kind of a non-starter for me. I haven’t seen a fast Java app with a great user interface yet, and I’ve given up looking. Hopefully that link helps other folks though.
I don’t think a fast java app will ever happen unless there is a completely radical re-implementation of how Java works. Java is just fundamentally slow and in its effort to be platform agnostic, it leaves out many platform-specific optimizations that are available in .net.
If developers want to make something cross-platform and fast, they at least need to use C++ if not C, but then software developers already find ways to introduce exploits into even high level languages like java or C#, without having direct access to memory.
Isn’t aforementioned DataGrip, and other JetBrains IntelliJ descendants for that matter, a Java app?
Notice that this post is about me switching to Azure Data Studio. 😉
I’ll have a look at ADS then… I think my DataGrip subscription renews next month! Renewal cost isn’t too bad – I think I just need to invest some time in using the projects/workspaces properly. I kind of use it like a SQL version of Sublime Text!
I would use ADS most of time if there was a SQL Prompt extension available right now.
I use SQL Prompt mainly for code-completion and macro expansion. I’m very happy with the User Snippets functionality in ADS, and *some* of the time the built in InteliSense satisfies. YMMV
SSMS v18 does not have a debugger? I spend a lot of time debugging tsql. Is there another debugging option if you want to continue using SSMS?
Brenda – no, Microsoft deprecated both the debugger and database diagrams. When you need to use the debugger, you’ll want to run the older version of SQL Server Management Studio (17.9.1.)
I’m just gobsmacked. How can you be a developer without a debugger? Any idea what was the reason for removing the debugger?
Brenda – maybe write better code with less bugs.
I KIIIID, I KIIIID. I actually don’t know anybody who uses the built-in debugger – you should be proud that you did, but most folks just use things like print statements and logging things to tables. From what I hear, the reason is just limited resources at Microsoft for the free tool, and limited usage of that feature.
Hmmm … then MS should pull the debugger from .NET also to help free up some resources since they don’t think that developers use debuggers. Sure that the .NET people could use write to a message box or a log tile.
I use the debugger – if you have to rely on print statements, you’ve just stepped back in time about a decade and a half. I guess the issue will arrive when 17.9.1 SSMS doesn’t recognise a new version of SQL Server?
” I actually don’t know anybody who uses the built-in debugger ”
time to go back to class Brent
Joe – even the Microsoft Certified Master sessions didn’t involve using the debugger.
I’m not saying it’s not useful to someone, just that I don’t personally know folks using it (other than Andy Leonard, who piped up in Twitter that he had to use it to debug Microsoft’s own SSIS stored procedures.)
More of us would but we cant because of permissions issues in the Enterprise.
Granted I am new to the industry (~5 years), but I have never used a debugger for SQL. I just make modifications to the SQL until it works properly. Don’t get me wrong I use them all the time for actual app code but it really seems unnecessary for SQL.
Notwithstanding this is an excellent blog post and I don’t want to distract from that too much, the fact you are “guessing” on how to make T-SQL work is probably less than ideal. The SSMS debugger is less than perfect, but trust me, I’ve been using it for 10 years and it’s by far the fastest way to identify what the code is doing, period.
I didn’t mean to allude to the fact that I was “guessing”. SQL Server returns error messages, so if something is wrong it lets you know what and where it is. SQL is pretty straight forward. Maybe the problem is that I haven’t had a need for something complex enough to warrant a debugger.
@brenda: I’m in the same boat: I debug SQL procs all the time (daily) in SSMS and I am also GOBSMACKED and this is a deal killer for me. I’m on SSMS 17.9 on Win 8.1 (which is the best version of Windows ever) and I MS will have to take Win 8.1 and SSMS 17.9 from my “cold dead hands”.
In all seriousness, I would like to hear from others that rely on debugging to solve problems every day what they’ll use going forward. Heck, even Oracle’s free PL SQL Developer has debugging. Anyway, with all due respect to Brent I don’t see how I could do my job without debugging SQL on a regular basis. Am I that odd?
Jeff – the short answer is that yes, you’re that odd. That’s okay, though, we’re all freaks around here. Just don’t show me your browser history, that’s all I ask.
OK, I asked for that.
+++ Jeff! I hate SQL diagrams but do have some customers that used them. However, debugging will be sorely missed and puts us back to sybase SQL server on OS/2 1.3 before MS bought it!
@David, my entire team uses debugging daily so I’m already looking for ways to prod MS to put it back, or to find another tool to replace SSMS.
You are right. I don’t use, need, or want to install Visual Studio just to install another tool just to get a debugger. This is a deal killer for me. I need the debugger. The removal of the debugger is strictly a strategic move by M$ to force users to install VS to inflate their # of installs numbers.
Excellent post as always Brent!!
We had the Guatemala SQL Server user group meeting yesterday, and it is a coincidence but I mentioned the same. It is a free tool, and we have to understand that is a piece of software built by humans thus there will be bugs but we can contribute reporting those bugs in GitHub.
Just found a super cool feature in 18 that I don’t think existed before. In SSMS, when you use “Registered Servers” window it shows “Local Server Groups” menu as before. But if you also have ADS installed, there’s another group called “Azure Data Server”. And it shows all of the servers you’ve registered in ADS! So it looks like you can share registered connections between the two applications fairly easily.
I just started working in a shop with Oracle and PostGres (Missing my old pal SQL Server something FIERCE – hi buddy!) and ADS has been a nice addition to the toolkit; especially given that pgadmin was the tool of choice. I really hope Oracle learns to play nice outside their sandbox, and yes I totally get that will never happen. Dammit janet.
I’m with others here, not just a DBA but an API developer. No Debugger is a huge oversight! Many of the systems I support use SQL stored procs as almost a full database layer. Without Tsql debugging I will not upgrade. I wonder who thought we didn’t need it?
>>I wonder who thought we didn’t need it?
It certainly wasn’t a Development DBA that codes complex tSQL. I feel this is a blind spot on Microsoft’s part and its probably driven by a lack of input from coders and too much influence by production dbas that do not dive into TSQL regularly and would never have a need to debug. But what do I know, I’m the red-headed step child Dev DBA here. Ironically, in 2 days I’ll be on the MS campus for SQL Saturday Redmond and I wish I knew the right door to knock on! 😉
The removal of the debugger is strictly a strategic move by M$ to force users to install VS to inflate their # of installs numbers.
One more circle for your Venn diagram of the target audience:
ADS can get me to my Azure SQL DBs through the secondary credential + firewall + VPN + non-trusted domain web of confusion that is my employer’s implementation of network security. The only way I can connect to these via SSMS is by using SQL Authentication, but using those raises a Big Red Audit Flag…
[…] Brent Ozar gives us the lay of the tooling land: […]
Finally a DPI aware version — not blurry anymore!
There are a couple of small bugfixes that it’s quite pleasing to see here, because I think we’d probably all assumed that they would literally never get fixed. I don’t know what’s going on at MS, but I appreciate it:
* The delete key works when renaming in Object Explorer (hey, sometimes I can’t be bothered fighting sp_rename)!
* LAG and LEAD (introduced 2012) are recognised as built-in functions!
My favourite fix on the list by far though is “Flat File Import Wizard: Fixed an issue related to importing of bits when values are 0 or 1”.
Talking of bugs…my manager discovered the bug you’ve reported on the SQL Server feedback forums considering sys.dm_db_stats_(properties|histogram) this morning (sys.dm_db_stats_properties_internal is also affected, but it’s undocumented). That’s pretty scary behaviour and it looks like it’s been around since the DMFs were introduced; you should give it a boost on here! Their may be roll-your-own statistics maintenance solutions out there that are either not maintaining statistics that need maintenance, or unnecessarily maintaining ones that are fine :s
ADS is primitive for sure! I just downloaded it and I was very underwhelmed with it compared to other solutions I use. I wonder why there is no result sorting or filtering. I use a product called universal SQL which has both and allows me to grab a result set and do things to it. Edit it, sort, filter, etc. I was expecting at least some functionality like this. I realize I can “edit” a table which brings the most rudimentary editor I have seen since 2000 era products.
i cannot understand the argumentation of microsoft and brendt absolutely that the debugger is not necessary.
yes it’s true, if you only use “simple” sql then the debugger is unnecessary. but as far as we know there are other logic elements besides sql like the if or a while loop. and it’s exactly for these that a debugger is needed in the analysis of a full-grown programming language.
may be that the debugger is bad (and it really is!), but has anyone ever tried to determine the value of a variable for a function? you can naively say: we do it like in the good old days with a print @var; i wish you much joy and happiness!
simple possibility without debugger –> you build yourself a procedure instead of a function and use print –> i thought these dark times we left behind a long time ago.
so i must actively demand that microsoft completely remove tsql from the sql server from version 2019 onwards, because it is no longer supported with developer tools! please make the downgrade to an ansi compliant database without the possibility of real programming!
I’m not saying it isn’t necessary.
I’m saying it’s gone.
If you find it necessary, then by all means, campaign Microsoft to change that decision. Leaving blog post comments here doesn’t do a damn thing.
This was done a long time ago:
debugger:
https://feedback.azure.com/forums/908035-sql-server/suggestions/35881492-put-debugger-back-into-ssms-18
diagram:
https://feedback.azure.com/forums/908035-sql-server/suggestions/35746747-database-diagram
https://feedback.azure.com/forums/908035-sql-server/suggestions/35526478-don-t-show-diagram-in-microsoft-sql-server-managem
https://feedback.azure.com/forums/908035-sql-server/suggestions/35755180-database-diagrams-on-new-release-18-not-work
unfortunately without success! generally this feedback area at microsoft is a black hole in relation to sql server from my point of view. unfortunately very little or no feedback at all.
what would you suggest, how you can influence microsoft here, so that the toolset corresponds to the product?
Dietmar – well, if someone wanted to get you to do something, how would you propose that they do it? Post on someone’s blog somewhere, or talk to you?
Go find the people who manage the SQL Server tooling, and talk to them. They’re completely open and available on social media. In fact, the last paragraphs of this very post talk about how to get support for these tools.
That portal is official place to send suggestions! But it seems only SCCM team makes the changes according to UserVoice input
ha ha ha. Microsoft has now discontinued the Feedback site. Each of the links above go nowhere.
while I rarely used the debugger… TODAY… I discover that it’s gone and really need it. And it isn’t in Azure Studio other. Attempting to install SSDT for VS.2017 is a circular loop of Nope.
“Thanks for your interest in providing feedback on Azure products and services. We value your opinion. We are evaluating enhancements and standardizations to improve and streamline how we communicate with customers and collect their feedback.”
Grab a copy of 17.9.1 here and install it stand-alone. Can happily run side-by side with other versions of SSMS.
https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791
I’m amazed at all the posters on here complaining about how desperately they need a TSQL debugger, when all they have ever used is the free and totally underwhelming debugger in SSMS, instead of something that works much better for these ‘highly critical’ development needs.
Cost isn’t an excuse. SQL Enterprise is 7 thousand dollars per core, per year.
Someone on the twitters pointed out that Visual Studio still has a debugger built in. Solid point if the need is dire.
EXACTLY. I dont use it much, but it seems like the one in SSDT also works better. I very rarely use the debugger – i’m not a developer, most of the scripts I write are simpler to just look at than mess with the crappy debugger.
lol its like the management part in SQL Server Management Studio went unnoticed that its primary function is for management.
Keith, with all due respect I don’t think anyone is stating cost as an excuse. I have 10K worth of development tools and licenses I maintain easily. I also have to use what clients maintain. I do not always have the ability to load a third party tool onto a server or highly secure machine in order to trace through a production problem. Having it present in SSMS meant it was loaded on SQL servers I needed it on without certification or exemptions.
I also feel your term “free” is debatable, but “included” is more the term I would use. How about that free ABS braking system in a new car, or “standard equipment” that is what determines the base price of the car? Further, it was decent, I don’t see it as underwhelming, it recently demonstrated to a client that triggers were doubling up on work from a long running stored procedure. Without a debug session it would have been very hard to see this for this set of data.
Lastly, given you are citing that the free and totally underwhelming debugger need not be distributed anymore, what paid product do you recommend?
You should NEVER run SSMS on a SQL box. This is bad on so many different levels. If there is some sort of connectivity problem where you cant get into it remotely, more than likely you’ll be needing to connect with SQLCMD anyways.
SSMS is free. It is an entirely separate product from SQL. It doesnt matter if it is developer, express, standard, enterprise, etc, its a separate, free download that is developed on an entirely separate track from SQL.
Paid alternatives: ApexSQL. I’ve played with a few demos and really liked their tools, and it is cheap. about 300 dollars per license.
Visual Studio. There is also the express version of visual studio that is still free. This is primarily what I use, but most of the sprocs I write are simple enough to not need a debugger.
There are a couple third-party SSMS plugins that I dont remember at the moment.
Toad, which I believe is cross platform.
I believe even RedGate has a debugger now.
I support software that is running on several hundred SQL Servers that belong to our various clients. Depending on the situation, if some tsql code is failing I can use SSMS to debug on the client’s server with their data, their sql version, etc and find the offending code. I am not able to install any software on the client’s SQL server. So SSMS, as lightweight as it might be, is good for this scenario. (And yes I know debugging on a production box is not ideal but there are times when it is justified.)
It seems to also be missing the Data Import and Export tool/functionality so has no way to import from .csv, Excel or Acess unless I’m missing something obvious which is also possible! Surely they haven’t removed that too? First before SSMS 17 there was a x64 and a x86 version then for some reason in 17 they killed the x64 version and then in SSMS 18 they seem to have killed both of them. I regularly need to import data and as a lot is ad-hoc I hope I am not going to have to write an SSIS task each time 😮
Just thought I should add a response here that whilst the Import/Export tools are missing from the start menu, you can right-click a database and select to import from there whilst still works although it is still only 32bit.
I really wish they would add Hive support to Azure Data Studio, and I don’t mean HDInsights.
Be advised that MS re-added back Database Diagrams in SSMS v18.1. Not sure about debugging though. https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017
Interesting read….
But as as a user who a) uses windows, and b) has to tune queries… (as noted) – yes, i’m not back in SSMS 17.9!
Why?
Because, SSMS 18 also dropped TFS plugin ability – no warning? no hint of deprecating? Just GONE!
I’m a Business Analyst/DBA and don’t use VS – but i need to write SQL code, and manage versions etc etc, and now consider TFS essential.
Thoughts on the demise of the “TFS” access within SSMS 18?
I’m having the same situation in my department. Are there other 3rd party plugins out there that integrates SSMS and TFS? We had a lot of SQL project solutions in TFS that we managed through SSMS and now that integration is gone. Now I have to Have TFS Source Control Explorer open to to manage the TFS side and then SSMS open to use the solution. What a step back,
Removing debugger is a complete show-stopper for me
Switching between SSMS and Visual Studio for debugging all the time?
Thanks microsoft, for this great “improvement”, caring for DBAs priductivity, and most of all, caring for third-party suppliers of debugger tools 😀
Hi,
fact is, that the only debugger for the MS SQL Server is for the moment the older SSMS 17.9.1
http://www.catalog.update.microsoft.com/Search.aspx?q=sql%20server%20management%20studio
and there search for SQL Server Management Studio – and there download the older 17.9.1 – it works parallel to V 18.3 – so problem “solved” …
The Vis Studio 2019 Ent V 16.3.1 with ssdt plain crashes, if you try to debug
2019-09-28
i just twittered the following:
I am absolutely sure, that the decision, to delete the Debugger for T-SQL Stored Procs in SSMS will enhance the security and stability of custom built MS SQL Server programs a lot. 🙁
Congratulations, you won the Golden Raspberry Award Microsoft #SQLServer
mfg Klaus Oberdalhoff
“Don’t need to tune those queries to make them go faster”
The engineer in me died a little upon reading that. Every line of code written needs to have performance in mind.
Every line, eh? May wanna check with your web server first. I tried opening the URL in your profile and it timed out after 30 seconds. O_O
I just updated to management studio 18 and was agog that the debugger was removed. I did not move up to 18 before because of a communication issue with SSIS and 18. but now that I have some sql server 19s I need to have ssms 18. I guess I will run them both concurrently, as banal as that is.
I wonder, Brent, after more than a year, are you using ADS even more for the SQL Server work? I keep trying and I keep struggling, but I’m getting there. It’s getting more and more useful, especially since they’ve added the PowerShell extension and a few others.
Quick rundown of my use cases:
* Working w/Postgres: ADS
* Working on the First Responder Kit: ADS (because I can work in my Mac w/o starting a VM, and query an RDS back end)
* Teaching people about SQL Server: SSMS
* Writing demos about SQL Server: SSMS (because I grab screenshots of query plans, and I need those in SSMS for the details)
* Working w/clients while screen sharing: SSMS