A Fine Slice Of SQL Server
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 closedWayne 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 closedGilbert 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 closedMarina 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 closedInes 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 closedImke 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 closedKevin Chant points out a change to Azure Data Studio:
I talked about the using the wizard in the Insiders build to deploy Big Data Cluster in that post. You can read that post in detail here.
Now, as well as deploying Big Data Clusters you can also use the wizard to deploy SQL Server container images locally using Docker.
Read on for notes about the process.
Comments closedAndrew 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 closedBill 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 closedNina Zumel takes us through a pair of techniques for avoiding overfitting:
Cross-validation is relatively computationally expensive; regularization is relatively cheap. Can you mitigate nested model bias by using regularization techniques instead of cross-validation?
The short answer: no, you shouldn’t. But as, we’ve written before, demonstrating this is more memorable than simply saying “Don’t do that.”
Definitely worth the read.
Comments closed