Press "Enter" to skip to content

Author: Kevin Feasel

SQL Server 2019 and Columnstore Cleanup Issues

Published 2020-04-27 by Kevin Feasel

Taryn Pratt shares an issue with columnstore cleanup in SQL Server 2019:

The output of sp_who2 was repeatedly showing GHOST CLEANUP and CREATE INDEX. Over and over and over again. To be clear, I’m not a clustered columnstore expert, I know enough to be able to maintain them as needed. I went to Twitter and mentioned what I was seeing. I was advised by @sqL_handLe to try trace flag 661 which disables the ghost record removal process, and by Joe Obbish via Erik Darling to enable trace flag 634 to disable the tuple mover background task.

Initially, we enabled trace flag 634, but the logs continued to grow. We disabled trace flag 634. Then we enabled trace flag 661, and the logs continued to grow, so we disabled it. Finally, we tried enabling both of the trace flags. The big jumps stopped, but we now had about 400GB of logs that needed to be written to the reporting cluster before we could perform the failover.

While the logs were exploding we wondered if whatever was happening might have been caused by the deletions we did in early February. But why would they be triggered by the upgrade to SQL Server 2019?

Read the whole thing if you’re looking at a migration to 2019.

Comments closed

Finding Your Azure Database Pricing Tier

Published 2020-04-27 by Kevin Feasel

Kenneth Fisher needs to know a server’s current pricing tier:

I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.

Click through to see the answer.

Comments closed

Aggregations in Power BI Desktop

Published 2020-04-27 by Kevin Feasel

Jeroen ter Heerdt tries out aggregations in Power BI Desktop:

Aggregations bring me back to the good old SSAS Multidimensional days. The days that I invariably built the aggregations tree the wrong way around, SSAS would complain, I would scream and eventually give in.

You can imagine that I was curious but skeptical when I tried aggregations in Power BI Desktop. I was afraid of ending up in the same hate-but-need relationship that I had with SSAS multidimensional when it came to aggregations.

The good news? It is not like that. At all. Once you have aggregations working, they are great.

The not so good news? It took me longer than I am willing to admit getting them working – primarily due to data types and creation of the aggregated table. More details in this blog post.

Read on for the full story.

Comments closed

Cleaning Up SQL Server Backup History

Published 2020-04-27 by Kevin Feasel

Stuart Moore has a public service announcement for us:

From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.

In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should

Read on for Stuart’s thoughts on the topic.

Comments closed

Sentiment Analysis with Power BI

Published 2020-04-27 by Kevin Feasel

Teo Lachev takes us through two options available for sentiment analysis with Power BI:

Cognitive Services is an Azure PaaS cloud service that supports text analytics and image recognition. It’s automatically included in Power BI Premium or Embedded capacities (make sure that AI workloads are enabled in the capacity settings). If you organization doesn’t have Power BI Premium or Embedded, you can provision Cognitive Services in Azure (requires an Azure subscription) and then write a custom Power Query function to invoke its APIs, as demonstrated by this tutorial. If you provision Cognitive Services outside Power BI Premium,  you’ll be charged per transaction. In the case of Power BI, the number of transactions equates to the number of rows in your table. So, if you refresh five times a table with 1,000 rows and calculate the sentiment polarity score for each row, you’ll be charged for 5,000 transactions.

Read on for the full report on each option.

Comments closed

Understanding kubeadm Authentication and Authorization

Published 2020-04-24 by Kevin Feasel

Praveen Sripati takes us through the way that kubeadm handles authorization and authentication for Kubernetes processes:

In the above K8S cluster, the default user (kubernetes-admin) created during the cluster setup has admin privileges to the cluster. This time I was curious on how the authentication and authorization work in K8S for this user to have full access to the cluster. This will enable me to be create additional users with different privileges, authentication and authorization mechanisms. It took me some time to get my mind/thoughts around it, but it’s all interesting. This blog is all about the same.

Note that the cluster has been setup using kubeadm, for kops and other the below varies a little bit. And also, kubeadm cluster setup default used X509 certificates for authentication. Authentication Providers are not built into K8S and so has to be integrated with external systems like Google Accounts, Active Directory, LDAP etc.

Click through to see what Praveen learned in the process.

Comments closed

Cloudera Data Platform High Availability Options

Published 2020-04-24 by Kevin Feasel

Liliana Kadar, et al, explain what options DBAs have around high availability when working with the Cloudera Data Platform:

All Data Replication (DR) combinations are supported:

– hot-hot
– hot -warm
– hot-cold
– hot-warm-cold
– other permutations of these configurations

The direction of replication can be uni-directional, bi-directional or multi-directional replication through advanced geo-distributed topologies. 

It’s interesting to watch the evolution of Hadoop administration, going from “the cluster is our HA option” to having realistic plans if problems occur. The post doesn’t really cover DR, where the evolution has been greater.

Comments closed

Running Multiple Microsoft Teams Orgs Concurrently

Published 2020-04-24 by Kevin Feasel

Mark Lelijveld has a workaround for a really annoying problem:

I do not think that it is a surprise for anybody that working with Microsoft Teams and different organizations is a big hassle. The multi-tenant setup is not very user friendly yet. On the right top in Teams you can switch organizations, but that only works if you are signing in with the same username and password to another organization. In other words, your account needs to be added as a guest account to the Active Directory in the other organization.

For a lot of us, working as a consultant for different clients, this is not our day-to-day practice. We have a separate Microsoft account for every client, which requires signing out and signing in again. If case you have single sign-on enabled, it can become even a bigger struggle to sign in with a different account than your organization one. As a solution, you might be running Teams in a browser window, which is a first step, but it is still hard to keep up with everything that is happening in Teams.

Well… not for long. With the few little tricks I describe below, you can run multiple Teams environments at the same time by using the new Edge Chromium browser and also customize it so you directly see which Teams environment you opened.

I appreciate that Marc has a very useful workaround. I hate that Marc has to spend the time coming up with a workaround in the first place. I definitely prefer the Slack model, where it doesn’t matter which account I’m using—I see all of my workspaces in one go, regardless of the e-mail address I’ve used.

Comments closed

Poor SQL Formatter’s SSMS Plugin

Published 2020-04-24 by Kevin Feasel

Dave Bland shows us the Poor SQL formatter for SQL Server Management Studio:

So what is PoorSQL Formatter?  Well, as you probably know, formatting is very important when writing T-SQL code.  While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy.  It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices.  The best part, it will do the formatting in seconds.

I’ve used that website many a time. It’s not as detailed as I want it to be, but if you just need a quick and dirty way to format a query so that it’s understandable, this does the trick.

Comments closed