Press "Enter" to skip to content

Category: Tools

Building Diagrams in Mermaid

Published 2023-10-23 by Kevin Feasel

Michael Bourgon tries out Mermaid:

Just found out about this the past month. 

I like diagrams for my documentation, and I detest making it. I also would like to build it via script, since that’s more useful.

I used Mermaid to create a series of architectural diagrams a couple years back. It was a reasonably good experience, although you have to keep in mind that you don’t get pixel-perfect designs and certain concepts can be difficult to represent. Even so, it’s quite alright for straightforward diagrams and includes support for icon sets for a variety of cloud and on-premises environments.

Comments closed

Monitoring Power BI Gateways with Microsoft Fabric

Published 2023-10-18 by Kevin Feasel

Tom Martens builds a solution:

No matter what, when the on-premises gateways are not working as expected, data will not refresh, and direct query queries will not succeed. For this reason, I consider it a good idea to track the well-being of these valuable resources. This article describes a solution built with Microsoft Fabric. It’s not necessary to use Fabric, and it’s also not necessary to build a solution on your own. If you want to track the well-being of your on-premises data gateways but do not want to build something, I recommend using the solution by Rui Romano you can find here: https://github.com/RuiRomano/pbigtwmonitor

I built this monitoring solution focusing on the well-being of the on-premises data gateway. I might extend this solution in the future, but for now, it’s about the availability of the on-premises data gateway and the data gateway connections. Availability and analysis will follow during the next weeks.

Click through for Tom’s solution.

Comments closed

A Critique (and Defense) of Generic Programming Languages for ETL/ELT

Published 2023-10-17 by Kevin Feasel

Teo Lachev doesn’t like general programming languages for ETL and ELT operations:

Someone asked the other day for my opinion about the open-source dbt tool for ETL. I hadn’t heard about it. Next thing I’ve noticed was that Fabric Warehouse added support for it, so I got inspired to take a first look. Seems like an ELT-oriented tool. Good, I’m a big fan of the ELT pattern whose virtues I extolled I discussed many times here. But a Python-based tool that requires writing custom code for orchestration in a dev environment, such as Visual Studio Code? Yuck!

My reasoning is simple: complexity. Bespoke ETL/ELT tools like SQL Server Information Services, Informatica, Azure Data Factory, Airflow, and the like are good when you fit into their primary use cases: moving data from a few data sources into a destination, perhaps with some level of transformation in between.

But here are areas off the top of my head where I’ve seen these tools not work well:

  • Wide scale. In one environment, we had to move contents from a couple thousand databases (with identical schemas) across 50-60 instances of SQL Server into a warehouse, including some facts and dimensions we needed within a minute or two. Even assuming those packages don’t change frequently—not a reasonable assumption—the pains of orchestrating that would be enormous. I don’t think we could have used metadata-driven approach and foreach loops in your ADF workflows, either, as that would not satisfy the time requirements. There are also resource limitation requirements on the other side—you don’t want to overwhelm the warehouse by trying to process a couple thousand clients’ worth of data all at once, so you’ve got to stagger this work using an orchestration engine with enough smarts to limit concurrent processes.
  • Limiting copy-paste efforts and drudgery. Going back to SSIS, it sucks having to maintain dozens of packages, especially common components you need to update in each one. I got to be pretty good at Biml, but a) that has its limits, and b) that’s C# development with SSIS packages as an output, so I’m claiming that for the generic programming languages side of the argument.
Comments closed

Connection Pooling in Postgres

Published 2023-10-13 by Kevin Feasel

Semab Tariq shows off a tool for Postgres:

PgBouncer is a lightweight yet powerful connection pooling tool for PostgreSQL. It efficiently manages and reuses database connections, reducing the load on the server and improving performance. It acts as an intermediary between applications and the PostgreSQL database, optimizing connection usage and enhancing scalability.

This is a bit different from SQL Server, where connection pooling is built in. Read on to see how it works.

Comments closed

Updates to Power BI Field Finder

Published 2023-10-13 by Kevin Feasel

Stephanie Bruno has an update for us:

The Power BI Field Finder is a standalone .pbix file you can download and hook up to your reports and data model to. The Field Finder helps you visually analyze where fields are used in reports.

I’ve used this to great effect on a prior project where I had to figure out what was going on in a report with about 20-25 pages that other people had put together.

Comments closed

A Review of DataVeil for SQL Server Users

Published 2023-09-20 by Kevin Feasel

Brian Kelley tries out a product:

My organization typically moves production data to other environments. There are a variety of use cases:

  • Testing with the amount and frequency of production data.
  • Performing analytics on said data.
  • Delivering production-like data to a third party for their use.

We do not want to move production data around. Instead, we want to deliver “production-like” data for these use cases. Sometimes, we work with multiple systems integrated with each other, and in those cases, we need the data to match up. In other instances, we need the sensitive data, such as personal identifiable information (PII), to be altered so it’s no longer sensitive, but there’s no requirement for it to be consistent across systems.

Read on for Brian’s full review. I should also note that this is most definitely a paid product.

Comments closed

Verifying a Backup in SQL Server

Published 2023-09-20 by Kevin Feasel

Chad Callihan knows your last backup is only as good as your last restore:

Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.

There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.

Click through to learn more about this. And you could easily put together Powershell scripts to stagger your restorations over a time frame (such as, 15% of your databases each day, so that you get to 100% by the end of the week).

Comments closed

The Joy of sp_HumanEvents

Published 2023-09-13 by Kevin Feasel

Erik Darling makes a pitch:

While my relationship with Extended Events is complicated for many reasons:

  • Awful documentation
  • Hardly any guidance on usage
  • Almost nothing useful about what type of target to use when
  • Everything stored in XML
  • Slow, unfriendly GUI in SSMS

My need to use them while consulting outweighs my gripes and grievances about how Microsoft has chosen to write about, use, and present the data to you.

That’s where my stored procedure sp_HumanEvents comes in handy.

In fairness, Erik put his virtual money where his virtual mouth is, and sp_HumanEvents is put together quite well.

Comments closed

Thoughts on Third-Party Power BI Tools

Published 2023-09-11 by Kevin Feasel

Chris Webb shares some thoughts:

Rather than blog about the tool itself – there’s no point repeating Nikola’s post – I thought it would be good to answer a question someone asked me later that day about Tabular Editor and which I’m definitely going to be asked about DAX Optimizer, namely:

This looks great, but it’s expensive and it’s hard for me to get sign-off to use third-party tools like this. Why doesn’t Microsoft give me something like this for free?

Chris shares his personal opinions on the matter. My opinion on it, as someone who has worked with Microsoft products for a long time and never for Microsoft, is that Microsoft needs to play a balancing act. They build products and tools with the intention of third parties extending them, whether by opening up APIs or creating an explicit extensions marketplace (like we see in Azure Data Studio and Visual Studio Code). If they go and take the best bits of these third party products, then that third party marketplace dries up quickly. On the other side of the coin, depending on third parties can’t always cut it. For example, Azure Data Studio used to have an awful execution plan viewer and the answer was “use SentryOne Plan Explorer instead.” That wasn’t a great solution either for Azure Data Studio (and today, I don’t know if the extension is even still around), so the pushback was firm: a good execution plan reader needs to be a core part of any first-class SQL Server developer tool from Microsoft.

Chris has plenty to say on the topic as well.

Comments closed