Press "Enter" to skip to content

Author: Kevin Feasel

STONITH Resources for Pacemaker Clusters

Published 2022-11-04 by Kevin Feasel

Andrew Pruski picks up Chekov’s Gun:

Recently I had to create another pacemaker cluster, this time on-premises using VMWare virtual machines. The steps to create the pacemaker cluster and deploy an availability group where pretty much the same as in my original post (minus any Azure marlarkey) but one step was different, creating the STONITH resource.

A STONITH resource is needed in a pacemaker cluster as this is what prevents the dreaded split brain scenario…two nodes thinking that they’re the primary node. If the resource detects a failed node in the cluster it’ll restart that node, hopefully allowing it to come up in the correct state.

Read on to see how Andrew did it.

Comments closed

MAXDOP Calculation Discrepancy

Published 2022-11-04 by Kevin Feasel

Brent Ozar does the math:

In this case, the SQL Server has multiple NUMA nodes, with greater than 16 logical processors per node – that’s the last line of the screenshot. In that line, Microsoft says MAXDOP should be half of the number of logical processors with a max of 16 – so 16.

But it’s recommending 8. Hmm.

Read on for the answer.

Comments closed

GitHub CI/CD for Synapse Link for SQL Server 2022

Published 2022-11-03 by Kevin Feasel

Kevin Chant does a bit of CI/CD:

In this post I want to show how a GitHub CI/CD experience for Azure Synapse Link for SQL Server 2022 can look. Which uses GitHub Actions. Including how to automatically stop and start it in the pipeline.

In my last post I showed a complete CI/CD experience for Azure Synapse Link for SQL Server 2022 using Azure DevOps.

With this in mind, in this post I show an alternative GitHub CI/CD experience for Azure Synapse Link for SQL Server 2022 which uses GitHub Actions. Which includes automatically stopping the link before the database update and starting it again after the update has completed.

Read on to learn how.

Comments closed

Archival Tables in SQL Server

Published 2022-11-03 by Kevin Feasel

Aaron Bertrand starts a new series:

We all have one: the table that grows forever. Maybe it contains chat messages, post comments, or simple web traffic. Eventually, the table gets large enough that it becomes problematic – for example, users will notice that searches or updates take longer and longer as this massive, ever-growing table is scanned.

People often deal with this by archiving older data into a separate table. In this tip series, I’ll describe an archive table, explain why that solution carries its own set of problems, and show other potential ways to deal with data that grows indefinitely.

This is where we say, “Ah, if only Stretch DB had been priced approximately 1/100th of what it really was.” Stretch DB also had its own problems—especially if you ever needed to change the large table’s schema—but stay tuned for Aaron’s answers.

Comments closed

SQL Server: Learning from Query Store

Published 2022-11-03 by Kevin Feasel

Grant Fritchey explains a second-order effect of Query Store:

I may have occasionally talked about the importance of Query Store, but today I want to emphasize just how much Microsoft is weaponizing query store.

Of course, I don’t mean they’re creating the Death Star or something, I simply mean they’re taking the information that Query Store gathers and using that to enable a number of new performance enhancements within SQL Server.

Grant shows several examples of how SQL Server can take data in Query Store and use it to make queries in that database faster. It took a few versions of SQL Server but we’re getting to see some of the long-run promise of the feature, now that most of the bugs have been ironed out and the SQL Server development team has had enough time to implement this functionality.

Comments closed

Refreshing Excel Data Model Tables via VBA

Published 2022-11-03 by Kevin Feasel

Chris Webb does some work in Excel:

Sometimes, when you’re analysing data, you need to be able to change variables and see what the impact is: for example you might want to see what your profit margin looks like if tax rates are set at different levels. Power BI’s what-if parameter feature can do this but it has the limitation that you can’t enter any value you like – you need to create a table containing all possible variable values in advance. The advantage the Excel Data Model/Power Pivot has over Power BI for this type of what-if analysis is that you have the Excel worksheet available, which is not only a place to display your report but which can also be used as a data source for tables in the Excel Data Model, making it easy for users to enter whatever variable they want. Up until recently, though, I assumed that if you were importing data from the worksheet into the Excel Data Model you would need to take some form of manual action, for example clicking a refresh button, to load the new data from the worksheet into the Excel Data Model when the data there changed. In this blog post I’ll show you how you can use VBA to solve this problem and build an elegant what-if analysis solution in Excel where no extra clicks are needed.

Read on for that demonstration.

Comments closed

NT AUTHORITY\ANONYMOUS LOGON Errors and How to Fix Them

Published 2022-11-03 by Kevin Feasel

Eitan Blumin reminds me of the bad old days:

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”. This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

This issue is often called “double-hop pass-through authentication”, also known as “Kerberos delegation“, which I’ll try to illustrate with the following diagram:

Eitan provides a helpful step-by-step guide to understanding not just how to fix the problem but also what concepts like SPNs really do.

Comments closed

Learning to Count in R

Published 2022-11-02 by Kevin Feasel

Jerry Tuttle does the math:

You would think base R would have a count function such as count(df$Team) and count(df$Team == “NYY”) but this gives the error “could not find function ‘count’”. Base R does not have a count function. Base R has at last four ways to perform a count:

Click through to learn the different ways available to you, including those built into R itself as well as other packages like dplyr. H/T R-Bloggers.

Comments closed

Lessons Learned from Index Tuning

Published 2022-11-02 by Kevin Feasel

Lee Markum has seven lessons for us:

SQL Server indexing basics are critical to query and server performance. Resources, like CPU and disk, are affected by the indexes that you have, or the ones you’re missing.

In the StackOverflow2013 database we’re going to look at Badges and users. Specifically, I want to start by seeing what badges a user has and when that user received them. Some badges, because of the type of badge it is, can be awarded more than once.

Click through for a demonstration.

Comments closed