Press "Enter" to skip to content

Month: May 2022

Cost Savings with Azure Data Factory

Published 2022-05-20 by Kevin Feasel

Koen Verbeeck maximizes the savings:

As you might’ve noticed, pricing in ADF is not the same as it was in SSIS for example. In SSIS, you pay your SQL Server license and you’re done (well, and you buy a server to run it on). It doesn’t matter what you do with SSIS, the cost is the same. If you run 1 package or 1000 packages, there’s no difference except in your electricity bill. However, in ADF you pay more if you use it more. You pay for each action you do, you pay for each activity you use and for how long things are running. There are a couple of guidelines you can follow to try to minimize costs:

Read on for those guidelines and some specific helpful items.

Comments closed

Troubleshooting Firewall Issues with Azure SQL MI

Published 2022-05-20 by Kevin Feasel

Emanuele Meazzo sees a problem pop up regularly:

Here is something that will save you lots of time and headaches when trying to connect to Azure SQL Managed Instances, especially from onprem servers or from other clouds; I had to repeat this multiple times to multiple actors, so I know it will happen to someone else too.

In most cases, “Connect Timeout” and/or “Cannot open server xxx requested by the login; Login failed” errors are caused by the firewall configuration and a lack of understanding the SQLMI networking model, let me explain:

Read on for that explanation.

Comments closed

Running Dynamic SQL in a Different Database

Published 2022-05-20 by Kevin Feasel

Kenneth Fisher doesn’t like this database and wants to move to a new one:

This is one of those things that on hindsight was a stupid problem, but still cost me hours and a major headache. So I thought I would share the story of my headache with you. 

A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.

But it didn’t quite work out the way Kenneth initially envisioned. Read on for the full story.

Comments closed

Searching SSRS Reports for Keywords

Published 2022-05-20 by Kevin Feasel

Hannah Vernon does a search:

Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a stored procedure as the source of data for SSRS Reports. In a large SSRS environment, it can be challenging to determine which reports use a specific stored procedure.

This code provides details about SSRS Reports where the definition of the report includes any specific keyword, such as the name of a stored procedure.

Click through for a query against ReportServer.

Comments closed

MySQL Security

Published 2022-05-20 by Kevin Feasel

Lukas Vileikis starts a series on security in MySQL:

Security is a critical part of any infrastructure – it’s even more so in the database world, where one step in the wrong direction can be the cause of system disruptions and downtime, customer dissatisfaction, and in the worst-case scenario – deaths.

As MySQL is one of the primary relational database management systems being used in the world today, it’s critical to understand how to go about properly the database infrastructure to keep data breaches away from the organization’s websites or, if they’ve happened already, to become a thing of the past.

The first article covers some of the basics of security in MySQL. The second promises to have more.

Comments closed

An Index for Change Tracking Cleanup

Published 2022-05-20 by Kevin Feasel

James Ferebee creates an index:

If the issue persists and we stack a lot of data that needs to be removed which can be too much for autocleanup to manage. At that point, we recommend running (as is indicated in the error message) sp_flush_CT_internal_table_on_demand which is discusseed in detailed in Amit’s blog post Change Tracking Cleanup–Part 1.

I will not discuss the internals of the process as Amit Banerjee already touched on this in the referenced blog. However if you are routinely encountering issues where autocleanup can’t keep up and/or manual cleanup is taking significant time, you can add the index and see if it helps and I have the process enumerated below. Keep in mind this is not guaranteed to fix all cleanup issues and it may still be necessary to run manual cleanup regularly. If you continue to have issues with cleanup feel free to create a ticket with us here at CSS to assist you and get specific data to your environment.

Read on for the index definition as well as some important notes about whether you might need it.

Comments closed

Summarize in KQL

Published 2022-05-19 by Kevin Feasel

Robert Cain continues a series on KQL:

When data is analyzed, it is seldom done on a row by row basis. Instead, data analysts look at the big picture, looking at total values. For example, the total number of times the disk transfer counter is recorded for a time period may give an indication of disk utilization.

To aggregate these values with KQL, we’ll use the summarize operator.

Read on for plenty of demos.

Comments closed

Apache Flink Table Store

Published 2022-05-19 by Kevin Feasel

Jingsong Lee and Jiangjie Qin have an announcement:

As of now it is quite common that people deploy a few storage systems to work with Flink for different purposes. A typical setup is a message queue for stream processing, a scannable file system / object store for batch processing and ad-hoc queries, and a K-V store for lookups. Such an architecture posts challenge in data quality and system maintenance, due to its complexity and heterogeneity. This is becoming a major issue that hurts the end-to-end user experience of streaming and batch unification brought by Apache Flink.

The goal of Flink table store is to address the above issues. This is an important step of the project. It extends Flink’s capability from computing to the storage domain. So we can provide a better end-to-end experience to the users.

Click through to see how table storage works.

Comments closed

T-SQL Order of Execution and Aliases

Published 2022-05-19 by Kevin Feasel

Joe Billingham explains why you can’t do that thing you want to do:

So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘.

The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it?

Read on for the answer. This is why some people I know have wanted a SQL-like language which runs in order of execution, so a query would start with the FROM clause rather than the SELECT clause. Languages like KQL do work that day, so there are examples in the wild.

Comments closed

Left and Right Deep Hash Joins

Published 2022-05-19 by Kevin Feasel

Forrest McDaniel dives into the forest:

There’s a lot already written about left versus right deep hash joins. Unfortunately for us SQL Server nerds, “left” and “right” don’t make as much sense in SSMS query plans – those have a different orientation than the trees of database theory.

But if you rotate plans, you can see left and right that make sense (even if they still don’t match canonical shapes). Just follow the join operators.

Read on to understand the difference and what it means for query performance.

Comments closed