#119 When the backups check module reports backup issues for a database but the issue is with a FULL or DIFF and the LOG is ok, we now show just the primary server in the Preferred replicas column as a FULL and DIFF only applies to the Primary – this reduces the number of warnings raised within the report as it will no longer report for all replica nodes if the AG backup preference is set to Prefer secondary or Secondary Only. See Git issue for more details.
Click through for the full change set.
We’ve got about 30 issues left to resolve which you can see and follow on our GitHub Projects page. If you’ve ever been interested in helping, now is the perfect time as we only have 30 more days left to reach our goal.
If you’re a current or past dbatools developer, we’d love any help we can get. Just hit up the GitHub Projects page to see what issues are left to resolve. If someone is already assigned, please reach out to them on Slack in the #dbatools-dev channel and see if they can use your help.
Read the whole thing and see if there’s anything you can do to help.
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.
Yeah, Management Studio is the best of the bunch. I’m using Azure Data Studio more at home but still need a couple of plugins to use it often at work. And those two beat pretty much every other tool I’ve ever worked with.
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.
There’s some good information in here for sqlcmd and for SQL Server Management Studio.
We have all been using SQL Server Management Studio to query and manipulate data, even for an Azure SQL database. There is also an option to do this same thing built into the SQL Azure database interface in the Azure portal. Although there have been a number of posts related to this topic dating back a few years, this feature is still marked as “preview” in the Azure portal.
Click through to see how it works, what you can do with it, and some of its limitations.
Last week I showed you how to use WorkloadTools to analyze a workload. As you have seen, using SqlWorkload to extract performance data from your workload is extremely easy and it just takes a few keystrokes in your favorite text editor to craft the perfect .json configuration file.
Today I’m going to show you how to capture a workload and save it to a file. If you’ve ever tried to perform this task with any other traditional benchmarking tool, like RML Utilities or Distributed Replay, your palms are probably sweaty already, but fear not: no complicated traces to set up, no hypertrophic scripts to create extended events captures. WorkloadTools makes it as easy as it can get.
Saving a workload to a file might look superfluous when you think that WorkloadTools has the ability to perform replays in real-time (I’ll discuss this feature in a future post), but there are situations when you want to replay the same exact workload multiple times, maybe changing something in the target database between each benchmark to see precisely what performance looks like under different conditions.
Gianluca’s technique does seem a lot less fussy than the Microsoft techniques.
Now that the analysis database contains the performance data, you can use WorkloadViewer to visualize it and draw your conclusions.
WorkloadViewer is a GUI tool that reads performance data from the analysis database and gives a graphical representation using charts and grids. It accepts a number of command line arguments that allow to automate its behavior, but it can be also opened without specifying any arguments: in this case, WorkloadViewer will present a form to fill the missing information.
WorkloadViewer can be used to visualize information about a single benchmark (analysis mode) or two benchmarks (comparison mode). In this case, you just need to work with a single benchmark, so it is enough to enter the connection info on the left, including the schema name where the tables are. When using Windows Authentication, you can leave UserName and Password blank.
Gianluca has a full demo from the beginning of data capture to analysis.
/* Begin Brief Soapbox*/
Honestly, this is by far one of my biggest grips about Open Source software now that I’m older, busier, and don’t want to spin my wheels trying to make something simple work. When the tools make it hard to dig in and work effectively with the database, most developers and shops will default to code-first/ORM only development. In nearly 20 years of software development and leading multiple teams, I’m still surprised how little most developers really care about effectively using a database of any kind. During most interviews only about 30% of applicants can ever answer a few basic SQL questions. And now I think I’m starting to understand why. Most of them have been relegated to an Open Source world with Open Source tooling when it comes to SQL. Yes, it’s cheap and allows projects to spin up quickly, but once those students get past their little pizza ordering app from CompSci 402, they’ll be lost in the real world.
/* End Brief Soapbox */
I completely agree with the tooling point. Having worked with Postgres and MySQL a little bit makes me appreciate Management Studio (for all its flaws) all the more. If you want Azure Data Studio to support Postgres, there’s a GitHub issue that you can vote up.
Throughout my career, I had to go through the pain of benchmarking often enough to get fed up with all the existing tools and decide to code my own. The result of this endeavor is WorkloadTools: a collection of tools to collect, analyze and replay SQL Server workloads, on premises and in the cloud.
At the moment, the project includes 3 tools:
– SqlWorkload – a command line tool to capture, replay and analyze a workload
– ConvertWorkload – a command line tool to convert existing workloads (traces and extended events) to the format used by SqlWorkload
– WorkloadViewer – a GUI tool to visualize and analyze workload data
Click through for the link to check it out.
There are actually about 40 things it checks for.
Current limitations are that queries with a cursor or temp table are not analyzed. There’s also a bug where the missing indexes and warnings appear on the wrong node/operator. Since the tool is using estimated plans at the moment, it may not be as accurate.
I’m planning on a few new features in the next month to add feeding the utility a query plan and displaying the original query. I’m also planning on adding history and the ability to execute a query from the tool. Before we get to those we need to fix some known bugs though. I’m hoping that you. Yes! you can help me identify other bugs to make this a great tool for the SQL community.
The product is in beta, so check it out and send Daniel some feedback.