Press "Enter" to skip to content

Author: Kevin Feasel

Transforming a REST API into a Data Stream

Lucia Cerchie and Dave Troiano build a stream:

In the space of APIs for consuming up-to-date data (say, events or state available within an hour of occurring) many API paradigms exist. There are file- or object-based paradigms, e.g., S3 access. There’s database access, e.g., direct Snowflake access. Last, we have decoupled client-server APIs, e.g., REST APIs, gRPC, webhooks, and streaming APIs. In this context, “decoupled” means that the client usually communicates with the server over a language-agnostic standard network protocol like HTTP/S, usually receives data in a standard format like JSON, and, in contrast to direct database access, typically doesn’t know what data store backs the API.

Of the above styles, more often than not, API developers settle on HTTP-based REST APIs for a number of reasons. They are incredibly popular. More developers know how to use REST APIs and are using them in production compared to other API technologies. For example, Rapid API’s 2022 State of APIs reports 69.3% of survey respondents using REST APIs in production, well above the percentage using alternatives like gRPC (8.2%), GraphQL (18.6%), or webhooks (34.6%). 

Click through for a demonstration of how to take an existing REST API and build a data stream out of it using Apache Kafka and Apache Flink.

Leave a Comment

Downloading and Deleting Files in Oracle Object Storage

Brendan Tierney continues a series on Oracle Object Storage:

In my previous posts on using Object Storage I illustrated what you needed to do to setup your connect, explore Object Storage, create Buckets and how to add files. In this post, I’ll show you how to download files from a Bucket, and to delete Buckets.

Click through for scripts to perform both. If you just want to delete an item from a bucket without deleting the bucket as a whole, you can do so with a quick modification to Brendan’s script.

Leave a Comment

SSIS Extensions in Visual Studio

Andy Leonard fills us in on what’s happening in the world of Integration Services:

I’m not sure why the decision was made to “fork” development and maintenance of the Integration Services extension for Visual Studio 2022. Based upon experience with SSIS, Visual Studio, and the Microsoft SSIS team, I am confident there’s a very good reason. As one can glean from my appointment screenshot above, I save the links to both pages. The links are:

SSIS Pre-2022: https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects&ssr=false#overview
SSIS 2022: https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices

Read on to see what’s new in both of those projects.

Leave a Comment

Exporting Multiple Databases Concurrently via SqlPackage

Jose Manuel Jurado Diaz has a script for us:

This week, I’ve been working on a service request case where we need to export multiple databases using SqlPackage. Following, I would like to share my lesson learned to export  simultaneous several databases, saving the export files to the F:\sql folder and the logs of the operations to the F:\sql\log folder.

Few recommendations when performing these exports:

Click through for those recommendations and the script. The cynic in me would add a third tip: make sure your databases are small, or else SqlPackage won’t work so well.

Leave a Comment

Using the Dedicated Admin Connection in SQL Server

David Seis talks about the DAC:

In SQL Server, a feature that often stands out for its unique capabilities is the Dedicated Administrator Connection (DAC). This not just another connection to your SQL Server instance, but a special diagnostic connection designed for administrators when standard connections to the server are not possible.

Read on to see how it differs from regular connections and how you can use it.

Leave a Comment

Explaining Filter Context in DAX

Marco Russo and Alberto Ferrari bust out the drawing board:

The filter context is a fundamental concept to understand in order to write good DAX code. In this article, we describe the filter context using a visual approach – we rely on a graphical visualization that represents the different interactions that exist in the filter context when you use the visual elements, filters, and slicers of a report. This is the first step to plan the changes required to obtain the desired result: these changes can be obtained by using the CALCULATE function, which removes, adds, and replaces existing filters in the filter context.

This article provides a different perspective on a topic already discussed in other filter context articles: read them to get more insights about this importance concept for DAX.

Read on to learn more about how it works.

Leave a Comment

Using a Filtered Index to Prevent Duplicate Rows

Jared Westover solves a problem:

Have you ever needed to prevent duplicate rows from popping up in a table, but the built-in unique constraint in SQL Server falls short? I ran into a table with duplicate rows, but we couldn’t delete them, at least for now. Ideally, you would delete all the duplicates and call it a day. If life were this simple, it would be boring. We needed to prevent new ones from showing up and keep the existing ones. The problem with a unique constraint is that it applies to all the rows in a column.

You can also use a filtered index to prevent the problem in the first place. This was actually an interview question I liked to give: with filtered indexes, you can ensure each non-NULL value was unique, but that you could have as many rows with NULL as your dataset required.

Leave a Comment

Updating Records in a Kusto Database

Vincent-Philippe Lauzon has an announcement:

Kusto databases, either in Fabric (KQL Database) or in Azure (Azure Data Explorer), are optimized for append ingestion.

In recent years, we’ve introduced the .delete command, allowing you to selectively delete records.

In February, we introduced the .update command in public preview.  This command allows you to update records by deleting existing records and appending new ones in a single transaction.

Today, the .update is Generally Available (GA)!

Click through for more details, including a link to the documentation, where you can see several examples of the syntax.

Leave a Comment

Low-Downtime Migration Techniques from SQL Server 2017 to 2022

Yohei Kawajiri describes three techniques for performing a SQL Server migration:

It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind: 

  1.  Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid. 
  2.  Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity. 

Building an availability group? Yeah, makes a lot of sense. Performing log shipping? Sure, I could see that working. Database mirroring? I did not expect to read that one, mostly because it’s been deprecated for a decade.

Leave a Comment