Press "Enter" to skip to content

Category: Tools

Large-Scale Database Schema Comaprison

Published 2021-09-01 by Kevin Feasel

Raul Gonzalez creates a process to compare database schemas across a large number of servers:

There are tools that can compare two databases in great detail, but the caveat I found, it is that they can only compare two databases, so while they’ll be fine to compare DEV<=>PROD or QA<=>PROD, they’re not a good fit to compare and find the differences in, literally, thousands of databases

There it’s where I was, so after some time trying to postpone the inevitable, it was time to roll up my sleeves and get the job done.

Click through for Raul’s explanation and process. At one point, I’d written something which used Red Gate’s Schema Compare DLL to do something similar. The big problem you run into is when there is no single, canonically correct database and you’re trying to generate one from the tangled mess of objects.

Comments closed

SQLCMD and the Question Mark

Published 2021-09-01 by Kevin Feasel

Solomon Rutzky stops SQLCMD in its tracks:

This is the story of one such time that I stumbled upon something interesting, and all because I made a mistake. I was doing testing for the previous post (mentioned above) and somehow entered an extra character, or forgot the closing quote on a string literal, or something. Either way, I submitted some T-SQL in the sqlcmd utility that should have produced a parsing error. But instead, it cancelled the entire query batch, and without returning any errors.

Read on to learn more and maybe have a little fun with co-workers.

Comments closed

Search and Destroy with RegEx: Visual Studio Code Edition

Published 2021-09-01 by Kevin Feasel

Andy Levy unleashes the power of regular expressions in Visual Studio Code:

Kind of a diversion into a Visual Studio Code/Azure Data Studio tip here (this works in both, as well as SQL Server Management Studio). I’m not a regular expression guru – far from it. I use them occasionally, but usually find myself fumbling around for a bit trying to figure out just the right expression to do what I need.

I’ve known for a while that VSCode/ADS had regular expression matching built into its find/replace feature, but did you know you can also replace with it? It can help remove the tedium of doing a large amount of text processing.

It’s quite a bit different from the regular expression parser in SQL Server Management Studio, but the support of capture groups in VS Code is quite helpful, as Andy demonstrates.

Comments closed

Query Waits in the QDS Toolbox

Published 2021-08-24 by Kevin Feasel

Jared Poche continues a series on QDS Toolbox:

Carrying on from my previous post on the QDS Toolbox, let’s review the Query Waits component.

This component provides details about the wait types associated with a given object, query, or plan. This doesn’t pull data from the reports generated by the Server Top Queries component; this gets the data directly from the Query Store in the database you specify. So you can use this to review the waits on a given procedure, even if you haven’t generated any reports with Server Top Queries .

That being said, reports from Server Top Queries could identify problem queries and give you the PlanID, QueryID, or ObjectName you need to run the procedure. A query that spends a lot of time waiting would tend to have a higher duration and lower active metrics like CPU usage or logical reads. So those are the queries I would want to run the Query Waits report against.

Read on to see how to call this procedure and what you might expect.

Comments closed

Power BI Cleaner Gen2

Published 2021-08-24 by Kevin Feasel

Imke Feldmann has an update to the Power BI Cleaner:

In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.

Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.

Read on to see how to set everything up, as well as the things you can do with it.

Comments closed

Power BI Push Tools

Published 2021-08-19 by Kevin Feasel

Marco Russo announces a new set of tools:

Power BI Push Tools is a set of open-source tools to work with Power BI push datasets.

Read Implementing real-time updates in Power BI using push datasets instead of DirectQuery to learn the architecture of solutions based on push datasets and how to create and populate a push dataset starting from a regular Tabular model.

In addition to reading that linked post, click through to see the three tools which are currently available.

Comments closed

Fun with GO and Preventing SQL Batches from Parsing

Published 2021-08-16 by Kevin Feasel

Solomon Rutzky has an apropos post for Friday the 13th:

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.

Comments closed

Helpful Tools for Apache Kafka Developers

Published 2021-08-06 by Kevin Feasel

Dave Klein has a few tools to make working with Apache Kafka a little easier:

We like to save the best for last, but this tool is too good to wait. So, we’ll start off by covering kafkacat.

kafkacat is a fast and flexible command line Kafka producer, consumer, and more. Magnus Edenhill, the author of the librdkafka C/C++ library for Kafka, developed it. kafkacat is great for quickly producing and consuming data to and from a topic. In fact, the same command will do both, depending on the context. Check this out:

Read on for more information on this tool, as well as several others.

Comments closed

A Wish List for SQL Server Monitoring

Published 2021-07-16 by Kevin Feasel

Chris Shaw lays out some of the problems with monitoring systems today:

A next-generation monitoring tool should not just provide the same standard old dashboard dressed up with new fancy graphs; it should empower me to be actionable. It needs to help me improve the environment and show me the impacts of those actions on the system, and thus, the business.

What the industry needs (for risk of rendering the title of this article bunk) is not another monitoring tool. I have tools that help me monitor and they do a fine job of specifically doing that.  I need a tool that will take me into the future, I need a tool that makes me better and faster at what I do.  This industry needs a smarter tool.

Click through to see what Chris has in mind, though the reason you haven’t seen some of this stuff is that it’s a couple orders of magnitude more difficult than what we do see in monitoring solutions. H/T Amanda White.

Comments closed