Press "Enter" to skip to content

Month: June 2022

Creating a SQL Server 2022 Learning Environment

Published 2022-06-09 by Kevin Feasel

Marlon Ribunal gets us started with a Docker container:

Maybe you want to get your hands dirty with the bells and whistles of the latest iteration of SQL Server, but you don’t have an extra bare metal or Azure or GCP based VM. Well, you’re in luck because Microsoft just released container images for SQL Server 2022.

Here are few steps to get you started with SQL Server 2022:

At this point, it’s quite easy to give new versions of SQL Server a try, even when they’re in preview. That said, some of the features make it to containers later so you might want to spin up a virtual machine and install it if there’s something you can’t get right now in the container.

Comments closed

Implementing a Power BI Composite Model

Published 2022-06-09 by Kevin Feasel

Marc Lelijveld walks us through an implementation of the Power BI composite model:

Have you been working with Composite Models in Power BI? Did you run into challenges while you did? Then this blog is for you! In this blog I will further elaborate on what the composite models (including DirectQuery for Power BI datasets) are and everything that comes into play when you start implementing them.

During this blog I will introduce you to topics like source groups, storage modes and relationship evaluation. All different topics that come into play when you start building composite models.

If composite models sound interesting, be sure to check this out.

Comments closed

Reviewing Power BI Field Parameters

Published 2022-06-08 by Kevin Feasel

Teo Lachev is pleased:

Coming back from a long vacation and I almost missed this new Power BI killer feature: Field Parameters! Not to be confused with Dynamic M Query Parameters that I ranted about here, field parameters solve a long-standing limitation of Power BI that prevents you to bind dynamically dimension members to a visual. Dynamic binding wasn’t issue with measures because they are dynamic and can evaluate runtime conditions, such as slicer selection. But dimensions were a different story. Once you have bound them to a category bucket in a visual, you couldn’t change them on the fly.

Read on for more information on a common scenario in which field parameters can be quite helpful.

Comments closed

Monitoring Open Connections in the Serverless SQL Pool

Published 2022-06-08 by Kevin Feasel

Liliam Leme has a pair of queries for us:

Consider a scenario where you are trying to monitor the connections from other applications to serverless SQL. I hit this need while trying to understand how many connections opened I had coming from an application. Had I hit some kind of limitation on serverless SQL or not. Spoiler: There is no limit for connections on Synapse serverless SQL  as you would find with a dedicated SQL pool (formerly SQL DW).

Alternatively, the limit to the number of serverless SQL pool connections is how much cash you have in your bank account…though given that it’s $5 per TB processed, if you’re writing good queries, that’s a lot of queries and connections.

Comments closed

Query Store Queries with Missing Index Requests

Published 2022-06-08 by Kevin Feasel

Erik Darling makes a query purchase at the Query Store:

I’ve said quite a bit about missing index request utility generally in SQL Server, even as recently as last week!

But then I got a user question about using Query Store to do something similar, so here goes.

If you need a pre-2019 way to do this with Query Store, Kendra Little has a blog post about that here.

For the 2019 version, check out Erik’s query and then sp_QuickieStore to make it easier.

Comments closed

Backups to S3 in SQL Server 2022

Published 2022-06-08 by Kevin Feasel

Anthony Nocentino tries out backup to S3 in SQL Server 2022:

In s3 object storage, a file is broken up into as many as 10,000 parts. In SQL Server, the each part’s size is based on the parameter MAXTRANSFERSIZE since this is the size of the write operation performed into the backup file. The default used for backups to s3 compatible storage is 10MB. So 10,000 * 10MB means the largest file size for a single file is about 100GB. And for many databases, that’s just not big enough. So what can you do…first you can use compression. That will get more of your data into a single file.

This right here is the pain. Anthony shows a few ways to extend this number but there’s still a hard cap on maximum backup size, one we don’t have on-premises.

Comments closed

Changing Default Powershell Behavior via Commands or Proxies

Published 2022-06-08 by Kevin Feasel

Jeff Hicks gives us a choice:

I’ve often told people that I spend my day in a PowerShell prompt. I run almost my entire day with PowerShell. I’ve shared many of the tools I use daily on Github. Today, I want to share another way I have PowerShell work the way I need it, with minimal effort. This specific task centers on files and folders.

As you might expect, I am constantly creating, editing, and managing files. I do all of this from a PowerShell prompt. I rarely use the start menu to find a program to launch. My challenge has always been finding the files and folders I’ve recently been using. Get-ChildItem is naturally the PowerShell tool of choice, but I’ve finally gotten around to making it work the way I need.

Not having done either of these before, I’m not sure which would be my preference, as I’d like to make sure it’s easy for me to remember later how I got to this non-standard state in case I need to replicate it elsewhere or if somebody else is at my keyboard. That’s one nice thing about the .bashrc file: it’s just there and well-known enough that people can look for changes there.

Comments closed

Request-Response and CQRS in Kafka

Published 2022-06-07 by Kevin Feasel

Kai Waehner compares two message exchange patterns:

How can I do request-response communication with Apache Kafka? That’s one of the most common questions I get regularly. This blog post explores when (not) to use this message exchange pattern, the differences between synchronous and asynchronous communication, the pros and cons compared to CQRS and event sourcing, and how to implement request-response within the data streaming infrastructure.

Read on to learn more.

Comments closed

The Value of MLOps

Published 2022-06-07 by Kevin Feasel

Tori Tompkins explains what MLOps is and why it’s valuable:

A ML project will typically begin in an ‘Explore Phase’ where a data scientist or team of data scientists will explore the data they currently have and experiment with models, algorithms, parameters and features. MLOps at this stage is responsible for supplying Data Scientists with environment they need to achieve this. One way this can be done is by leveraging Feature Store.

A feature store is a tool for storing commonly used features. As data scientists create new features then can log these into feature stores such as Feast and Databricks Feature Store, they can reuse these features across teams and projects. This will benefit teams in multiple ways by reducing compute times for both training and inference, provide consistency in common features and reducing effort for create complex logic.

Read on for information about all six phases.

Comments closed