Press "Enter" to skip to content

Month: May 2022

Seeding AG Replicas from Snapshots in SQL Server 2022

Published 2022-05-27 by Kevin Feasel

Anthony Nocentino is excited about using storage snapshots in SQL Server 2022:

But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process can be nearly instantaneous?

In addition to saving you time, this process saves your database systems from the CPU, network, and disk consumption that comes with direct seeding and using backups and restores to seed.

This process described in this post is imlemented on Pure Storage’s FlashArray and works cloud scenarios on Pure’s Cloud Block Store.

Click through to see how.

Comments closed

Creating a SQL Server 2022 dacpac

Published 2022-05-27 by Kevin Feasel

Kevin Chant gets an upgrade:

In this post I want to cover how you can create a dacpac for SQL Server 2022 databases using sqlpackage. So that you keep the new SQL Server 2022 compatibility level when you deploy new databases.

Just to clarify, a dacpac file is a special type of file which contains details about SQL Server database objects. Which you can use to deploy database updates to other SQL Server databases.

Read on for initial thoughts, a post-upgrade experience, and more.

Comments closed

Power BI Datamarts

Published 2022-05-26 by Kevin Feasel

Reza Rad has a four-parter on datamarts in Power BI. First up is the simple question:

One of the newest additions to the Power BI components is the Datamart. Power BI Datamart is more than just another feature, it is a major milestone where the development of Power BI solutions will be revolutionized based on that. This is a feature that helps both citizen data analysts and developers. In this article and video, I’ll explain what is a Power BI datamart, how it helps you in your Power BI implementation, and why you should use it?

Next up, how do you create one?

In the previous article, you learned what is a Datamart and the use cases of that in a Power BI implementation. In this article and video, you will have your first experience with Datamarts and learn through an example about what it is and how it works in detail. The interesting thing in all the steps below is that you just need a web browser to build the datamart.

Thirdly, we have datamart components:

What is a Power BI Datamart underneath? Can you connect to the database generated by Power BI Datamart? how the Dataset associated with the Datamart can be used? Is there a linage view? In this article and video, I’ll explain These and you will learn about the components of a Power BI Datamart. If you are new to Power BI Datamarts, this article explains what it is and its use cases and this article gets you through the Datamart editor and your first experience with it.

Reza rounds out this series of videos with how datamarts fit into the broader Power BI ecosystem:

Power BI Datamart is integrating well with other components of the Power BI ecosystem (such as workspaces, sharing, deployment pipelines, endorsements, sensitivity labels, etc). In this article and video, I’ll explain how Power BI works with other features and services in Power BI implementation. If you are new to Power BI Datamarts, this article explains what it is and its use cases and this article gets you through the Datamart editor and your first experience with it. You can also learn about the components of the Power BI Datamart from this article.

Click through for four videos hitting the high points.

Comments closed

Square Area Charts

Published 2022-05-26 by Kevin Feasel

Simon Rowe fills in a grid:

A square area chart—also known as a waffle chart—is a square display.  They often consist of a 10×10 grid containing 100 individual squares. The squares are then coloured based on the data that’s being visualised. The chart gets its food-based name from its resemblance to a tasty waffle. (Sometimes this type of chart is referred to as a “unit chart,” although that term applies more appropriately to a broader category of visualisations, which tend to use discrete icons, rather than squares, to represent the values.)

Click through for several good use cases.

Comments closed

T-SQL Enhancements in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Aaron Bertrand notes some T-SQL improvements:

A few of the most useful changes I’ve been able to play with in SQL Server 2022 so far:

– GREATEST / LEAST

– STRING_SPLIT

– DATE_BUCKET

– GENERATE_SERIES

In this tip, I’ll explain each one, and show some practical use cases.

Click through for more information. I particularly like GREATEST() and LEAST() but GENERATE_SERIES() could be very useful as well. Some of this stuff was first made available in Azure SQL Edge.

Comments closed

Window Function Improvements in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Itzik Ben-Gan knows how to inspire joy:

Microsoft recently released the first public preview of SQL Server 2022. This release has a number of T-SQL improvements. In this article I focus on windowing and NULL-related improvements. These include the new WINDOW clause and the windowing NULL treatment clause.

I’ll be using the sample database TSQLV6 in the examples in this article. You can download this sample database here.

I’ve been waiting for INGORE NULLS since they previewed it in Azure SQL Edge.

Comments closed

Extended Events in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Tom Zika checks out some extended events:

It has been announced today (2022-05-24) during the MS Build event. The blog post includes a download link. Unfortunately, the Docker container is not quite ready yet.

Anyway, because I’m a #TeamXE, I had to check out if there are any new goodies there. So, I took an XE event list from Microsoft SQL Server 2019 (RTM-CU16) and the new one from Microsoft SQL Server 2022 (CTP2.0) and compared them.

There are a lot of new events—click through to see how many.

Comments closed

Pre-Staging Log Shipping Backups with dbatools

Published 2022-05-26 by Kevin Feasel

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

IF Branches and Updating Parameters

Published 2022-05-26 by Kevin Feasel

Erik Darling explains why NULL checks on parameters doesn’t help when trying to improve query performance:

One thing I see developers do quite a bit is try to “fix” a parameter in an IF branch.

Maybe it’s to protect against bad search values, but more often it’s to nix NULLs.

I know that the stored procedure I’m showing only has one branch in it where a query is executed, and the series is supposed to be about if branching with multiple queries.

I’m only doing that to simplify the point of this post, which is that “fixing” supplied values does not correct performance and cardinality estimation issues with queries in IF branches.

Click through for the example and a brief round-up of several things which don’t work.

Comments closed