Press "Enter" to skip to content

Day: October 28, 2019

Azure AD Credential Passthrough and Databricks

Published 2019-10-28 by Kevin Feasel

Anna Shrestinian, et al, explain how Azure Databricks enables Azure Active Directory credential passthrough when working with Azure Data Lake Storage Gen2:

Azure Data Lake Storage (ADLS) Gen2, which became generally available earlier this year, is quickly becoming the standard for data storage in Azure for analytics consumption. ADLS Gen2 enables a hierarchical file system that extends Azure Blob Storage capabilities and provides enhanced manageability, security and performance.

The hierarchical file system provides granular access control to ADLS Gen2. Role-based access control (RBAC) could be used to grant role assignments to top-level resources and POSIX compliant access control lists  (ACLs) allow for finer grain permissions at the folder and file level. These features allow users to securely access their data within Azure Databricks using the Azure Blob File System (ABFS) driver, which is built into the Databricks Runtime.

There are some tradeoffs involved, particularly around using High Concurrency clusters (or limiting yourself to one user account), but it’s a nice bit of added value when you’re a heavy Azure user.

Comments closed

Re-Introducing rquery

Published 2019-10-28 by Kevin Feasel

John Mount has a new introduction to rquery:

rquery is a data wrangling system designed to express complex data manipulation as a series of simple data transforms. This is in the spirit of R’s base::transform(), or dplyr’s dplyr::mutate() and uses a pipe in the style popularized in R with magrittr. The operators themselves follow the selections in Codd’s relational algebra, with the addition of the traditional SQL “window functions.” More on the background and context of rquery can be found here.

The R/rquery version of this introduction is here, and the Python/data_algebra version of this introduction is here.

Check it out.

Comments closed

What’s New with Standard Edition

Published 2019-10-28 by Kevin Feasel

Niko Neugebauer is jazzed about SQL Server 2019 Standard Edition:

The documentation has been released on the Editions & Features support in Sql Server 2019 and there are huge news that will get a good number of Standard Editions users excited.

The thing that should make every single user of the Sql Server 2019 Standard Edition jump is the presence of TDE (Transparent Database Encryption), the feature that

– every single responsible company would love to be able to use to secure their data
– that every single DBA, Developer & IT Professional kept asking Microsoft to include for so many years
– was already there since Azure SQL Database has it by default a couple of years even for the basic edition

I am beyond happy with this news. Grateful that Microsoft has listened to the common sense voice and made a security feature available for the paying customers, giving them a little bit more possibility to be conformant to such demanding norms as GDPR.

Aside from that, Niko looks at several new features which will be available in Standard Edition, as well as a few Enterprise-only features.

Comments closed

Emergency Mode in SQL Server

Published 2019-10-28 by Kevin Feasel

Paul Randal answers a reader question:

I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.

All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.

Read on for an explanation of what emergency mode is and why we need it to run CHECKDB repair operations.

Comments closed

skip-2.0 and SQL Server Security

Published 2019-10-28 by Kevin Feasel

K. Brian Kelley has the lowdown on skip-2.0:

Problem
I’ve read recently that there’s a new piece of malware that’s been named skip-2.0 and it targets SQL Server. What exactly is it, where did it come from, and how do I protect myself against it?

Solution
This new piece of malware, skip-2.0, does target SQL Server. Specifically, it targets SQL Server versions 11 and 12, which correspond to the names SQL Server 2012 and SQL Server 2014 respectively. Therefore, if you’re only running SQL Server 2016 or higher, you’re not affected by skip-2.0 (yet another reason to upgrade).

Read on to learn how it works and how you can protect against it.

Comments closed

Speeding Up Excel Pivot Table Performance

Published 2019-10-28 by Kevin Feasel

Chris Webb shows how you can improve performance of Excel pivot tables hitting Analysis Services Multidimensional models:

Back in 2016 I wrote the following blog post about changes to the way Excel 365 generated MDX queries for PivotTables connected to Analysis Services, Power Pivot/the Excel Data Model and Power BI datasets:

https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/

I know it sounds boring and not something you need to worry about but trust me, this is important – these changes solved the vast majority of Excel PivotTable performance problems that I encountered when I was a consultant so you should read the above post before continuing.

Unfortunately, earlier this year these changes had to be partially rolled back because in some rare cases the queries generated returned incorrect results; this means that you may find that values for subtotals and grand totals are again being returned even when they aren’t being displayed. The good news is that you should still be able to get the improved performance with a few minor tweaks.

Read on to see what those tweaks are.

Comments closed

Against Premature Re-Architecture

Published 2019-10-28 by Kevin Feasel

Cyndi Johnson has a good rant:

One of my biggest pet peeves in software development is the compulsion that so many developers have to rip up the foundation and completely build something over again, pretty much from scratch.

I’ve been that developer plenty of times. It’s easy to walk in, see that there are some problems, and want to raze everything. Sometimes that’s a reasonable answer, but every apparent mismatch or hack was put in to solve a particular business rule, many of which are lost to the mists of time. Burning down and starting over loses a lot of that information, so rebuilding is something you do with caution.

Comments closed