Press "Enter" to skip to content

Curated SQL Posts

sqltxls Now Supported Cross-Platform

Published 2020-03-26 by Kevin Feasel

Randolph West has made sql2xls work on Linux and MacOS:

Last year I released sql2xls, a free open-source tool which lets you throw a bunch of scripts into a folder, run them automatically against SQL Server and get the results back in a nicely-formatted Excel file, one tab per script.

A year later, I am happy to announce that the tool now runs cross-platform. You can compile and run it with .NET Core 3.1 LTS to run on Windows, macOS, and Linux. It uses the new Microsoft.Data.SqlClient library from NuGet, as well as the latest beta of ClosedXML.

Read on for a few more tips and go check the project out.

Comments closed

Getting SQL Server with Current Linux Distributions

Published 2020-03-26 by Kevin Feasel

Tejas Shah announces the availability of SQL Server 2019 on the latest long-term releases of Ubuntu, Red Hat, and SuSE:

SQL Server team has been working diligently in adding support for current Linux distributions. To this end, the team announced support for SQL Server 2019 on RHEL 8.0, Ubuntu 18.04 and SLES 12 SP5 within last quarter.

The team is glad to announce that the Azure marketplace PAYG (Pay As You Go) images for SQL Server 2019 on RHEL 8.0, Ubuntu 18.04 and SLES 12 SP5 have been made generally available. You can deploy these images to get the latest of both SQL Server 2019 functionality and operating system improvements.

With Ubuntu 20.04 coming out soon, it’ll be interesting to see when that officially becomes supported.

Comments closed

Authentication in Hadoop with Apache Ozone

Published 2020-03-25 by Kevin Feasel

Xiaoyu Yao explains how we can use Apache Ozone to perform service account authentication for a Hadoop cluster:

Like Hadoop delegation tokens, Ozone security token has a token identifier along with a signed signature from the issuer. Ozone manager issues delegation token and block tokens for users or client applications authenticated with Kerberos. The signature of the token can be validated by token validators to verify the identity of the issuer. This way, a valid token holder can use the token to perform operations against the cluster services as if they have Kerberos tickets of the issuer. 

Read on for the high-level overview.

Comments closed

Figuring out How a Plan was Forced

Published 2020-03-25 by Kevin Feasel

Erin Stellato wants to know whether a DBA forced a plan or SQL Server did automatically:

If you use Automatic Plan Correction, and thus also Query Store, you may wonder how was a plan forced: manually or automatically with APC?  The type of forced plan is tracked in sys.query_store_plan, and you can use this simple query to determine how a plan was forced:

Click through for a simple query, as well as a more complex form which gives you a bit more info.

Comments closed

Striping SQL Server Database Backups

Published 2020-03-25 by Kevin Feasel

John McCormack explains why and how to stripe database backups:

First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.

The biggest reason for me is the time savings. I’ve done a few performance analyses and in my various circumstances, the optimal number of files to create (for minimizing full backup time) has consistently been greater than 1, even when I’m writing all of the files to the same underlying drive. The only downside I see is having to manage more files.

Comments closed

Upgrading SQL Server Reporting Services to 2017

Published 2020-03-25 by Kevin Feasel

Alexandre Hamel walks us through the new process for upgrading SQL Server Reporting Services:

In the past, we could run the SQL installer to do an in-place upgrade of SQL server including the SSRS instance to a newer version. As of 2017, SSRS is a separate install from SQL server, so this is no longer possible. In fact, if you do an in-place upgrade of SQL 2014 to 2017 for example, you will see a warning that SSRS will be uninstalled. Before proceeding with the SQL upgrade, follow these steps to upgrade the SSRS instance.

It’s not as easy as it was before, but Alexandre takes us through the step-by-step process and even includes some notes on how to roll back your upgrade attempt if necessary.

Comments closed

Storing Streaming Data in Azure Data Lake

Published 2020-03-25 by Kevin Feasel

Jesse Gorter takes us through writing streaming data from Event Hubs into Azure Data Lake Storage:

In my previous blog I showed how you can stream Twitter data to an Event Hub and stream the data to a Power BI live dashboard. In this post, I am going to show you how to store this data for long term storage. An Event Hub stores your events temporarily. That means it does not store them for later analysis. Say you want to analyze whether negative or positive tweets have an impact on your sales, you would need to store tweets for a historical view.

The question is where to store this data: directly to the datawarehouse, or store it to a data lake? This really depends on the architecture that you want to have. A data lake is often used to store the raw data historically. Is is especially interesting because it allows to store any kind of data, structured or unstructured and it is quite cheap compared to Azure SQL database or Azure SQL datawarehouse. So for that reason, we are going to store it in a data lake.

Jesse walks us through data lake creation and data migration from Event Hubs into a Data Lake Storage container.

Comments closed