Press "Enter" to skip to content

Day: November 23, 2021

Managing File Retention in Blob Storage

Published 2021-11-23 by Kevin Feasel

Jeet Kainth shows how to configure a retention strategy in Azure Blob Storage:

This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you will need to add the Az.Storage module to the automation account to be able to use some of the commands listed in this blog.

Click through for the process, including Powershell code to perform the task.

Comments closed

Offset Window Functions

Published 2021-11-23 by Kevin Feasel

I continue a series on window functions:

Offset functions are another class of window function in SQL Server thanks to being part of the ANSI SQL standard. Think of a window of data, stretching over some number of rows. What we want to do is take the current row and then look back (or “lag” the data) or forward (“lead”).

There are four interesting offset window functions: LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE(). All four of these offset functions require an ORDER BY clause because of the nature of these functions. LAG() and LEAD() take two parameters, one of which is required. We need to know the value to lag/lead, so that’s a mandatory parameter. In addition, we have an optional parameter which indicates how many steps forward or backward we want to look. Let’s see this in action with a query:

Click through for that query, as well as a few more and plenty of explanation.

Comments closed

Error Messages during Change Tracking Cleanup

Published 2021-11-23 by Kevin Feasel

Lee Markum troubleshoots some error messages:

You’re a data professional and you’re trying to keep up with patching a wide range of SQL Server versions and editions. How do you know what’s in the CU and whether you should apply it or not? My favorite way to read up on CUs is to go to SQLServerUpdates and click around there.  It will take you to the latest CU pages as well as give you a way to see previous CUs that are available.

While doing this recently, I discovered this for CU 26 on SQL Server 2017.

These sorts of regressions do slip in, so keep an eye on them before (and after) upgrading. Lee gives us a concreate example of one in a recent CU of SQL Server 2017.

Comments closed

Sargability and Dates

Published 2021-11-23 by Kevin Feasel

Chad Callihan makes me want to change the title to “Getting Sarge a Date”:

We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:

For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.

1 Comment

Managed Instance Failover Groups

Published 2021-11-23 by Kevin Feasel

Arun Sirpal takes us through Azure SQL Managed Instance failover groups:

If you have been following me for a while you will know that I really like the Fail over groups within Azure SQL DB and it is no different to when applying it to Managed Instances. If you want a rock-solid DR plan, this is the way forward.

Remember it’s an abstraction layer on top of the active geo-replication feature, before this we had to do a lot of manual one to one database setups but now this feature simplifies deployment and management of geo-replicated databases at scale. You can initiate failover manually or automatically if there is a massive failure (researching this topic this could mean things from memory leaks to wrong network cables cut during routine hardware decommissioning – you never know, it could happen so plan for it)

Click through to see how to set this up and what failover looks like.

Comments closed

Intent Shared Locks in RCSI

Published 2021-11-23 by Kevin Feasel

Tomas Zika troubleshoots a deadlocking problem:

I was analyzing a deadlock graph and there was a mystery lock of type IS (Intent Shared). That was weird by itself because the database has Read Committed Snapshot Isolation (RCSI) enabled, which is the Optimistic Concurrency model that shouldn’t take shared locks. All the statements were contained in this database. Also, the locked table was seemingly unrelated to anything that has been going on in the deadlock report.

Click through for an image, a repro script, and an explanation as to what exactly is going on.

Comments closed