Press "Enter" to skip to content

Category: Administration

Praise for Purvi’s List

Published 2023-02-15 by Kevin Feasel

Andy Yun is a fan:

By sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.

Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.”

Read on to learn what had Andy so excited.

Comments closed

Data Cleanup in Query Store

Published 2023-02-13 by Kevin Feasel

Grant Fritchey does some housekeeping:

The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:

Even with that limit, there are still several options available to you for when to clean up old Query Store data, whatever the definition of “old” (i.e., time-based or at the user’s whim).

Comments closed

Migrating SQL Server Databases to Aurora

Published 2023-02-09 by Kevin Feasel

Jamie Wick wounds me:

Our decision to move some (not all) SQL databases to AWS Aurora was partially a cost decision, but also an effort to simplify operational management by diversifying our database platforms. Now that second reason might seem counter-intuitive, but here are some details about the situation and how we came to our decision:

Read on for Jamie’s thoughts and tips to make the migration process easier.

Comments closed

Unsupported Characters in Azure SQL DB Database Names

Published 2023-02-07 by Kevin Feasel

Nithya Bondalapati notes a discrepancy:

ARM (Azure Resource Manager) API does not support specific T-SQL resource naming formats and that’s why when Databases are created using T-SQL/SSMS, unsupported characters could get allowed into the names.
Azure Portal UI does not have this issue, because when you use Portal UI for creating a Database, the creation goes through ARM, and hence when unsupported characters are entered in the Name field, it immediately throws error, as shown in the below image.

None of the restrictions or onerous, but Nithya explains what actions fail or don’t quite work right if you have a database with special characters or ending in a period or space.

Comments closed

When to Use OPTIMIZE_FOR_SEQUENTIAL_KEY

Published 2023-02-07 by Kevin Feasel

Chad Callihan explains a feature:

If you’re running into problems caused by concurrent inserts and you’re on SQL Server 2019 or above, it’s worth testing out OPTIMIZE_FOR_SEQUENTIAL_KEY. This may alleviate those issues by handling inserts a little bit differently to reduce contention. OPTIMIZE_FOR_SEQUENTIAL_KEY will “control the rate at which new threads are allowed to request the latch, and favor threads that are likely to keep the throughput high.”

Read on to see how you can enable it but also when it makes sense to enable it.

Comments closed

Power BI Workspace Roles

Published 2023-02-03 by Kevin Feasel

Reza Rad shares some recommendations with us:

Power BI workspaces are not like the old days when we had Edit access and View access only. You have more options for roles in a workspace, and in my courses, I have found that many people have chosen the incorrect role without knowing what the role does. In this article, I’ll explain all the roles in the workspace, and what is the best way to set them up to have a secure workspace.

Click through for the article, as well as an accompanying video. Or a video and an accompanying article, if that’s how you roll.

Comments closed

Power BI App Audiences and Workspaces

Published 2023-02-02 by Kevin Feasel

Marc Lelijveld looks at an update to Power BI:

Back in August 2022, Microsoft released a feature to have multiple audiences for Power BI Apps, or if you will deploying multiple versions of the same app where you can actively show and hide content for different audiences. A very welcome feature that has a big impact on the number of workspaces people require to have to reach the same goals. In this blog, I will elaborate on the impact of multiple audiences for Power BI Apps, as well as the impact on workspace governance.

Read on for Marc’s thoughts on the topic.

Comments closed

Registering AKS Endpoints on Azure DNS

Published 2023-02-02 by Kevin Feasel

Denny Cherry notes that the DNS server is in another castle:


If you have an Azure environment when you have your DNS servers in a separate vNet from your new AKS environment you’ll notice that you get an error when deploying the AKS environment which looks something like this.

Agents are unable to resolve Kubernetes API server name. It’s likely custom DNS server is not correctly configured, please see https://aka.ms/aks/private-cluster#hub-and-spoke-with-custom-dns for more information.

The fix for this is actually pretty straightforward, but I’m going to give you a little background on why this happens.

Click through for the answer.

Comments closed

Configuring Ola’s Scripts

Published 2023-02-01 by Kevin Feasel

Ben Miller begins a series on Ola Hallengren’s maintenance solution:

I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.

This first post acts as a primer for those who might have the solution but haven’t investigated it in any detail.

Comments closed

What to Use Instead of Get-EventLog

Published 2023-01-30 by Kevin Feasel

Emin Atac gets a scary message:

When you type the following

Get-EventLog-SourceMicrosoft-Windows-Kernel-General-Newest20-LogNameSystem-InstanceId1 | Select-ExpandPropertyMessage

You get

Possible detection of CVE: 2023-01-09T09:08:23.5000000Z
Additional Information: 2023-01-08T19:56:29.1492612Z
This Event is generated when an attempt to exploit a known vulnerability (2023-01-09T09:08:23.5000000Z) is detected.
This Event is raised by a User mode process.

Read on to learn what this error message means, why it pops up, and what you can do to avoid it in the future.

Comments closed