In Memoriam: Azure Data Studio

Recently, Microsoft announced that they will be sunsetting Azure Data Studio (ADS). 

In other words, ADS is off to Software Valhalla, where it will join Query Analyzer and SQL Enterprise Manager. (And SQL Server for Itanium and SQL Server for Alpha and Multiplan and Professional Development System and QuickBasic and QuickC and many, many other things (Microsoft or not) that exist only as memories.)

This announcement was a bummer, but I was surprised at my lack of surprise. It was more of a "yeah, I guess that makes sense" rather than a "oh, wait-what?" or "I thought they were going to get rid of SSMS". Remember when Microsoft helpfully (?) installed ADS when you installed SSMS?

I've been using SSMS since SQL Server 2005 When I started working with SQL Server, I used Query Analyzer because that is what was available. Which means that I used Query Analyzer for about six or seven years. How could it have been that long? No one remembers good old QA anymore-it was more responsive and less complicated than SSMS.

I liked Azure Data Studio. In the last five years, I've probably spent more time with it than any other application, other than a web browser. I used ADS as my daily driver for all of that time. I spent thousands of hours with it and wrote many thousands (tens of thousands?) of lines of SQL code with it.  

More soberly, I've been installing ADS alongside:

• Visual Studio (because I need that for some stuff)
• Visual Studio Code (because I need that for other stuff (like PowerShell) and things like CSV files (because I'd have to use NotePad++ if I not for VSC extensions)
• SQL Server Managment Studio (because I need that for even more other stuff)

My favorite ADS features, in no particular order, were:

• Git support. I have actually used old-school TFS with VS and SSMS, years and years ago. 
• Easy theming, including font choice, sizing and dark mode.
• More rational options dialog-I feel like I can actually find stuff-stuff that might be in SSMS but I'll never see it.
• Sharing of profiles between copies of ADS installed on different computers. This predated similar support for VSC, IIRC.
• Ctrl+ and Ctrl- for easy scaling changes
• Easy search for files (IOW: Ctrl-P)
• Wildcard expansion for "SELECT *", which was actually the thing that pulled me into ADS to start with.

The things I was not a fan of were:

• The never-seemed-ready-for-prime-time extension market. I feel that easy installation of extensions, like what was available in VSC, is key to adoption of things like extensions. Ask me about my (long-retired) Nokia E71 and Java apps. Another take: How many SSMS extensions have you installed?
• I did not like accidentally starting PowerShell sessions and opening PS1 files in ADS, when what I wanted was to use the debugger in VSC.
• I did not feel that I could uninstall SSMS and not miss it. For "fiddly things", like digging into SQL Agent jobs, digging into database properties or query plans, I never felt as confident with VSC as I did with SSMS.
• The SQL Project extension always feels weird-especially the Publish feature-when I compare it to how VS SSDT does the same thing. By "weird" I mean "Am I doing this the right way or am I about to nuke production?" I haven't really tried this in VSC yet.
• I did not like having to keep ADS and VSC up to date, on two or three different workstations, with frequent updates that don't get taken care of with the "regular" Windows updates. I feel like I spent one or two hours a month just updating "stuff" and I'm always concerned about what might have broken by an update. 

In retrospect, I don't think I ever understood why ADS had to be forked off of VSC. ADS never seemed to get the kind of love that VSC (directly) got, particularly in the area of extensions. 

What I'm working on now

I hope that you are all safe in this time of COVID.

I am (slowly) working through the "Automate the Boring Things (with Python)" class over at Udemy.

I am keeping my code/notes/experiments in an open GitHub repository. You can read my raw files and see how I think when I write programs in a language I am not at all familiar with.

I have also been (slowly) updating my old "Tools I use" wiki at github. This includes things like programmer's editors and some of the languages I have used over the decades.

News Flash: Old dog learns new tricks (or tries to)

Lately, I have been trying to up my game a bit and one of the things I am doing is using some of the latest tools and bits of things.

(Partly, this is because I have been working with some old technology lately and I am feeling "dated". Ask me about Fox Pro, if you dare.)

PowerShell
After many years of not seeing value in updating, I am moving to PowerShell 6. (Version 6.2, specifically.) Frankly, more things work than I expected. I haven't seen any performance regressions and things have gone smoothly. I think I spent more time incorporating the PowerShell version into my Prompt function than anything else that I had to do because of the upgrade.

SSMS
I am running the absolutely-latest build of SSMS. I skipped some SSMS builds when the removed the diagramming functionality. Now that it has been put back into the product, I am on the update train again.

Aside: The diagramming feature isn't awesome but it is easy to use, requires no installation, doesn't cost anything and is more than adequate for 95% of the things I need such a tool for. I know that it isn't a real "enterprisey" ERD tool, but I don't need one. In fact, if they took out all of the "modify the tables in the database" features, I would like it more. The main thing is that, sometimes, you just want to look at a diagram of a subset of the tables in a database.

Windows 10 1903 Update
After not getting any update action for weeks after it was released, I bit the bullet and forced this latest Windows release onto my main laptop. (Carpe Diem, right?) It's been fine so far. The blurred-out login display was a little jarring at first. Multi-monitor has always been the shakiest feature of my laptop, but it has not been any worse after updating.

DbaTools
After several years of "beta", DbaTools has had it's first release that is judged as production-ready. I've updated and I'm slowly looking through my code for issues. I've been using this as a production tool for years. I'm excited for this release because it validates all of the work that has been done on the tool and the names of functions should stop jumping around so much.

Learnings
I spent a lot of time watching things on YouTube (or the SQL PASS site) in order to learn new things and see how other people approach the problems that I probably have. Lately, I have been focusing on the PowerShell + DevOps Global Summit 2019 videos.

Just For Fun
I am trying out the new "Windows Terminal", which is in preview and available in the Windows Store. I found this through a post on Hanselman's site. It found my Windows Subsystem for Linux (WSL) and gave me bash, it found my new and old PowerShell installations and my old, trusty cmd shell (which I still sometimes use for chocolatey). I haven't put it into heavy use yet, but nearly everything seems OK so far. (The settings menu pick doesn't seem to be working properly for me.)

DbaTools version 1.0 has been released

(Actually, they are up to version 1.0.2 already.)

I've been using DbaTools for a number of years. I have come to rely on it more and more. Now, I find it at least as deeply entwined in my PowerShell code as the SqlServer module is.

All right-thinking people use DbaTools. And you should, too.

More seriously, DbaTools is one of the few PowerShell modules I absolutely rely on, on a daily basis. I use DbaTools so frequently that I can't easily categorize it all. In short, I can:

• Check servers, instances, databases, disks, security and many other things for issues on a daily basis
• Check business rules on a daily basis
• Restore databases to test environments on an ad-hoc basis. I have this working so smoothly that I forget that I have Ola Hallengren's backup scripts actually running the database backups. 
• Load data into databases
• Extract data from databases

I've never actually used DbaTools for it's original purpose, which was migrating databases and instances onto new computers. It can still do that, but it has added so much more goodness over the years that you should install it anyway. There really is something for everyone in DbaTools.

If you want to see a cool ten-minute, "lightning" demo of how you can use PowerShell and Excel together to analyze data or produce nice reports, watch this YouTube video, from 7:47 to 18:22. This is presented by Doug Finke, the author of the Import-Excel PowerShell module.

What I did for the week ending 2018/08/24

The highlights for this week have been a few videos from the free-to-view PASS Summit 2016 archive, available at PASS.org.

Managing SaaS Application Databases with Elastic Jobs: SQL Agent and More for Azure SQL Database, presented by Deborah Dove

• Essentially, "Elastic Jobs" is SQL Agent for Azure SQL. She also talks about "Elastic Query", which allows you to run queries on all of your databases, or a specific subset of them.

Azure Data Services: Spotlight on Azure SQL Database, presented by Debora Dove

• I think that this is a good introduction to Azure SQL and it covers Elastic Pools. I'm sure that I learned a few things. One thing that bothers ms is the low query/minute ceilings for the various offerings.

Design Patterns for SaaS Applications with Azure SQL Database, presented by Bill Gibson

• If you are going multi-tenant, sharding and employing Elastic Pools seems to be the only sane choice.
• "Most of our customers are compute-bound, not storage-bound."
• There are various clever things you can do with pools and tagging.

What I did for the week ending 2018/08/17

The summer has been busy and my Azure training schedule has suffered. Here are a few highlights from the last few weeks of study.

The following are presentations that occurred during SQLPASS Summit 2016. I can't  link to these presentations directly because they are behind a paywall (though it is free to subscribe):

Backup and Restore SQL Server Databases in Microsoft Azure, presented by Andy McDermid and Pinal Dave

This presentation is about Virtual Machines, not SQL Azure. My takeaways were:

• Segregating data, log, tempdb and backup locations is still recommended practice.
• The "backup to URL" feature can be useful because frees up a disk because it is not needed to hold backup files and writing to a URL doesn't count as bandwidth usage against your VM disks. "Backup to URL" can stripe the data just like writing "regular files" can. You need to ensure that the costs associated with the (required) Azure Storage Account make sense.
• You can stripe VM disks for performance, but remember that VM sizing has rate-limits on the disk performance. If striping provides capacity that exceeds those rate-limits, you won't get the performance benefit that you expect.

Virtual SQL Servers. Actual Performance, presented by David Klee

Klee is always worth listening to, though his client's systems are much bigger than anything I've seen. My takeaways were:

• Your virtual sockets should look like your physical sockets. Put everything inside of a NUMA node, if you can.
• Always consider: "What if my VM gets moved to a host with a different processor configuration?"
• Paraphrased: "Changed from 1x16 (socket x core) to 2x8 and the performance went up 25%"
• Paraphrased: "A client had every VM set to 64 vCPU. Changing from 64 vCPUs to 4 vCPUs got a throughput 3.5 times higher than anything the client had seen up to that point".
• Turn off "hot-add CPU" because it disables proper NUMA configuration.
• Hypervisors generally ignore hyper-thread "cores" until the server is really loaded up. That level of load should not happen often. Therefore, there is little use in turning HT off in the BIOS.
• If your storage does compression, rely on that and turn off SQL Server's data compression.
• Virtual Disk Controllers (VDCs) are still a problem. Creating additional (non-default) VDCs and spreading your IO load around is recommended.
• As always, test any change to make sure that you get what you expect.

SQL Server in Azure VM: Best Practices, Latest Features, and Roadmap, presented by Luis Vargas

and 

SQL Server High Availability & Disaster Recovery in Windows Azure, presented by Lous Vargas, Sanjay Misra, et al

Both of these were very good, with some overlap. I liked the first one so much (which Vargas presents "solo"), I watched the second one when I noticed that he was one of the presenters. My takeaways were:

• You are better off using storage pools, not MDF/NDF files, for striping and performance.
• Licensing seems to only get more complex as time goes by.