Press "Enter" to skip to content

Curated SQL Posts

Why the DBA is Important

Published 2019-11-21 by Kevin Feasel

Melody Zacharias takes us through five areas where DBAs are important in the SQL Server 2019 world:

Databases are the beating heart of digital transformation. Businesses increasingly realize that having a unified view gives them a competitive advantage in a world where data is king. The task of breaking down those silos will fall to highly skilled DBAs using cool new technologies such as PolyBase [https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15]. Although it was introduced in SQL Server 2016, PolyBase got a whole lot more interesting in SQL Server 2019 with the ability to query external SQL Server, Oracle, Teradata, and MongoDB using T-SQL. Our world just got a whole lot bigger!

Read on for the full set of reasons. My agreement with this comes with one caveat: DBAs are important insasmuch as they are willing to grow, try new things, and develop skills. If you’re a stodgy type who hasn’t learned a thing since SQL Server 2008, you’ve got a shelf life.

Comments closed

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

Creating a Failover Cluster Instance with Shared Storage

Published 2019-11-21 by Kevin Feasel

Ryan Adams wraps up a video series on setting up a SQL Server lab environment:

You are going to create a SQL Server Failover Cluster Instance in Part 4 of our series on how to build a SQL Cluster Lab. The FCI will only be installed on Node1 and Node2. FCIs require shared storage so you will make your domain controller an iSCSI target. Last you will create your FCI using the iSCSI drives you presented to the cluster. 

Click through for links to the entire series.

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

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

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

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

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

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