A Fine Slice Of SQL Server
Chen Hirsh explains a concept:
A Streaming Table in Databricks is a table that receives data… well, in a stream.
And no, this has nothing to do with Netflix.
Imagine a conveyor belt carrying packages into a warehouse. A robot picks up each package and places it on the shelves.
Read on for the metaphor, as well as thoughts on when streaming tables make sense.
Leave a CommentErik Darling does some digging:
While looking at a client’s server, I noticed there was, at any given time, 2-3 background sessions with the
commandcolumn sayingBRKR TASK, which I found quite odd.Only the Microsoft-shipped broker queues were present, and none were activated. Nothing uses Service Broker, Mirroring, or Log Shipping.
Read on for Erik’s findings. If you need a Misfits song to listen to, we are close enough to the end of October for Halloween to work.
Leave a CommentChris Webb goes looking for unused objects:
A few weeks ago I wrote about how Semantic Link Labs now has tools with interactive UIs and showed how you could use this to view lineage and so Vertipaq-Analyzer-stuff in a notebook. I didn’t show what I think is the coolest new feature though: the ability to find the tables, columns and measures in a semantic model that aren’t used and which therefore could be deleted. There are tons of excellent third-party tools that do this available already of course but the advantage of using Semantic Link Labs for this is that you can automate the process of finding unused columns and run it from a notebook and, crucially, you have two ways of finding those unused columns: from analysing the structure of downstream reports and from analysing DAX queries captured in Workspace Monitoring.
Read on to see how it works.
Leave a CommentGreg Low has started a series on entity-attribute-value tables. The first post covers what they are:
If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).
Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.
The second post covers pros and cons:
In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.
Greg is very much against EAV, and I agree with this. I do like Greg’s alternative of using something like JSON, with the proviso that the database simply become a whole-record storage and retrieval engine rather than trying to strip out and splice in new JSON via T-SQL. Otherwise, spend the time on proper data modeling and take advantage of what the platform can do for you.
Leave a CommentAndreas Wolter shares some thoughts:
CISA recently published guidance on using cyber decoys, tripwires, breadcrumbs, and honeytokens to detect attackers who are already operating inside an environment: Using Cyber Decoys to Strengthen Detection and Response
Looking at this through my SQL Server lens, I think it is worth considering how these concepts can be applied to database systems.
Before building any kind of database honeypot, you need to ask yourself:
Click through for that list of questions, as well as additional thoughts from Andreas.
Leave a CommentFabiano Amorim explains a vulnerability:
SQL injection inside Microsoft-signed system stored procedures is not supposed to happen. Yet, as I’ve been documenting in this series, it happens more often than you may assume.
This article walks through another one I found and reported to the Microsoft Security Response Center (MSRC). It’s a textbook SQL injection sitting inside
sys.sp_MSdeletefoldercontents, a system stored procedure used by SQL Server replication.What makes it interesting is not the injection technique itself (there is no clever Unicode trick this time), but what it enables: a working execution path for
xp_cmdshellon a server wherexp_cmdshellis explicitly disabled by configuration.
Admittedly, I kind of shrug my shoulders at this one as well. You already need to be sysadmin, and sysadmins can enable xp_cmdshell whenever. I understand that the point of Fabiano’s post is that the procedure call ignores xp_cmdshell’s status, so there’s something to it. But I have trouble thinking that the SQL Server team made the wrong call in deciding it’s a low-risk vulnerability.
Leave a CommentSteve Jones looks at a pair of procedures:
I was creating a question on sp_readerrorlog and realized that this procedure is different from the one it wraps: xp_readerrorlog. This post digs into a few differences.
Click through for the answer, though Steve’s reference of sp_readerrorlog being “new” is, I think, not quite right. I’m pretty sure that both sp_readerrorlog and xp_readerrorlog were introduced in SQL Server 2005. I did dig up a Rodney Landrum article from April of 2010 talking about sp_readerrorlog, so it was at least in SQL Server 2008.
Leave a CommentHugo Kornelis digs into an execution plan:
The OVER clause, that can be added to aggregate functions to turn them into window aggregates, can come with or without a frame specification, in the form of an ORDER BY clause, plus an explicit or implied ROWS or RANGE clause. If there is no frame specification, then every row in a partition, or window, can see all other rows in the same partition or window for the purpose of the aggregation.
In plansplaining part 6, I looked at this specific form of window aggregation, and explained in detail all the steps that the execution plan takes to compute the aggregation result and add it to each of the rows, for every row in each window.
Click through for another look at the plan.
Leave a CommentGreg Low violates Betteridge’s Law of Headlines:
Foreign keys are used to ensure referential integrity in relational databases. We don’t want to have orders for customers that don’t exist, or have lines on the orders that refer to products that don’t exist. That seems straightforward enough – and a good idea – so why is there an endless discussion in the development community about whether databases should include declared foreign key constraints or not?
I tend to be a foreign key extremist, in that I want key constraints whenever possible. There are certain specific cases in which foreign key constraints can’t work, but I’d argue they’re much rarer than people think.
Leave a CommentRyan Adams explains a concept:
Connections are shared infrastructure in Microsoft Fabric. Pipelines, dataflows, semantic models, and other Fabric items use them to reach data sources without storing connection details and credentials in every item. Connection Recency adds the context administrators need to understand how each connection is being used. If you go to “Manage Connections and Gateways” in Fabric settings and open the settings of any connection, the following information card will populate:
Click through to learn more about connection recency and how it can be helpful to understand the mechanic.
Leave a Comment