Press "Enter" to skip to content

Curated SQL Posts

Power BI Licensing Guide

Published 2023-01-05 by Kevin Feasel

Reza Rad busts out the calculator:

Licensing in Power BI comes with many options. Understanding which features are included in which licensing plan is always a question for users. In this article and video, you will learn about all the different licensing plans in Power BI, the scenarios for which to use the licensing, and scenarios for which you may need to change your licensing. This article and video are intended to help you to decide the most cost-effective licensing plan for your requirement.

Reza goes into great detail in the post and then answers a lot of questions in the comments as well.

Comments closed

Cosmos DB to Data Explorer Synapse Link

Published 2023-01-05 by Kevin Feasel

Vicent-Philippe Lauzon makes an announcement:

We recently made our new Kusto data connection available in public preview:  Cosmos DB to Azure Data Explorer Synapse Link.

This does look like a marketing-heavy announcement but the short version is that you can ingest data from Cosmos DB into Data Explorer pools via Synapse Link rather than creating your own ETL process. The previous Cosmos DB connector for Synapse Link tied to a dedicated SQL pool.

Comments closed

Finding Unused Indexes on an Instance

Published 2023-01-05 by Kevin Feasel

Matthew McGiffen goes searching:

The script uses the undocumented sp_MSforeachdb internal stored procedure to iterate through all the databases (excluding the system ones) and store the results of a standard unused indexes script into a temp table. The table can then be queried to analyse the results.

This script comes with a caveat, that the sp_MSforeachdb procedure can be unreliable and on occasion skip databases so you should check that the results include all the databases you are interested in. A simple count of distinct database names, and comparing that against the number of databases on your instance should be sufficient.

That caveat aside (and if you are concerned about it, check out Aaron Bertrand’s sp_foreachdb), click through for the script. I do like that this limits you to just zero-read indexes, as even write-heavy indexes may still be useful.

Comments closed

Throwing MRAN a Retirement Party

Published 2023-01-04 by Kevin Feasel

Umachandar Jayachandran makes an announcement:

In June 2021, we announced that Microsoft Machine Learning Server & Microsoft R Open will be retired on July 1, 2022. In continuation with the retirement process, the Microsoft R Application Network (MRAN) website and CRAN Time Machine will be retired on July 1, 2023.

If you are using MRAN, click through and review the dates so you don’t have the rug pulled out from under you.

Comments closed

2023 Data Professional Survey Results

Published 2023-01-04 by Kevin Feasel

Brent Ozar busts out the briefcase full of Benjamins:

Are your peers being paid more this year? Are they switching job roles? Are they planning on leaving their companies? To find out, I run a salary survey every year for folks in the database industry. Download the raw data here and slice & dice ’em to see what’s important to you.

As a quick note, however, remember that inflation in the US went up considerably. Inflation wasn’t something we had to factor in from 2017-2021, as it was 1.5-2%. In 2021, it increased to more than 4% and in 2022 was closer to 8-9%, so converting these from nominal (pre-inflation) to real (post-inflation) will help tell the full story.

Comments closed

The Security of TDE

Published 2023-01-04 by Kevin Feasel

Matthew McGiffen explains one area of limitation with transparent data encryption:

TDE encrypts data stored on the file system, so it should be pretty clear that we are trying to protect ourselves from an attacker who gets access to our files. You would be right in suggesting that shouldn’t be allowed to happen. Access controls should be in place to prevent inappropriate access. The reality though is that sometimes we get hacked and someone is able to work around our access controls. Sometimes backup files are stored offsite with a different organization where we do not control the access. That is why we have encryption – encryption is an extra line of defense. TDE offers no protection however against individuals who have direct access to query the database.

Let’s say someone does get access to our files – does TDE mean we are still sufficiently protected?

My problem with TDE is something Simon McCauliffe wrote about a few years back (Wayback Machine link because the actual site went down in 2020): if you have root-level access to the server, you can ultimately get access to all of the keys to break TDE. I suppose the level of effort involved is high and that will mitigate the risk, but it’s always there.

2 Comments

Testing BIGINT Support in Applications

Published 2023-01-04 by Kevin Feasel

Michael J. Swart reminds us that it’s not just the database which needs to be able to handle large values:

In the past I’ve written about monitoring identity columns to ensure there’s room to grow.

But there’s a related danger that’s a little more subtle. Say you have a table whose identity column is an 8-byte bigint. An application that converts those values to a 4-byte integer will not always fail! Those applications will only fail if the value is larger than 2,147,483,647.

This post specifically pertains to identity columns but don’t forget those non-identity columns when testing.

Comments closed

A Review of Prometheus Changes at PromCon

Published 2023-01-03 by Kevin Feasel

B.C. Gain reports on sessions from PromCon EU 2022:

Prometheus’ installations are now in the hundreds of thousands range with millions of users, Richard (RichiH) Hartmann, director of community at Grafana Labs and a CNCF Technical Advisory Group Observability chair, said during his talk “I don’t have to convince this room that Prometheus is a de facto standard in cloud native metric based monitoring.”

But as Prometheus’ maintainers celebrate its 10-year anniversary, the community’s needs for monitoring Kubernetes are evolving quickly. Users are also becoming smarter about what they want and need. PromCon EU 2022, held in Munich in November, the Prometheus annual user’s conference, served as a forum about how and why Prometheus must evolve and what Prometheus maintainers must do.

Prometheus is a critical part of the modern service monitoring stack; read on to learn more about histogram updates and work at the core which should help Prometheus users along the way.

Comments closed