Press "Enter" to skip to content

Month: June 2021

The Enterprise Eats Software

Jessica Kerr explains why software from large firms is so often terrible:

Software is hard to get right. And every time we don’t, customers leave.

Appointment scheduling that sends a calendar invitation “Join the Zoom” without a link. Checkout screens that delete my credit card number when I change the shipping address. Complete Order that comes back with “Please try again later.” Items can’t all be shipped, can’t all be picked up, and this is a maze to figure out (also Lowe’s). A shopping cart that pops up a generic error modal when any single call to the server fails.

Read the whole thing. A lot of this sounds like an incentive alignment problem: each sub-group within a large firm optimizes for its own benefits, but the sum total of those choices leads to a sub-optimal result for the firm itself, as in the case of bad software driving customers to Amazon.

Comments closed

Spark SQL and Merge Errors from Multiple Source Rows Matched

Manoj Pandey explains an error message in Spark SQL:

UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge, when multiple source rows match on the same target row, the result may be ambiguous as it is unclear which source row should be used to update or delete the matching target row. You can preprocess the source table to eliminate the possibility of multiple matches. Please refer to https://docs.microsoft.com/azure/databricks/delta/delta-update#upsert-into-a-table-using-merge

The above error says that while doing MERGE operation on the Target table there shouldn’t be any duplicates in the Source table. This check is applied implicitly by the SQL engine to avoid unnecessary updates and avoid inconsistent data.

Read on for a reproduction and what you can do to resolve the issue.

Comments closed

Foreign Key Constraints and Blocking

Paul White takes a look at blocking due to foreign key checks:

This article covers one such consideration that does not receive much publicity: To minimize blocking, you should think carefully about the indexes used to enforce uniqueness on the parent side of those foreign key relationships.

This applies whether you are using locking read committed or the versioning-based read committed snapshot isolation (RCSI). Both can experience blocking when foreign key relationships are checked by the SQL Server engine.

Under snapshot isolation (SI), there is an extra caveat. The same essential issue can lead to unexpected (and arguably illogical) transaction failures due to apparent update conflicts.

This article is in two parts. The first part looks at foreign key blocking under locking read committed and read committed snapshot isolation. The second part covers related update conflicts under snapshot isolation.

Definitely worth reading the whole thing.

Comments closed

Reasons to Switch to Power BI Gen2

Teo Lachev gives us five reasons to switch to Power BI Gen2:

More memory
Imported models are memory-resident so memory is usually the most constraining factor. With Gen2, the capacity maximum memory applies to the resource itself and not collectively across all resources in the capacity. Let’s say you are on a P1 plan which has a maximum memory capacity of 25GB. With Gen 1, you won’t be able to have two datasets, let’s say 20GB and 10GB, loaded at the same time. However, Gen2 will apply the 25GB limit to each dataset. So, each resource (dataset, report, dataflow) will be boxed within 25 GB. This feat is possible because Gen2 uses a SaaS approach, which means datasets are scattered across multiple cluster nodes instead of being associated with a dedicated capacity. A potential downside, however, could be “noisy neighbor” because a P3 cluster node may co-host datasets from different customers.

Read on for the full list of reasons, as well as three things Teo would like to see improved in a future release.

Comments closed

SQL Injection and Square Brackets

Erik Darling is not amused:

I see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.

In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.

Click through for a demonstration of the problem.

Comments closed

Updating dbatools

Chad Callihan shows us how to update the dbatools Powershell module:

The first sentence on the dbatools download page references the belief in releasing early and releasing often. While SQL Server and SQL Server Management Studio may get a handful up dates every year, dbatools averages a few every month. Fortunately, staying up to date with dbatools is easily manageable as we’ll see below.

Read on to see how you can tell which version of the module you have and then how to update it.

Comments closed

Hyperconvergence and SQL Server

Robert Sheldon gives us a primer on hyperconverged infrastructure:

A growing number of organizations have deployed hyperconverged infrastructure (HCI) systems in an effort to simplify IT operations, better utilize resources, and lower costs. They might house the systems in their own data centers, colocation facilities, edge environments, or office closets. Regardless of the location, many of the organizations are running SQL Server on their HCI systems, often alongside other applications. Although it means deploying SQL Server to a virtualized environment, such a practice has become fairly common, especially with the advent of the cloud. This article covers hyperconvergence, another option for SQL Server.

Despite how common hyperconvergence has become, some IT teams might still not be familiar with HCI or are familiar with HCI but have not deployed SQL Server to an HCI platform. In either case, they might now be considering HCI for SQL Server and need to better understand what this looks like before deciding on new infrastructure. Although HCI can make it easier to provide a platform for SQL Server, decision-makers should know what they’re getting into before going down this route.

Click through to learn more about hyperconverged infrastructure and where it can help (or hurt).

Comments closed

SchemaDrift Available in Beta

Kiana Bergsma announces a new tool:

Save yourself Time and Money with Steve Stedman’s new database comparison tool. Following in its cousin’s (Database Health Monitor) footsteps, SchemaDrift is FREE! It is currently in Beta phase but it is free to download for personal or business use.

We only ask that you give us feedback. Let us know what you like and dislike. How can we make this product even better and we’ll send you emails on update releases. Comment down below or message us through our website.

Click through for a download link, as well as a FAQ in video form.

Comments closed

Solving Scheduling Problems with Calendar Tables

Aaron Bertrand continues a series on the utility of calendar tables:

In two previous articles (part 1 and part 2), I showed some practical ways to use a SQL Server calendar table to solve business day and date range problems. Next, I’ll demonstrate how you can use this table to solve scheduling problems, like environment-wide server patch management.

When you have a handful of servers, managing patching is straightforward. When you have hundreds, it gets more complicated to balance keeping everything in sync and not spending all of your time organizing and performing patching.

This is a concrete example of where calendar tables can make life a lot easier.

Comments closed

Using FOR XML PATH with Reserved XML Characters

Erik Darling shows how we can use FOR XML PATH on data which includes reserved XML characters:

The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.

Read on to learn more. One thing I’ve done in the past, when I know that there are specific reserved characters in use, is to run REPLACE() over the resultant data, changing &lt; to < and so forth. But Erik shows us how to do it the best way.

Comments closed