Press "Enter" to skip to content

Author: Kevin Feasel

Notifications when Admins Connect to a SQL Server

Published 2019-07-12 by Kevin Feasel

Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems:

I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.

Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.

Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.

Comments closed

Adding Line Breaks on DAX Measures

Published 2019-07-12 by Kevin Feasel

Gilbert Quevauvilliers shows how you can add line breaks to DAX measures for formatting results in Power BI:

I was at a customer and they were drilling through between pages, and I wanted to put in what the filter selections were when they drilled through to the new page.

This would allow them to easily see what had been on the previous page, and to avoid going backwards and forwards.

I quickly created the DAX measure to show the filters, but one thing that bugged me was that it looked a bit clunky and not clear in terms of what the filters were.

I knew that I wanted to use a Line Break to put each filter on a new line. 

Read on to see how you can do this.

Comments closed

Using Show-Command in Powershell

Published 2019-07-12 by Kevin Feasel

Kenneth Fisher walks through the Show-Command cmdlet in Powershell:

Years ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that will help me learn to script. At least in a very basic way. For example, if I want to see what parameters are actually available for Get-Help and maybe script out a template to work with then I can do this:

Show-Command Get-Help

Read on to see how you can put this into action.

Comments closed

Importing Biml Metadata from Excel

Published 2019-07-12 by Kevin Feasel

David Stein wraps up a series on using Biml to load flat files:

Make sure you read and understand the concepts in each of the following articles before tackling this one.

– Import Biml Metadata with GetQuerySchema
– BimlScript Code Nuggets and Mad Libs
– Import Biml Metadata Directly from Excel

One of the primary issues I’ve mentioned in each of the preceding short cut steps is a lack of real automation. Since we didn’t have our metadata externalized, we needed to copy/paste some object, T-SQL file, Biml file, or Excel Spreadsheet in order to create new extracts.

The final post in the series contains quite a bit of code, too.

Comments closed

Kafka Docker on Kubernetes

Published 2019-07-11 by Kevin Feasel

Bill Ward gives us a step-by-step set of instructions for installing Kafka Docker on Kubernetes:

In this ultimate guide I will give you a simple step-by-step tutorial on installing Kafka Docker on Kubernetes. This post includes a complete video walk-through.

There has been a lot of interest lately about deploying Kafka to a Kubernetes cluster. If you are wanting to take the deep dive yourself then you found the right article. Now that we have Kafka Docker, deploying a Kafka cluster to Kubernetes is a snap.

This makes it even easier to get started with Kafka in a development environment.

Comments closed

Spark and dotnet in a Single Container

Published 2019-07-11 by Kevin Feasel

Ed Elliott shows how you can combine Spark and .NET Core in a single Docker container:

This is quite new syntax in docker and you need at least docker 17.05 (client and daemon), after the images “FROM blah” you can specify a name “core” in this case, then later you can copy from the first image to the second using “–from=” on the “COPY” command.

In this dockerfile I have added Spark 2.4.3 and the default environment variables we need to get spark running, if you grab this dockerfile and run “docker build -t dotnet-spark .” you should get an images you can then run which includes the dependencies for dotnet as well as spark.

Ed includes all of the scripts needed to test this out, too.

Comments closed

Memory Defaults in SQL Server 2019

Published 2019-07-11 by Kevin Feasel

Randolph West looks at a new settings tab in the SQL Server 2019 installation:

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias.

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.

On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. Last week we looked at MaxDOP, and this post will specifically look at the Memory tab.

It’s a small change but a nice one.

Comments closed

Extended Events Files on Linux

Published 2019-07-11 by Kevin Feasel

Jason Brimhall looks at an error when trying to set up an Extended Events session on Linux:

This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to the Database directory after creating it while in sudo mode. I will get to that in just a bit. Let’s see what the errors would look like for now.

Msg 22048, Level 16, State 1, Line 15
xp_create_subdir() returned error 5, ‘Access is denied.’
Msg 25602, Level 17, State 23, Line 36
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: ‘Access is denied.
‘ while creating the file ‘C:\Database\XE\PREEMPTIVE_OS_PIPEOPS_0_132072025269680000.xel’

Read on for the solution.

Comments closed

Creating Azure SQL Elastic Jobs

Published 2019-07-11 by Kevin Feasel

Arun Sirpal takes us through Elastic Jobs against Azure SQL Databases:

The purpose of an Elastic Job is to execute a T-SQL script that is scheduled or executed ad-hoc against a group of Azure SQL databases.  Targets can be in different SQL Database servers, subscriptions, and/or regions. This blog post is quite long and heavy (code wise) so grab a coffee and follow through.

The architecture you could follow is shown below.

All of the code is in Powershell and Arun talks us through it.

Comments closed