Press "Enter" to skip to content

Author: Kevin Feasel

Another Way to Upgrade SQL Server 2017 Containers to 2019

Published 2019-11-25 by Kevin Feasel

Anthony Nocentino gives us another option for upgrading SQL Server on containers:

Yesterday in this post I described a method to correct permissions when upgrading a SQL Server 2017 container using Data Volumes to 2019’s non-root container on implementations that use the Moby or HyperKit VM. My friend Steve Jones’ on Twitter wondered if you could do this in one step by attaching a shell (bash) in the 2017 container prior to shutdown. Absolutely…let’s walk through that here in this post.  I opted to use an intermediate container in the prior post out of an abundance of caution so that I was not changing permissions on the SQL Server instance directory and all of the data files while they were in use. Technically this is a-ok, but again…just being paranoid there.

Click through for that process. The good news is that with upgrading from SQL Server 2019 to SQL Server 202x, I wouldn’t expect that we’d need to go through this again, as the process would stay non-root forevermore.

Comments closed

Power BI Premium Capacity Testing

Published 2019-11-25 by Kevin Feasel

Matthew Roche announces an interesting tool:

This new tool was included as part of the BRK2046  session on Power BI Premium at MBAS. The whole session is valuable, but the tool itself comes in around the the 32 minute mark. There’s a demo at the 37 minute mark. The tool is available today on github.

This tool will help Power BI Premium customers better plan for how their specific workloads (reports, dashboards, datasets, dataflows, and patterns of access) will perform on a given Premium capacity.

Click through for instructions and a description of how it works.

Comments closed

Partition Switching to Make Table Changes

Published 2019-11-25 by Kevin Feasel

Daniel Hutmacher shows a couple things you can change with near-zero downtime using partition switching:

Look, I’m not saying that you’re the type that would make a change in production while users are working.

But suppose that you would want to add an identity column to dbo.Demo, and change the clustered index to include that identity column, and make the index unique? Because it’s the table’s clustered index, you’re effectively talking about rebuilding the table (remember, the clustered index is the table), which involves reorganizing all of the rows into a new b-tree structure. While SQL Server is busy doing that, nobody will be able to read the contents of the table.

Daniel mentions a read-only table, though you could also do this with a read-write table as long as you have triggers to keep the two tables in sync until go time. That adds to the complexity, but it is an option if you need it.

Comments closed

MSDTC and the Firewall

Published 2019-11-25 by Kevin Feasel

Josh Smith shows how you can enable MSDTC in a buttoned-down environment:

This is just a fancy way of saying you need to be better friends with who ever is managing your enterprise firewall. I hadn’t had to touch the DTC until a recent vendor insisted their application wouldn’t work without it (despite their only having a single data store). The MSDTC was developed to coordinate transactions that would span multiple machines and was originally introduced in SQL Server 2000.

In theory it’s not super complicated: just enable the DTC service/communication on the servers in question and turn on some built in firewall rules on the servers right? Almost.

Read on for the full set of instructions.

Comments closed

Columnar File Formats in Hadoop

Published 2019-11-22 by Kevin Feasel

Matthew Rathbone gives us an overview of the benefits behind the ORC and Parquet file formats:

People throw this term around a lot, but I don’t think it is always clear exactly what this means in practice.

The textbook definition is that columnar file formats store data by column, not by row. CSV, TSV, JSON, and Avro, are traditional row-based file formats. Parquet, and ORC file are columnar file formats.

Read on for a comparison and example. In the SQL Server world, think columnstore versus rowstore indexes and you won’t be too far off.

Comments closed

Aggregations in Power BI

Published 2019-11-22 by Kevin Feasel

Shabnam Watson takes us through aggregations in Power BI:

In Power BI, Aggregations start as tables just like any other table in a model. They can be based off a view or table in the source database, or created in Power BI with Power Query. They can be in Import or Direct Query storage mode.

Once in the model, these tables can be configured so that the engine can use them instead of a detail table to answer queries when possible. The process of creating and configuring aggregations in Power BI is significantly easier than the process of creating aggregations in SSAS multidimensional.

Once an aggregation table is configured, it becomes hidden from end users. Report developers and end users don’t know that it exists and don’t need to change anything in how they query the dataset.

This was one of the key benefits to a multidimensional model. Shabnam has an excellent, detailed article here, so give it a read if you are a Power BI developer.

Comments closed

Decomposition Trees in Power BI

Published 2019-11-22 by Kevin Feasel

Tomaz Kastrun takes us through a new visual in Power BI:

Decomposition tree is a data presentation of slicing and dicing of selected metrics based on the attributes of these metrics or with combination of other metrics. Another great aspect of this visual is to analyze the selected variable with many metrics or attributes (dimensions) as the same time.

It’s not the type of visual I’d want to see on a dashboard, but I can see it as quite useful in exploratory data analysis.

Comments closed

Why the DBA is Important

Published 2019-11-21 by Kevin Feasel

Melody Zacharias takes us through five areas where DBAs are important in the SQL Server 2019 world:

Databases are the beating heart of digital transformation. Businesses increasingly realize that having a unified view gives them a competitive advantage in a world where data is king. The task of breaking down those silos will fall to highly skilled DBAs using cool new technologies such as PolyBase [https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15]. Although it was introduced in SQL Server 2016, PolyBase got a whole lot more interesting in SQL Server 2019 with the ability to query external SQL Server, Oracle, Teradata, and MongoDB using T-SQL. Our world just got a whole lot bigger!

Read on for the full set of reasons. My agreement with this comes with one caveat: DBAs are important insasmuch as they are willing to grow, try new things, and develop skills. If you’re a stodgy type who hasn’t learned a thing since SQL Server 2008, you’ve got a shelf life.

Comments closed