Traditional database integrity checks in SQL Server can be time-consuming and resource-intensive, especially for large databases, even when using super cool tools like Ola Hallengren’s maintenance solution.
To address this challenge, I developed a TSQL script for performing incremental integrity checks, which significantly optimizes the process and reduces its impact on the server.
Read More »Incremental Integrity Check for Large Databases
Redundant indexes in SQL Server are a phenomenon that is more common than I’d like to admit. I see it in quite a lot of shops. Meaning, that there’s still a significant target audience for this blog post!
This article published by Brent Ozar is very informative about redundant/duplicate indexes, what they mean, why they’re bad, and what should be done with them.
Also, a few years ago, Guy Glantser published a post about dropping redundant indexes. It’s very useful for finding all redundant indexes within all tables in a specific database.
But what both of these articles are missing – is the ability to easily generate Drop/Disable commands for these redundant indexes.
Read More »Drop All Redundant Indexes In Every Database All At Once
In this post, I will introduce you to a special T-SQL script that you can use for detecting potential SQL injection attacks in your database, as well as application-level bugs that you didn’t necessarily know you had.
Read More »Detect Application Bugs and Vulnerabilities You Didn’t Even Know About
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.
Hopefully, with this blog post, I’ll provide you with an easy-to-understand step-by-step guide, which would help you resolve the issue the right way, without any workarounds.
Read More »Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step
A few years ago, I created a couple of T-SQL scripts that can be used for comparing instance-level and database-level properties between two HA/DR replicas. Originally, this supported comparing only two servers. But recently, following a fan request, I upgraded the script to support an unlimited number of servers that you can compare to each other.
So, I figured, if one person found this useful, there must be more out there that would need this, right?
Read More »Compare SQL Server Instance Properties
Turns out there’s a shortage of solutions online for easily managing partitioned tables in SQL Server. Well… I guess I’ll have to do it myself!
As part of my journey to get better at Powershell, I gave myself a task: Create a project utilizing Powershell at its core, to solve a complicated problem as elegantly as possible.
A complicated problem indeed presented itself: How to remotely control multiple computers without having to log into them? Make them perform any task that could possibly be needed? Control them from one central location? And make the solution as easily scalable as possible?
I present to you, the Framework for Remote Execution using Powershell and SQL Server. Or in short: FREPS. Hmm… Actually, I may have to work on that name. 🤔
Read More »I got the Power: Remote Execution using Powershell and SQL Server
This is a T-SQL script that I’m cross-publishing with the official Madeira Data Solutions blog.
This script generates various permutations and variations of common and weak passwords and uses the PWDCOMPARE function to check whether there are any enabled SQL logins that have these passwords.
Read More »Detect Weak Passwords in SQL Server
Thanks to Paul Randal and his recently published blog post: “The Curious Case of… tracking page compression success rates“, I finally discovered what was the purpose of the column page_compression_success_count from the system function sys.dm_db_index_operational_stats, and how to use it.
Read More »Detect Low PAGE Compression Success Rates in all Databases
Every once in a while there comes an opportunity to “upgrade” your abilities with newly acquired knowledge that lets you “step up your game” and possibly add some “wow factor” to your work, leaving your peers awe-struck by your amazing new “magic trick”.
As a SQL Server consultant, one such opportunity that I had in my line of work, is when I learned about “Hypothetical Indexes” and how to use them.
Read More »Performance Tuning Like a Pro – with Hypothetical Indexes