Press "Enter" to skip to content

Curated SQL Posts

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

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

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

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

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

Installing Third-Party WHL Packages in Synapse with DEP

Published 2022-11-02 by Kevin Feasel

Sabyasachi Samaddar walks through what I consider a real difficulty:

It is really challenging when you need to install third-party .whl packages into a DEP-enabled Azure Synapse Spark Instance.

According to the documentation, https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-azure-portal-add-librar… Installing packages from PyPI is not supported within DEP-enabled workspaces. Hence we cannot just upload the .whl packages into the workspace. We need to upload all the dependencies along with the .whl package and it will be an offline installation. Now Synapse spark clusters come with in-built packages and hence we may find some conflicts when we try to install some third-party packages.

Read on to see what you need to do.

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