Press "Enter" to skip to content

Author: Kevin Feasel

Big Data Clusters and Fixed IP Addresses

Published 2020-02-04 by Kevin Feasel

Denny Cherry warns you about Big Data Clusters and keeping a particular IP address:

No problem, we just added in the correct IP range to the possible addresses for the vNet, added a new Subnet and moved the VMs over to the new subnet (which caused the VMs to reboot, but that was expected).

It turns on that BDC in SQL Server 2019 doesn’t like having the IPs changed for the aks nodes.  The problem stems from the fact that BDC is generating its certificates off of the IP address of the node, so if the IP address of the node changes (even if you are using DHCP for on-prem nodes and DHCP gives you a new IP address) your BDC won’t respond.

Read on for your three possible solutions.

Comments closed

Generating Fake Data with R

Published 2020-02-03 by Kevin Feasel

Dave Mason takes a look at generating fake PII in R:

I’ve been thinking about R and how it can be used by developers, DBAs, and other SQL Server professionals that aren’t data scientists per se. A recent article about generating a data set of fake transactional data got me thinking about this again and I wondered, can R be used to obfuscate PII data?

In a word, yes. Well, mostly. (More on this in a bit.) As with anything R-related, there are probably multiple packages that are useful for any given task. For this one, I’ll focus on the “generator” package.

Click through to see what it does and Dave’s thoughts on the topic. It would also be possible to generate fake data in R by hitting a web API like Daniel Hutmacher’s service.

Comments closed

Fun with Palindromic Dates

Published 2020-02-03 by Kevin Feasel

Tomaz Kastrun has a bit of fun with the date February 2, 2020:

As of writing this blog-post, today is February 2nd, 2020. Or as I would say it, 2nd of February, 2020. There is nothing magical about it, it is just a sequence of numbers. On a boring Sunday evening, what could be more thrilling to look into this little bit further 🙂

Let’s kick R Studio and start writing a lot of useless stuff.

Tomaz also compares US versus EU palindromic dates and visualizes the different distributions.

Comments closed

Updating the Powershell Kernel in Azure Data Studio Notebooks

Published 2020-02-03 by Kevin Feasel

Bob Pusateri has a two-parter on Powershell notebooks. First up is the problem:

PowerShell Notebooks are a great new feature in Azure Data Studio, first becoming available in the November 2019 release. Like SQL notebooks, PowerShell notebooks are based on Jupyter Notebooks format, which are interactive documents containing text and executable code blocks.

Having some working PowerShell code that I wanted to share along with explanations and examples, I created a PowerShell Notebook. The only problem was my functions would never initialize. Actually they would never stop initializing – I would run the cell they were defined in, and it would just keep running forever.

And then Bob has the solution:

It turns out I did not have the latest version of the PowerShell Kernel running on my machine. The latest version is currently 0.1.3, and I had 0.1.2. Upgrading appears to have solved this issue for me – yay!

This solution also raises the issue that there is no notification from Azure Data Studio that a PowerShell Kernel exists or is in need of updating. I (and probably others) will just believe that as long as Azure Data Studio is up to date, we’re good to go. So how does one update their PowerShell kernel? Well, it’s simple, but not intuitive.

Read on to see how.

Comments closed

DAX and Leap Days

Published 2020-02-03 by Kevin Feasel

Erik Svensen shows us what happens in DAX functions when February 29th rolls around:

Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.

Well – in fact there was a few surprises depending on what you select from you date table.

Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.

Click through for those surprises. They aren’t bugs, as the functions have to do something and the possible set of valid somethings is greater than one.

Comments closed

Creating a New Database in Azure Data Studio

Published 2020-02-03 by Kevin Feasel

Dave Bland shows how you can create a new database using Azure Data Studio:

Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools.  What is not an option in both tools is to right click and to go “New Database”.  This has been in SSMS for many years, however it is NOT present in Azure Data Studio.

If you really do want to create a new database using a GUI in Azure Data Studio, Dave shows you the extension you need. It’s not as fully-featured as the wizard in Management Studio, so it would make sense to understand what that wizard is doing and learn the T-SQL yourself.

Comments closed

Queueing Event Notifications with Service Broker

Published 2020-02-03 by Kevin Feasel

Max Vernon ties event notifications to Service Broker:

My previous post shows how to configure an Event Notification to fire whenever a login event occurs. The post uses Service Broker to receive those Event Notifications into a queue, which is then processed by a stored procedure and saved into a standard SQL Server database. This post provides a quick+dirty VB.Net command line monitor that shows how full a Service Broker queue is.

The following code should be pasted into a blank Visual Studio VB.Net console project. It is trivially easy to translate this into C#, but I like VB – what can I say.

Click through for the code. No F# translation from me, however, as I am lazy.

Comments closed

Resource Limitations with Azure Data Factory

Published 2020-02-03 by Kevin Feasel

Paul Andrew has a public service announcement for us:

As far as I can tell Microsoft do an excellent job at managing data centre capacity so I completely understand the reason for having limitations on resources in place. There is no such thing as a limitless cloud platform.

Note; in a lot of cases (as you’ll see in the below table for Data Factory) the MAX limitations are only soft restrictions that can easily be lifted via a support ticket. Please check before raising alerts and project risks.

Click through for the limits, and “contact support” definitely is good advice if you’re expecting to push past those limits.

Comments closed

Data Lake File Formats and Security

Published 2020-01-31 by Kevin Feasel

Ashish Kumar and Jorge Villamariona continue a series on data lakes:

People from a traditional RDBMS background are often surprised at the extraordinary amount of control that data lake architects have over how datasets can be stored. Data Lake Architects, as opposed to the Relational Database Administrators, get to determine an array of elements such as file sizes, type of storage (row vs. columnar), degrees of compression, indexing, schemas, and block sizes. These are related to the big data oriented ecosystem of formats commonly used for storing and accessing information in a data lake.

It is a bit of a different world and it comes with trade-offs. The whole thing is worth reading.

Comments closed