Press "Enter" to skip to content

Curated SQL Posts

Don’t Use sys.dm_hadr_cluster_members for Quorum Info

Published 2020-05-11 by Kevin Feasel

Sean Gallardy explains a limitation of sys.dm_hadr_cluster_members:

I’ve now run across a few different instances where the monitoring for quorum was done via this DMV. On the surface, it seems like nothing would be wrong with using the “number_of_quorum_votes” column to check on the members of the cluster and see their voting status. However, this isn’t quite the case… you see there are various mechanisms that influence whether or not a member (or witness) has a vote and these continue to be expanded in each version of WSFC.

Click through for a short history lesson as well as some good advice on how accurately to get this information.

Comments closed

Patching SQL Server in Docker Containers

Published 2020-05-11 by Kevin Feasel

Rob Farley takes us through updating SQL Server when it lives in a container:

Now, the thing with running SQL in containers is that the concept of downloading a patch file doesn’t work in the same way. If it were regular Linux, the commands would be very simple, such as ‘sudo yum update mssql-server’ in RHEL. But Docker doesn’t quite work the same way, as reflected by the Microsoft documentation which mentions Docker for installing but not in the Update section.

Rob then explains the process. Containers are cattle, not pets. Just make sure your data files live outside the container before you blow it away…

Comments closed

Determining Statistics Utilization

Published 2020-05-11 by Kevin Feasel

Deborah Melkin shows us how to see if a particular statistic is in use:

You know those tweets that you see once but can never find again? I remember seeing one a while ago where someone tweeted to #sqlhelp asking if the internal inserted and deleted tables had statistics or if they were like table variables, which didn’t.

This is a great question in general. But then it got me thinking – how do you prove this? I wanted to know the answer as well so I decided to look into this. And I went down the wrong sort of rabbit hole trying to figure this out. Eventually I talked to a friend about this and got pointed in the right direction…

And the answer to how you find which statistics are used is…?

Read on for the answer and several examples.

Comments closed

Using Postman with Power BI’s REST API

Published 2020-05-11 by Kevin Feasel

David Eldersveld takes us through the Power BI REST API:

Postman is a valuable tool to work with APIs, especially when testing and making ad hoc requests outside of an automated production solution. In terms of where a Power BI developer may find Postman useful, it sits somewhere between the documentation’s “Try It” functionality and a more production-worthy solution incorporating tools like Azure DevOps, Logic Apps/Power Automate, a Power BI custom connector, etc.

The ideas in this post extend an original post from Carl de Souza. Carl shows how to obtain an OAuth2 access token but does so with hardcoded values. Additional API requests use the token from the original response, but he also manually provides this token to those subsequent API calls.

David has a clever technique for getting the bearer token, so check it out.

Comments closed

Technology Choices for Streaming Pipelines

Published 2020-05-08 by Kevin Feasel

The Hadoop in Real World team takes us through different tools available when working on streaming pipelines:

Businesses want to get insights as quickly as possible and do not want to wait for a day, like before, to bring up a report to understand what happened till yesterday. They require a more proactive approach that can help to act immediately when something significant happens and also to prevent the system from any faults/downtime before it occurs. Imagine you are buying some product from an e-retailer and you have gone till the point to make payment and something happened that caused the payment not to go through successfully. At that very moment, you are having a second thought about whether to buy the product now or later. Suppose, if the business is getting a report of this occurrence next day, it would not be of much use for them as the customer would have already bought it from somewhere or decided against it. This is where real-time events and insights come in. If it were a real-time report, the team would have called up the customer and made the purchase by offering some discounts, which in turn would have changed the mind of the customer.

Click through for a high-level discussion of these tools.

Comments closed

Project Metamorphosis: Elastic Kafka Clusters

Published 2020-05-08 by Kevin Feasel

Jay Kreps explains what Confluent has been up to lately:

What is Project Metamorphosis?

Let me try to explain. I think there are two big shifts happening in the world of data right now, and Project Metamorphosis is an attempt to bring those two things together.

The first one, and the one that Confluent is known for, is the move to event streaming.

Event streams are a real revolution in how we think about and use data, and we think they are going to be at the core of one of the most important data platforms in a modern company. Our goal at Confluent is to build the infrastructure that makes that possible and help the world take advantage of it. That’s why we exist.

But event streaming isn’t the only paradigm shift we’re in the midst of. The other change comes from the movement to the cloud.

Click through for the high-level. I can see this even more directly competing with Kinesis and Event Hubs.

Comments closed

Polygon-Based Spatial Searches with Cosmos DB

Published 2020-05-08 by Kevin Feasel

Hasan Savran continues a series on spatial data in Cosmos DB:

I want to continue to develop our new map application for Azure Cosmos DB. So far, we can run a custom spatial query in Cosmos DB and display the results on a map. I want my users to create a polygon on map and search for data under this polygon. If you are familiar with Zillow, that is how their application lets you look for houses to buy or rent. You select an area, and Zillow application displays all available houses or rental under the area you draw. It is extremely useful and user-friendly search.

Click through to see how Hasan does it, as well as getting around a coordinate ordering problem.

Comments closed

Using Computed Columns to Avoid Scans without Changing Queries

Published 2020-05-08 by Kevin Feasel

Andy Mallon shares a trick you don’t want to use too often, but can get you out of a pinch:

We’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance.

Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s because you’ve got a third-party app and simply don’t have access to the code. But you do have access to the database…

This is the type of thing you learn about and use maybe twice in your career, and then you get frustrated with the third-party vendor which won’t fix their code.

Comments closed

A History of Bad Habits

Published 2020-05-08 by Kevin Feasel

Aaron Bertrand has a compendium of bad habits, anti-patterns, and Festivus-quality grievances to air:

Going back more than a decade, I’ve been writing and presenting about what I call “bad habits” – typically shortcuts or sub-optimal ways to do things in SQL Server. Often users just don’t realize these things are bad or that there is a better way.

Here is an ongoing list of articles that I consider to be along these lines – eradicating bad habits or at least promoting best practices. Not all are explicitly framed as a “bad habit,” but they do all present things I wish I observed less often.

Click through for a disturbingly long list of items.

Comments closed

mssql-cli Generally Available on MacOS and Linux

Published 2020-05-08 by Kevin Feasel

Alan Yu announces the general availability of mssql-cli outside of Windows:

We are excited to announce that mssql-cli is now generally available on macOS and Linux.

mssql-cli is an open source and cross-platform command-line tool (CLI) to manage SQL Server on-prem and on the cloud. We are a proud member of the dbcli family of open source command line tools to manage relational databases.

If you are a user of sqlcmd, you will love the interactive and modern design components in mssql-cli. With this release, you will also be able to use mssql-cli in non-interactive scenarios such as scripting and automation. Read on to learn more about how mssql-cli will help improve your productivity through a modern CLI experience.

If you love the command line (or simply need to SSH into a box from time to time), give this product a try.

Comments closed