Press "Enter" to skip to content

Author: Kevin Feasel

Powershell Runspaces

Mark Wilkinson has a new blog and an itch to explain the notion of runspaces in Powershell:

So I have a problem with some of the posts I’ve read about runspaces. It all comes down to a small detail that I think makes a big difference in your understanding of them.

$Runspace = ::Create()

This code looks innocent. What does it do? You’d probably think it’s creating a new runspace, but it’s not. This code is instead creating a fresh instance of PowerShell. If you run this code and run Get-Runspace you’ll see there is still just one listed, the one attached to your current session. So what is this instance we just created?

Click through for an example and a step-by-step breakout of that example.

Comments closed

Rebuilding Rowstore Indexes Online on Tables with Columnstore Indexes

Niko Neugebauer explains something about rebuilding rowstore indexes:

This blogpost will be about older SQL Server versions (2016, 2017) and some implications that I have found that people rather do not understand, until they hit the problems in productions – the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes on their tables.
As you should know by now – SQL Server 2017 & SQL Server 2019 respectively brought the ONLINE features support for the Columnstore Indexes (Nonclustered(2017) & Clustered(2019)) and I have blogged about those news in Columnstore Indexes – part 96 (“Nonclustered Columnstore Index Online Rebuild”) in 2017 and in Columnstore Indexes – part 123 (“Clustered Columnstore Index Online Rebuild”) in 2018.

What I honestly did not expect is the amount of the same question I have faced as in the last 3 months regarding the rebuild operations for the the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes.

Read on to see the demonstration for when you have a clustered columnstore index and a nonclustered columnstore index.

Comments closed

Row-Level Security in Power BI Reports

Drew Skwiers-Koballa shows how to use an embed token to implement row-level security with Power BI:

To present a PowerBI report user or consumer with a securely pre-filtered dataset, row level security must be used. In a PowerBI embedded architecture where “app owns data”, implementing row level security (RLS) requires a modification to the token generation request. By specifying a role and user in the token request, we can generate an embed token specific to the user’s data access.

Click through for the instructions.

Comments closed

Implementing Soft Deletes in SQL Server

Brent Ozar shows how to use soft deletes in SQL Server:

Normally when you run a DELETE statement in a database, the data’s gone.

With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages:

– Easier/faster undeletes
– History tracking (keeping deleted rows around for auditing purposes, although the method I’m going to show here doesn’t do anything fancy like track who did the delete)
– Easier reconciliation during disaster recovery failovers (refer to the Senior DBA class module on recovering from failovers for more info)
– Lower workload for Availability Groups secondaries (neat scenario from Aaron Bertrand)

Read on to see how and what the costs of this are.

Comments closed

Check Those R Repos

John Mount has a public service announcement:

In a lot of our R writing we casually say “install from CRAN using install.packages('PKGNAME')” or “update your packages by using update.packages(ask = FALSE, checkBuilt = TRUE) (and answering ‘no’ to all questions about compiling).”

We recently became aware that for some users this isn’t complete advice.

The above depends on your R install pointing to a repository that is in fact up to date. To check what repositories you are using please use the command options('repos').

The specific example here is around the Microsoft R Archive Network (MRAN), which stays at fixed dates. This is for a good reason: because it helps companies standardize on a known set of versions of R packages by default. That way you don’t have version 1.8 of a package in dev and then get 1.9 in production and find out that something broke between the two versions.

Comments closed

Delta Lake and ACID Properties

Kundan Kumarr notes that Spark’s Delta Lake allows for ACID transactions:

DeltaLog is the crux of Delta Lake which ensures atomicity, consistency, isolation, and durability of user-initiated transactions. DeltaLog is an ordered record of transactions. Every transaction performed since the inception of Delta Lake Table, has an entry in the DeltaLog (also known as the Delta Lake transaction log). It acts as a single source of truth, giving users access to the last version of a DeltaTable’s state. It provides serializability, the strongest level of isolation level. Let’s see how DeltaLog ensures ACID Transactions.

Click through for the explanation.

Comments closed

Should DBAs Learn Kubernetes?

Randolph West makes me violate Betteridge’s Law of Headlines:

So this question, whether a SQL Server DBA really needs to know about Kubernetes, is really a question about whether DBAs need to know about the plumbing that runs the infrastructure upon which our databases reside.

In October 2018 I asked, “What is a DBA anyway?” It was a week after another post where I declared the DBA role “history.” My answer is:

Yes! You need to know Kubernetes if you’re a SQL Server DBA.

I agree with Randolph that it’s useful for a DBA to have at least some working understanding of Kubernetes, especially around being able to troubleshoot database issues on the platform. Read on for Randolph’s take on the matter.

Comments closed

RESULT_SCAN() in Snowflake

Koen Verbeeck introduces us to the RESULT_SCAN() function in Snowflake DB:

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.

This post builds upon part 6 of the series, which dealt with query history. There it is explained how Snowflake caches the query results. You can find a query in the history and take a look at what was returned. Using the RESULT_SCAN table function, you can do this with SQL. Let’s take a look at an example.

This is an interesting function. Click through to see it in action.

Comments closed