Press "Enter" to skip to content

Category: Administration

SQL Server and the Slow Registry

Published 2022-09-15 by Kevin Feasel

Michael J. Swart diagnoses issues when the Windows registry slows down operations:

I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps.

Read on for some of the various operations which request data from the registry, as well as thoughts from Michael on some of the effects of a slow registry. It sounds like there’s not a whole lot we can do about it and this is rare.

1 Comment

Try out Chocolatey

Published 2022-09-13 by Kevin Feasel

Grant Fritchey makes a recommendation:

Chocolatey, as it says on the web site, is a package manager for Windows. But what does that mean? Basically, a package manager is a central place to install, upgrade and remove software. You get a database of the software you have installed, then it can help you to manage your software. Package managers are built into most operating systems. For example, apt or yum on a Linux box. However, not Windows. That’s where Chocolatey comes in.

I do like Chocolatey a lot as well and not just on VMs that I regularly need to rebuild.

Comments closed

Deploying an Arc-Enabled SQL Managed Instance

Published 2022-09-12 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-enabled data services:

Now that we have our Azure Arc-enabled Data Controller configured and available, we can now deploy our first Arc-enabled SQL Managed Instance into our environment. As previously mentioned depending on the type of configuration required for your environment with your Arc-enabled Data Controller (Directly connected or Indirectly connected modes) this will dictate the approach available for you to setup / configure your Arc-enabled SQL Managed Instance.

Click through for a step-by-step guide.

Comments closed

Creating Server Roles Can Create Logins

Published 2022-09-07 by Kevin Feasel

Kenneth Fisher ran into an interesting issue:

One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).

This time around, it was creating a server role. Read on for Kenneth’s thoughts and how you can avoid accidentally creating logins for individual Active Directory users.

Comments closed

Principal Type not Supported Errors in Azure SQL DB/MI

Published 2022-09-07 by Kevin Feasel

Sabrin Alsahsah takes us through a few common causes of Azure Active Directory errors in Azure SQL Database and SQL Managed Instances:

We received some support cases when customers encounter the error below while trying to add an AAD security group to their Azure SQL Database or Azure SQL managed instance. In this blog article, we will be listing a few points to be checked to troubleshoot this error and can help you to identify the cause.

Msg 33130, Level 16, State 1, Line 1

Principal ‘XXXXXX’ could not be found or this principal type is not supported.

Read on for several reasons why things might be going wrong.

Comments closed

Finding SQL Server Indexes with Unused INCLUDE Columns

Published 2022-09-06 by Kevin Feasel

Dave Mason does some digging:

Periodic index analysis for SQL Server typically involves tasks such as checking for missing/unused/overlapping indexes, checking for heaps that maybe should have been designed with a clustered index, analyzing ROW/PAGE compression, etc. There are numerous DMVs that you can use as a starting point for those tasks. There’s also some good open source tools and scripts that members of the SQL Server community have created and shared. One task that I don’t recall ever seeing was an analysis of INCLUDE columns for nonclustered indexes. What I really wanted to do was to find INCLUDE columns that were never being used, and remove them from index definitions.

Click through to see what Dave learned, as well as the repo containing several useful scripts.

Comments closed

Max Server Memory and AWS

Published 2022-08-26 by Kevin Feasel

Andrea Allred runs into a weird issue on AWS RDS:

We tracked down every job that was touching the server and started to tune it, thinking that was just pushing us over the edge. We worked with AWS and finally one of our engineers noticed that our MAX Server Memory Setting was back at the SQL default. You know that insane default? Yes, it was there. But we had properly set that…three months ago when this stack was put in place.

Click through for the entire story, including symptoms and resolution.

Comments closed

Continuing Arc-Enabled Data Services

Published 2022-08-22 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-Enabled Data Services. Part 5 takes us through what you can do with the Azure CLI:

In our previous post, we touched on the deployment of the Data Controller and being able to deploy via the Portal, Azure Data Studio, or CLI commands depending on whether you are implementing a directly or indirectly connected Data Controller.

Az Arcdata is a suite of CLI commands that allow command line management of the data controller and the Arc-enabled SQL Managed Instance once we have it configured.

Part 6 details the services available today:

Azure data services such as Azure SQL Managed Instance and Azure PostgreSQL are fully managed by Microsoft in the Azure Cloud. They provide you with evergreen environments because they are managed by Microsoft and always have the latest patches and feature offerings, while also providing you the ability to quickly and easily scale on demand based on the workload or requirements.

I do expect this set to grow over time.

Comments closed

Purview Access Policies and SQL Server 2022

Published 2022-08-12 by Kevin Feasel

Srdan Bozovic links Purview and SQL Server 2022:

The focus of this article is on using Microsoft Purview to enable access to user data as well as specific system metadata in SQL Server 2022 running on Azure Arc–enabled servers.

With the SQL Server 2022 release, the goal is to enable three main scenarios:

– Browsing data in user-defined tables and views.

– Performance monitoring with system commands, functions, and views.

– Security auditing with security-related system functions and views.

If Azure Arc-enabled servers are required for Purview to work, I think that will seriously hinder uptake.

Comments closed

Resolving tempdb Issues in Azure SQL DB

Published 2022-08-12 by Kevin Feasel

Holger Linke troubleshoots some problems:

The tempdb system database is a global resource available to users who are connected to Azure SQL Database or any instance of SQL Server. It holds temporary user objects that are explicitly created by a user or application, and internal objects that are created by the SQL Server database engine itself. The most common tempdb issue is running out of space, either regarding tempdb’s overall size quota or the transaction log.

The available tempdb space in Azure SQL Database depends on two factors: the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database. These are also the main factors to control if you are running out of tempdb space.

Click through for several error cases and how we can resolve them.

Comments closed