Press "Enter" to skip to content

Author: Kevin Feasel

Upgrading SQL Server 2017 Containers to 2019

Published 2019-11-21 by Kevin Feasel

Anthony Nocentino takes us through one of the big changes to SQL Server containers:

When you start up the 2017 container, the SQL Server (sqlservr) process is running as root (uid 0). Any files created by this process will have the user and group ownership of the root user. Now when we come along later and start up a 2019 container, the sqlservr process is running as the user msssql (uid 10001 by default). This new user doesn’t have permission to open the database files and other files used by SQL Server.

Read on to see how Anthony fixed this.

Comments closed

Throttling Power BI Data Gateways

Published 2019-11-21 by Kevin Feasel

Gilbert Quevauvilliers shows how you can use load balancing with Power BI gateways:

I always recommend for On-Premise Data Gateway installations that there be at least 2 Gateways installed.

The initial reason was to ensure that if one gateway went down the other one would be able to still refresh or connect to the DirectQuery or LiveConnection sources.

With the recent update where you can now control the CPU and Memory on each Gateway instance, this means that I am able to define a dedicated server to take more of the refreshing/DirectQuery/LiveConnection load. And I can offload secondary refreshing to the second server which might be installed on another server with multiple roles.

NOTE: In order this to work you must have the Oct 2019 version of the On-Premise Data Gateway installed.

There’s a lot of good information here, including one potential failure scenario.

Comments closed

T-SQL Tuesday 120 Round-Up

Published 2019-11-21 by Kevin Feasel

Wayne Sheffield summarizes T-SQL Tuesday #120:

The end of the first 10 years of T-SQL Tuesday blogging occurred this month, with me hosting T-SQL Tuesday #120. The theme this month was to talk about something you’ve seen that made you think “What were you thinking?” (you can read the invitation here). We had several bloggers jump in and post their thoughts. So let’s just jump into a quick recap of who posted what (for each blogger, I also include a link to their Twitter account, their main blog, and the link to their T-SQL Tuesday #120 post).

Click through for the recap.

Comments closed

Text Processing Tools and Methods

Published 2019-11-20 by Kevin Feasel

Ines Roldos takes us through several tools and techniques used in text processing:

Text processing is the process of analyzing and manipulating textual information. This includes extracting smaller bits of information from text (aka text extraction), assign values or tags depending on its content (aka text classification), or performing calculations that depend on the textual information. 

Since we naturally communicate in words, not numbers, companies receive a lot of raw text data via emails, chat conversations, social media, and other channels. This unstructured data is filled with insights and opinions about different topics, products, and services, but companies first need to organize, sort, and measure textual data to get access to this valuable information. One way to process text data is manually, which has been the most popular method – up until now.

We’re still in the early days of text processing, but there have been some nice improvements over the past decade.

Comments closed

Debugging Code in R

Published 2019-11-20 by Kevin Feasel

Marina Wyss walks us through debugging techniques in R:

There are many ways to approach these problems when they arise. For example, condition handling using tools like try(), tryCatch(), and withCallingHandlers() can increase your code’s robustness by proactively steering error handling.

R also includes several advanced debugging tools that can be very helpful for quickly and efficiently locating problems, which will be the focus of this article. To illustrate, we’ll use an example adapted from an excellent paper by Roger D. Peng, and show how these tools work along with some updated ways to interact with them via RStudio. In addition to working with errors, the debugging tools can also be used on warnings by converting them to errors via options(warn = 2).

Read on for a survey of what’s available in R. It’s a lot more than writing a bunch of print statements. H/T R-Bloggers

Comments closed

Power Query: Types from Text

Published 2019-11-20 by Kevin Feasel

Imke Feldmann shares a new M function with us:

Look at the M-code that has been generated in the formula bar: “type text” is in quotes and this makes it a text-string. The function dialogue doesn’t give an option to actually select or enter a type expression. This would be without quotes like so:

MyFunction( type text )

So if I aim to feed my function a text value to dynamically create a type from it, I need a function that returns a type and accepts a text value to identify the actual type.

Click through for Imke’s version as well as a second version in the comments.

Comments closed

Character Generation with T-SQL

Published 2019-11-20 by Kevin Feasel

Bill Fellows shows off what you can do with character generation in T-SQL:

The mod or modulus function will return the remainder after division. Modding a value is a handy way to constrain a value between 0 and an upper threshold. In this case, if I modded any number by 26 (because there are 26 characters in the English alphabet), I’ll get 0 to 25 as my result.

Knowing that the modulus function will give me 0 to 25 and knowing that my target character range starts at 65, I could use the previous expression to print any number’s ascii value like SELECT CHAR((2147483625 % 26) + 65) AS StillB;. Break that apart, we do the modulus, %, which gives us the value of 1 which we then add to the starting offset (65).

He also provides a DB Fiddle for it.

Comments closed

Named Volumes in Docker with SQL Server 2019

Published 2019-11-20 by Kevin Feasel

Andrew Pruski takes us through one of the biggest changes with SQL Server 2019 in containers:

I’ve seen a few people online asking how to use docker named volumes with the new SQL Server 2019 RTM images. Microsoft changed the way SQL runs within a container for the RTM versions, SQL no longer runs as the root user.

This is a good thing but does throw up some issues when mounting volumes to create databases on.

Let’s run through what the issue is and how to overcome it.

Click through to see what you need to add to your DOCKERFILE to get things working.

Comments closed

Installing Power BI Gateway

Published 2019-11-18 by Kevin Feasel

Paulina Nowinska shows how to install the Power BI Gateway in its two separate modes:

This On-premises was created for a multi-developer environment. Here multiple people can work on the same Data Gateway if the administrator authorized them before. With this Data Gateway, you will have much more fun than Personal Mode. Why? Because of on-premises support not only Power BI just like his brother Personal Mode. Here you can provide quick and secure data transfer between data which is not in the cloud and Microsoft cloud services: PowerApps, Microsoft Flow, Azure Analysis Services, and Logic Apps and of course Power BI. Depending on your needs, you can choose one of them.

Click through for step-by-step instructions on both techniques.

Comments closed