Press "Enter" to skip to content

Category: Administration

Event Log Mining with Powershell

Published 2021-05-12 by Kevin Feasel

Jeffrey Hicks proffers some advice:

The other day someone who is learning PowerShell reached out to me with a problem. He couldn’t understand why the relatively simple PowerShell expression to pull information from the System event log wasn’t working. He wasn’t seeing errors, but he also wasn’t seeing the events he was expecting. Searching event logs with PowerShell is a common task. But as you’ll see, you may need to update your approach to mining event logs with PowerShell. Things change in the PowerShell world, and sometimes in subtle ways that you may not notice. Although to be fair, some of these changes my arise from new versions of the .NET Framework and/or Windows 10. Here’s what we encountered.

Read on to learn a more efficient technique for this process.

Comments closed

Cannot Recover the Master Database

Published 2021-05-12 by Kevin Feasel

Erik Cobb takes us through an undesirable experience:

Recently, during patching for a 2019 SQL Server, the SQL services refused to start after the patching. It was throwing the following heart attack inducing error:

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.

This is not the first time I have seen this error. Actually, this seems to be the default error that Microsoft throws any time there is a problem with the patching process. The good news is, most likely this is a false error and the master database is perfectly fine.

Read on to learn more.

Comments closed

The Mechanisms of Page Splits

Published 2021-05-11 by Kevin Feasel

Deepthi Gogrui takes us through page splitting in SQL Server:

Page splits can happen with the logical fragmentation and low page density causing the transactional log to be huge. Page splits are very expensive. As we learned from the previous post, page splits happens when SQL Server tries to insert a row and there is no more space on the page to fit in the page so page will split the page to give the space to fit it in that record. When SQL Server has to insert any rows in to the pages, first the free space on the page is checked at the header information of the page and if the record is within the mentioned free bytes, the record fits in. If the space is not contiguous within the page but there is total space to fit in the record, then the in-memory compaction of the page making the amount of free space on the page contiguous. This is not page split. Page split occurs when the space is not available on the page to fit in the record, in that case page split into half and the split point of the page is usually 50 percent each. Sometimes, the split can happen at the different point on the page is chosen by the storage engine as the obvious split point which is known as skewed page split. This is even more expensive when compared with the regular page split as this will create much larger transaction log.

What causes the page splits?

Read on for the full article.

Comments closed

Building a Better sp_help_revlogin

Published 2021-05-11 by Kevin Feasel

Eitan Blumin remembers:

Anyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.

Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).

Read on to understand what this is and several ways of doing the same thing better, including a new sp_help_revlogin2 that Eitan has put together.

Comments closed

When RCSI Is Not Enough

Published 2021-05-11 by Kevin Feasel

David Klee diagnoses an issue:

Basically, the use of the WITH (NOLOCK) query hint performs a dirty read, of which I’m sure you can find oodles of examples on the Internet about. Microsoft introduced RCSI in SQL Server 2005 to help you reduce the amount of blocks, and with RCSI exclusively in use, database readers no longer block other readers or writers. I love enabling RCSI wherever appropriate, as long as the TempDB database is monitored to make sure that RCSI’s version store is not causing any issues (and it usually is just fine). It also goes without saying that with RCSI enabled, you should work to remove the WITH (NOLOCK) query hints from your code, as RCSI is superseded by NOLOCK and can still invoke dirty reads.

But… this Client is still seeing serializable connections that will occasionally cause issues. What else can we check?

Read on for a few tips.

Comments closed

Registering an SPN for SQL Server

Published 2021-05-11 by Kevin Feasel

Jack Vamvas explains how to register an SPN for SQL Server:

I received a question from a colleague asking how to register a Service Principal Name (SPN) for SQL Server. The specific situation relates to upgrades and new SQL Server instances deployed onto new servers\server names . 

Defining an SPN results in an Active Directory name – allowing a client to uniquely identify  the service instance.  The SPN is comprised of a service name with a computer and user account resulting in a Service id. 

Read on for the process.

Comments closed

Securing Amazon Managed Streaming for Kafka

Published 2021-05-07 by Kevin Feasel

Stephane Maarek has some security advice for us:

AWS launched IAM Access Control for Amazon MSK, which is a security option offered at no additional cost that simplifies cluster authentication and Apache Kafka API authorization using AWS Identity and Access Management (IAM) roles or user policies to control access. This eliminates the need for administrators to run an unfamiliar system to control access to Apache Kafka on Amazon MSK, and learn intricate details and specific commands to manage Apache Kafka access control lists (ACLs).

This is a game-changer from a security perspective for AWS customers who use Apache Kafka: I recommend Amazon MSK customers use IAM Access Control unless they have a specific need for using mutual TLS or SASL/SCRAM authN/Z.

Read on to see how it works.

Comments closed

Availability Groups and Logins

Published 2021-05-04 by Kevin Feasel

Andrea Allred runs into a post-failover issue:

While doing a planned Availability Group failover, the application stopped talking to the database. After checking the SQL Server log, we found that all the SQL Logins were failing with an “incorrect password” error. The logins were on the server, the users were in the databases, and the passwords were even right, so what was wrong? It all comes down to SID’s (Security Identifiers).

Read on for the cause and the solution. I’d also recommend Sync-DbaAvailabilityGroup as a good dbatools cmdlet to use.

Comments closed

An Introduction to Latches

Published 2021-05-03 by Kevin Feasel

Paul Randal starts a series on latches:

In some of my previous articles here on performance tuning, I’ve discussed multiple wait types and how they are indicative of various resource bottlenecks. I’m starting a new series on scenarios where a synchronization mechanism called a latch is a performance bottleneck, and specifically non-page latches. In this initial post I’m going to explain why latches are required, what they actually are, and how they can be a bottleneck.

Read on to learn what a latch is, why it is useful, and how latches work at a high level.

Comments closed