A Fine Slice Of SQL Server
Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.
Read on to learn more.
Comments closedKenneth Fisher shows off templating in SQL Server Management Studio:
Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from this team each have their own database that they can use as a type of scratch pad and I guess they’ve been hiring. It’s simple enough to create the database and then grant the necessary permissions, but let’s face it, after the first time I was already tired of the GUI and scripted the process out. Running the script was better but I quickly became annoyed at having to make changes to the script. User names etc. So I decided to create a more permanent script. My first thought was to use dynamic SQL. While that would work, and I’m certainly comfortable with dynamic SQL, it just didn’t feel right for this. I decided in the end to use a trick from templates. If you set up parameter(s) in the file you can use Ctrl-Shift-M to scroll through them and make changes. In an odd twist these are called template parameters.
Click through for a demo.
Comments closedThomas LaRock notes an oddity in SQL Server Management Studio and Azure Data Studio:
Anyway, I spend time trying to debug what is happening. I am able to manually set NOCOUNT on and off inside of T-SQL and see a count of rows affected returned (or not). I check and recheck everything I can think of and feel as if I have lost my mind. I’m starting to question how I ever became certified in SQL Server.
I mean, it’s a simple configuration change. This isn’t rocket surgery.
So I do what anyone else in this situation would do.
I turn off my laptop and forget about everything for a few days.
I’d never used this particular style of setting NOCOUNT on for a user (I would always enable it by session using SET NOCOUNT ON), so I’m not sure when certain tools started ignoring the user-level setting, but read the whole thing for maximum intrigue.
Deborah Melkin contrasts SQL Server Management Studio with Azure Data Studio:
Honestly, the vast majority of my time is split between Management Studio (SSMS) or Azure Data Studio. I’m pretty simple\straightforward this way. I started playing a lot more with Azure Data Studio over the past year, but I find I’m not able to make the switch to using it full time. It really depends on the task that I need to do.
So what tasks do I do often and which tool do I use?
The plus side for Azure Data Studio is that it’s far enough along that some of these choices are difficult to make. The minus side is that it’s still often on the losing end. I’d expect that shift to continue over the next couple of years as the product matures and becomes a good product for database developers.
Comments closedDrew Skwiers-Koballa gives us an update on where SQL Server tooling is at:
The December releases of Azure Data Studio 1.25 and SQL Server Management Studio (SSMS) 18.8 are now generally available. Additionally, the mssql extension for Visual Studio Code has recently been updated to version 1.10.0. Read on to learn more about each of these updates and grab the latest versions of SSMS, Azure Data Studio, or the mssql extension for VS Code.
Read on to learn more.
Comments closedDave Mason follows up on yesterday’s post:
I have a number of scripts and queries I’ve written and curated over the years. They’re not organized that well, scattered across different folders. Some are named poorly or grouped in a questionable manner. There are a handful that I tend to use the most. And yet with that small number, I sometimes have difficulty quickly finding a particular script (if I can find it at all), or spending too many mouse clicks to find it. It dawned on me recently to make use of code snippets.
Code snippets may have been intended primarily to aid code writing, but they can assist with administrative tasks too.
These can provide a considerable benefit for data platform specialists.
Comments closedDave Mason uses an external memory:
There are certain T-SQL statements whose syntax I have trouble remembering. When those situations arise, I might look up the syntax online; find the same type of object in SSMS, right-click it, script out the object, and use that as a starting point; or find one of my own scripts that has the syntax I’m looking for. Another option that I often overlook is T-SQL code snippets.
Click through to see how to use code snippets in SQL Server Management Studio. You can also create your own as well.
Comments closedGlenn Berry takes us through the latest edition of SQL Server Management Studio:
One big change with SSMS 18.7 is described by Microsoft this way:
“Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio. Azure Data Studio is a cross-platform and open-source desktop tool for your environments, whether in the cloud, on-premises, or hybrid.“
So far, this has been a pretty controversial change. Erik Darling created a User Voice suggestion on October 20th that has already gotten over 234 votes, and many comments.
I’m not going to weigh in too much here, though I would prefer this to be an optional installation. Do watch out for an annoyance, though, if you have Azure Data Studio installed as a User instead of System.
Comments closedAndy Leonard clues me in on a new connection type:
SQL Server Management Studio – SSMS – supports viewing the contents of Azure Blob Storage containers.
I’m still going to use Azure Storage Explorer for all of my Blob Storage heavy lifting, but it’s nice to see that if I just want to see a listing during a presentation or something, I can.
Comments closed1. Open the “New Session…” GUI for extended events in SSMS
2. Give it a name, it’s not important
3. Add an event with a Duration Field, I’ve added sp_statement_completed and configure it to be higher than an arbitrary value
And then watch it go boom in SSMS 18.7. Click through for a demo of the issue and a workaround.
Comments closed