Press "Enter" to skip to content

Author: Kevin Feasel

Data Lake Tools For VS Code Updated

Published 2017-07-14 by Kevin Feasel

Jenny Jiang announces Azure Data Lake Tools for Visual Studio Code’s July update:

Local Debug enables you to debug your C# code behind, step through the code, and validate your script locally before submitting to ADLA.

  • Use command ADL: Start Local Run Service to start local run service and set a breakpoint in your code behind, then click command ADL: Local Debug to start local debug service. You can debug through the debug console and view parameter, variable, and call stack information.

Click through to see the other improvements.

Comments closed

Cloudera Enterprise 5.12 GA

Published 2017-07-14 by Kevin Feasel

Fred Koopmans announces that Cloudera Enterprise 5.12 is now generally available:

Data Science & Engineering

  • Cloudera Data Science Workbench enhancements include:

    • GPU Support: Cloudera Data Science Workbench now enables popular deep learning frameworks to run on GPUs, both on-premises and in the cloud.

    • Embedded Web UIs: Users can work with the Apache Spark Web UI for Spark sessions. Other interactive web applications like TensorBoard, Shiny, and Plotly now appear directly in the workbench.

    • Enhanced Job Scheduling: Cloudera Data Science Workbench users can now schedule jobs directly from external schedulers or orchestration systems via the new Jobs API.

Read on for more enhancements.

Comments closed

Using SMO On Linux

Published 2017-07-14 by Kevin Feasel

Richie Lee explains how to get the SQL Tool Service running on Linux:

So, to briefly sum up, to use SMO on Linux, you need to do the following:

  • Install .NET Core 2.0
  • Install PowerShell beta 2
  • Install SQL Tool Service

You can use PowerShell from the Terminal, but I prefer something like an IDE so this is optional:

  • Download Visual Studio Code

  • Install PowerShell plugin

  • Change settings file to point explicitly to PowerShell beta 2.

Read the whole thing.

Comments closed

TDE + AG = Higher CPU Utilization

Published 2017-07-14 by Kevin Feasel

Ginger Keys has an analysis stress testing CPU load when Transparent Data Encryption is on and a database is in an Availability Group:

Microsoft says that turning on TDE (Transparent Data Encryption) for a database will result in a 2-4% performance penalty, which is actually not too bad given the benefits of having your data more secure. There is even more of a performance hit when enabling cell level or column level encryption. When encrypting any of your databases, keep in mind that the tempdb database will also be encrypted. This could have a performance impact on your other non-encrypted databases on the same instance.

In a previous post I demonstrated how to add an encrypted database to an AlwaysOn group in SQL2016. In this article I will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group compared to the same database not-encrypted.

The results aren’t surprising, though the magnitude of the results might be.

Comments closed

SSMS Default Reports

Published 2017-07-14 by Kevin Feasel

Kenneth Fisher talks about the default reports built into Management Studio:

As DBAs our stock in trade is information and there is certainly an impressive amount available. The diagnostic views are the most common place to get the information we need but every now and again it’s nice to get an organized/pretty view. To that end, you can write your own reports or you can use the default reports that Microsoft makes available through SSMS. There are reports at the Server, Database and Agent level.

The Disk Usage by Table report is on my go-to list.

Comments closed

Grooming The Error Log

Published 2017-07-14 by Kevin Feasel

Mark Wilkinson explains how to keep your SQL Server error logs in check:

We typically think of error logs as somewhere to go to find issues, but what if your error logs ARE the issue? Like most anything else in SQL Server, if you neglect your error logs you can run into trouble. Even on a low-traffic SQL Server instance, a bad piece of code, or a hardware issue, could easily fill your error logs, and with the introduction of Hekaton in SQL Server 2014, the SQL Server error log started getting a lot more data pumped into it than you might have been used to before. What this means for the DBA is that you can quickly start filling your main system drive (if your SQL install and error logs are in the default location) with massive error logs. So what questions should you be answering about error logs to make sure you don’t run into problems?

Read on to learn more.

Comments closed

Wanted: Filtering Power BI Data Views

Published 2017-07-13 by Kevin Feasel

Rob Collie is rabble-rousing and building up a pitchfork mob:

I don’t know how we’ve managed to go this long without the ability to Filter in the Data View:

As simple as it sounds, this is the #1 thing I miss when I leave the Excel Power Pivot environment for Power BI Desktop.

Why do I miss it?  Debugging and Validating, primarily.  When I’m getting results from a measure that I don’t trust, it is super common for me to filter a table in Power Pivot so that I’m just looking at a specific set of rows.  And then I often filter by a second column, and then sometimes a third, in order to see if there are any unexpected surprises in the data.  Scrolling through thousands of rows looking with my eyes just doesn’t cut it.

This is positively crucial, at least for me, and creating a Table or Matrix visualization to perform the same task is such a high-friction alternative that I’m usually tempted to start over in Power Pivot.  Ugh.

In fact this is the overwhelming #1 reason why, if I’m starting a model from scratch, I try to do as much of the work as I can in Power Pivot before converting over to PBIX format.

Rob has provided a link to the Power BI Ideas forum, making it easy to vote this item up.

Comments closed

Better Grouping With dplyr

Published 2017-07-13 by Kevin Feasel

John Mount builds a function to improve upon the group-by to mutate model in dplyr:

The advantages of the shorthand are:

  • The analyst only has to specify the grouping column once.
  • The data (mtcars) enters the pipeline only once.
  • The analyst doesn’t have to start thinking about joins immediately.

Frankly I’ve never liked the shorthand. I feel it is a “magic extra” that a new user would have no way of anticipating from common use of group_by() and summarize(). I very much like the idea of wrapping this important common use case into a single verb. Adjoining “windowed” or group-calculated columns is a common and important step in analysis, and well worth having its own verb.

Below is our attempt at elevating this pattern into a packaged verb.

Click through for the script.  I’d like to see something like this make its way into dplyr.

Comments closed

Azure SQL Database Security Basics

Published 2017-07-13 by Kevin Feasel

Arun Sirpal explains some of the security features exposed in Azure SQL Database:

You do not really have to use all of them, this is down to you and your requirements but at least you have decisions to make. I will mention TDE (Transparent Data Encryption), I know a lot of people will opt for TDE in Azure. The big advantage of TDE in Azure over the earthed flavour is that Microsoft does a lot of the work for you, especially around the key management side of things. Also assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Microsoft will also rotate your certificate at least every 90 days, doing this with a local based SQL Server can be quite manual and fiddly (well I think it is).

Read the whole thing if you’re thinking of moving forward with Azure SQL Database, or if you already have a database up in Azure and haven’t checked the latest offerings yet.

Comments closed

Building Multiple Docker Containers At Once

Published 2017-07-13 by Kevin Feasel

Andrew Pruski shows how to use Docker compose to build multiple containers simultaneously:

As SQL Server people we’re only going to be interested in one application but that doesn’t mean we can’t use compose to our advantage.

What I’m going to do is go through the steps to spin up 5 containers running SQL Server, all listening on different ports with different sa passwords.

Bit of prep before we run any commands. I’m going to create a couple of folders on my C:\ drive that’ll hold the compose and dockerfiles: –

Andrew also explains a couple of common errors as he walks us through the process.

Comments closed