DEV Community: Amar Abaz

MSSQL LEDGER TABLES Bulletproof Data Integrity and Authenticity

Amar Abaz — Mon, 05 Oct 2026 19:16:37 +0000

Trust is hard to come by, especially when it comes to sensitive data like medical records, security or financial info.
External regulators and clients often demand independent, zero trust verification. They don't just want to know that your policies are being followed, they want undeniable proof.
For this sensitive data, starting from SQL Server 2022 (16.x), you can natively use LEDGER TABLES because the table itself becomes self verifying.

Every time a row is inserted, updated, or deleted, SQL Server cryptographically hashes the transaction and links it into a secure, history backed chain.
This provides an unalterable record of data authenticity that simplifies compliance and prevents tampering with sensitive data. Even with full access, the cryptographic chain ensures that the historical data matches the ledger digest perfectly, making internal audits completely bulletproof.

In this article, I will dive straight into the T-SQL scripts you need to build up ledger systems and verify your database authenticity.
Depending on your specific use case, you can create a LEDGER TABLE only for INSERTS, or one that fully tracks INSERT, UPDATE, and DELETE.

🫡 Case 1: Securing Insert Only Data (APPEND_ONLY)

With the code below, you will create a table that strictly allows only INSERT statements.
If you try to run UPDATE query, SQL Server won't even try to execute it. It will instantly block the transaction and fire back an ledger protection error message.
T-SQL Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE dbo.PatientRequests
(
    RequestID INT NOT NULL PRIMARY KEY,
    RequestNumber VARCHAR(255) NULL,
    MedicalProduct VARCHAR(255) NULL,
    ClientID VARCHAR(255) NULL,
    RequestDate DATETIME NULL
) 
WITH (LEDGER = ON (APPEND_ONLY = ON));
GO

INSERT INTO dbo.PatientRequests (RequestID, RequestNumber, MedicalProduct, ClientID, RequestDate) 
VALUES (1, 'REQ-9875', 'Insulin Pump', '0101990710023', GETDATE());
GO

UPDATE dbo.PatientRequests 
SET MedicalProduct = 'EpiPen' 
WHERE RequestID = 1;
GO

Preview:

🫡 Case 2: Securing Updatable Data (INSERT, UPDATE, DELETE)

OK, for our next scenario things get a bit more messy. Because an updatable ledger automatically generates a separate history table to track every change made to the original table, letting SQL Server build it by default is not always the best option.
You need to keep the growth and size of your tables in mind. To prevent overloading your primary storage, the best approach is to organize your ENV by creating a seperate schema and a separate filegroup for your historical ledger data for the best maintenance. Even better, a solution is to create history tables based on a PARTITION FUNCTION. But I won't cover that here.

First lets create schema and filegroup.

USE [master]
GO
ALTER DATABASE [AdventureWorks2022] ADD FILEGROUP [DATA]
GO
ALTER DATABASE [AdventureWorks2022] ADD FILE ( NAME = N'AdventuerWorks_DATA', FILENAME = N'C:\MSSQL\DATA\AdventuerWorks_DATA.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [DATA]
GO

USE [AdventureWorks2022]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'HIST')
BEGIN
    EXEC('CREATE SCHEMA HIST');
END
GO

Now you can create your table using the code below. I have also included the necessary INSERT, UPDATE, and DELETE statements right after the table creation script so you can test the full lifecycle immediately.

T-SQL Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE dbo.PatientRequests_main
(
    RequestID INT NOT NULL PRIMARY KEY ON [PRIMARY],
    RequestNumber VARCHAR(255) NULL,
    MedicalProduct VARCHAR(255) NULL,
    ClientID VARCHAR(255) NULL,
    RequestDate DATETIME NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) 
ON [PRIMARY]
WITH 
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = HIST.PatientRequests_hist),
    LEDGER = ON
);
GO

ALTER TABLE HIST.PatientRequests_hist 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

DROP INDEX [ix_PatientRequests_hist] ON [HIST].[PatientRequests_hist] WITH ( ONLINE = OFF )
GO

CREATE CLUSTERED INDEX [ix_PatientRequests_hist] 
    ON HIST.PatientRequests_hist(RequestID, ValidFrom, ValidTo) 
    ON [DATA];
GO

INSERT INTO dbo.PatientRequests_main (RequestID, RequestNumber, MedicalProduct, ClientID, RequestDate)
VALUES (101, 'REQ-5550', 'Blood Pressure Monitor', 'CLIENT-99', GETDATE());
GO

UPDATE dbo.PatientRequests_main
SET MedicalProduct = 'Glucose Meter'
WHERE RequestID = 101;
GO

DELETE FROM dbo.PatientRequests_main
WHERE RequestID = 101;
GO

😎 END RESULT: Querying and Managing Your Ledger Tables

Immediately after you create your ledger tables, SQL Server automatically generates dedicated ledger views for you. This means that even though you are using history tables, you don’t need to write queries using clauses like FOR SYSTEM_TIME AS OF to show past data. You won't be able to turn off system versioning or make manual adjustments to alter past states. Everything is fully taken care of by SQL Server's engine.

To properly audit your data, you should query the system views that SQL Server automatically creates with the _Ledger suffix for your tables. Here is an example below:
T-SQL Code:

SELECT *
FROM dbo.PatientRequests_Ledger
ORDER BY 
    ledger_transaction_id ASC, 
    ledger_sequence_number ASC;
GO

SELECT *
FROM dbo.PatientRequests_main_Ledger
ORDER BY 
    ledger_transaction_id ASC, 
    ledger_sequence_number ASC;
GO

Preview:

😎 Generating Fingerprint proof

You can now run this procedure below to generate a Database Ledger Digest. This outputs a cryptographic snapshot representing the state of your database at this exact moment in time.
The digest is something you save externally and later use as evidence to verify that the ledger hasn't been tampered with.

EXEC sp_generate_database_ledger_digest;

When you want to verify it, take the outcome of the procedure and execute it like this,

USE master;
GO
ALTER DATABASE [AdventureWorks2022]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE [AdventureWorks2022];
GO
DECLARE @ledgerDigest NVARCHAR(MAX) = N'{"database_name":"AdventureWorks2022","block_id":1,"hash":"0xDEB4ADFCC9F06E2FF0155C9D0543B9CBA155052657BEFB0B21887A8AF8BC5900","last_transaction_commit_time":"2026-10-05T20:48:27.4000000","digest_time":"2026-10-05T18:51:12.6864896"}';
EXEC sp_verify_database_ledger @ledgerDigest;
GO

USE master;
GO
ALTER DATABASE [AdventureWorks2022]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO

To view DATABASE Ledger use this command

SELECT * FROM sys.database_ledger_transactions;
GO

SELECT * FROM sys.database_ledger_blocks;
GO

Microsoft Illustration:

learn.microsoft.com

Reference:

MSSQL EXTENDED EVENTS Stop Guessing and Start Targeting

Amar Abaz — Mon, 05 Oct 2026 12:24:24 +0000

Instead of capturing everything and slowing down your system with SQL Profiler, XEvents allow you to execute a targeted, razor sharp seek.
It is lightweight, deeply integrated into the SQL Server engine, and gives you the exact data you need to debug without a performance penalty.

Once your session is running, you can stream the results live, inspect them temporarily in the memory ring buffer, save them directly to a file, or extract the XML data to load it straight into a readable table for advanced querying and reporting.
In this article, I won't talk about the GUI wizard. Instead, we will focus on my T-SQL scripts you can directly execute to start the seek for different use cases.

Earlier, I already shared how to use XEvents to track and report on DEADLOCK and BLOCKING sessions. 🤯
https://dev.to/abeamar/track-blocking-and-deadlocks-in-mssql-with-my-custom-script-1877
This time, we are diving deeper to understand what the underlying code really does, while exploring new scenarios you can use in your daily work.

🫡 Scenario 1: Catching Silent Errors

Applications often catch errors internally or suppress them, leaving you completely in the dark.
This script creates an XEvent catching every single error reported across the whole instance. It collects critical context like the exact SQL text, client hostnames, and query hashes, and dumps them into a file.

CREATE EVENT SESSION [Seek_ERRORS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
)
ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLTrace.xel')
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
);
GO
-- Start the session immediately
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER STATE = START;
-- Start automatically if the SQL Server restarts
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER WITH (STARTUP_STATE=ON);
GO

Once the session has been running and gathering data, you don't need a UI to read it. You can query the target file directly using T-SQL and cast the output to XML for easy viewing:

SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\Temp\AbeSQLTrace*.xel', NULL, NULL, NULL);

If you don't want to save the data to a physical file and would rather just inspect the events using the SSMS GUI in real-time, you can simply remove or comment out the file target line

--ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLTrace.xel')

When you do this, you can just right click the session in SSMS and select "Watch Live Data" to see the errors pop up on your screen on the fly.

If you only want to look at errors on a specific database or look for specific keywords mentioned in the SQL text, you can add a WHERE STATEMENT in you code for event, like this

    WHERE ([sqlserver].[sql_text] LIKE N'%AdventureWorks%') 
       OR ([sqlserver].[database_name] = N'AdventureWorks')

🫡 Scenario 2: Tracking behavior on target objects

And now by using the WHERE statement with COMPLETED queries, you can actively track the behavior of specific object usage, and that targets a specific database. This allows you to track exactly who was accessing your tables, from which machines, and when.

CREATE EVENT SESSION [Monitor_OBJECTS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION (
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
)

ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLMonitor.xel')
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
);
GO
-- Start the session immediately
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER STATE = START;
-- Start automatically if the SQL Server restarts
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER WITH (STARTUP_STATE=ON);
GO

If you are running SQL Server on AWS RDS, you don't have access to standard directories, so you must save into the designated RDS log directory: D:\rdsdbdata\Log\
Once captured, you can read the data back using sys.fn_xe_file_target_read_file.
Also you can always filter your sessions by severity or exclude generic custom messages by filtering the error_number. Here is a full example combining all of these for aws rds. Errors equal or over 50000 are custom raise.

CREATE EVENT SESSION [SeekErrorsAWS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.sql_text
    )
    WHERE (
        [package0].[greater_than_int64]([severity], (10)) 
        AND (
            [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks') 
            OR [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks2')) 
        AND [error_number] <> (50000)
    )
)
ADD TARGET package0.event_file(
    SET filename = N'D:\rdsdbdata\Log\SeekErrorsAWS',
    max_file_size = (32)
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION [SeekErrorsAWS] ON SERVER STATE = START;
ALTER EVENT SESSION [SeekErrorsAWS] ON SERVER WITH (STARTUP_STATE=ON);
GO
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\rdsdbdata\Log\SeekErrorsAWS*.xel', NULL, NULL, NULL);

🫡 Scenario X: Mastering WHERE statements

So as you see crucial thing is your case and mastering WHERE statement.
You can also track sessions by duration, lets say you want to track sessions that take over 5 minutes to finish.

    WHERE (
        [duration] >= 300000000 
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )

or high logical reads, cpu, specific user..

WHERE (
    ([logical_reads] >= 10000 OR
    [cpu_time] >= 2000000 OR
    [sqlserver].[username] = N'SpecificUSER')
    AND [sqlserver].[database_name] = N'AdventureWorks'
)

At the end, when you finish debug don't forget to stop or drop the event you have created.

-- Stop the sessions
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER STATE = STOP;
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER STATE = STOP;
GO

-- Drop the sessions
DROP EVENT SESSION [Seek_ERRORS] ON SERVER;
DROP EVENT SESSION [Monitor_OBJECTS] ON SERVER;
GO

Reference:

Show DEV: AbeGO Video & GPX Telemetry Sync Tool

Amar Abaz — Wed, 23 Sep 2026 11:33:49 +0000

I wanted a reliable way to overlay my telemetry data onto my video without relying on complex video editing suites or paid platforms.
So, I built AbeGO.

Sync Data. Overlay Metrics. Export Video.

AbeGO is a WIN desktop tool designed to turn your ride videos or other outdoor activities into professional looking telemetry video.
Simply import a GPX file from Strava, Garmin, or any other GPS device, add your action camera video, and effortlessly sync the GPS data with your footage.
Once synced, you can export partial highlights or full length videos with your real time activity metrics displayed directly on the screen.

You can see exactly how the telemetry will look on your video before export, including:

View the entire GPX log alongside comprehensive statistics.
Monitor current and total speed dynamic indicators.
Track current and total elevation metrics.
Display current and total distance counters.
Render interactive route maps and graphics on screen.
Download a partial clip or the full video after a proper sync.

The project is fully live and public on GitHub. Check it out!

https://github.com/abeamar/AbeGO

APP PREVIEW:

Overall, the purpose of this app is to link the gap between raw data tracking and engaging visual storytelling.
It takes the complexity out of video production so you can focus on your next adventure.

What happens when SQL Server PAGE gets corrupted ?

Amar Abaz — Sat, 14 Feb 2026 19:45:14 +0000

In this article, I will explore how SQL Server works with PAGES, the smallest unit of data storage. We will explore how SQL Server organizes data within these pages and how to interact with them. Additionally, I am gonna simulate page corruption, run a validity check, and demonstrate how to fix it.

Data files are divided into 8KB pages, which consist of

Page Header (96 bytes); Contains metadata about the page.
Data Rows (8060 bytes); Holds the actual data of your table.
Slot Array (36 bytes); Manages the location of data rows within the page.

Together, these components make up 8192 bytes, or 8KB, which is the standard size for a SQL Server page.
An extent in SQL Server consists of 8 contiguous 8KB pages, totaling 64KB.
There are two types of extents UNIFORM EXTENTS, where all 8 pages are used by a single object, and MIXED EXTENTS, where the pages are shared by multiple objects. This helps optimize space usage and performance, and behaviour is menagable in your database Options.

SELECT [name], [is_mixed_page_allocation_on] FROM sys.databases;

These pages are stored in physical files, categorized as

Primary or Master Data File (.mdf)
The main file where system and user data are stored by default. It contains also sys data of your database.
Secondary or Next Data File (.ndf)
Additional data files used to expand storage, but optional for better arhitecture and sizing.
Transaction Log Data File (.ldf)
Stores the transaction logs, which track changes made to the database to ensure consistency and recovery.

These files work together to manage your database storage and ensure data integrity.

Let's get to it..

Lets now check the allocation and structural integrity of the pages in the AdventureWorks2022 database, (skipped checking the indexes). I will now focus on table Person where we will do our corruption test.

DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

DBCC CHECKALLOC ('AdventureWorks2022', NOINDEX);

Identifying Pages in Your Database

You can identify which pages a table occupies by using DBCC IND.
This command provides a breakdown of where the table's data is stored across pages and files.

DBCC IND('AdventureWorks2022', 'Person.Person', -1)

Once you've identified a page, you can go deeper into its content using the DBCC PAGE command.
Lets show the internal structure of the page, including the Page Header, m_pageId, m_objId, and the data rows stored in it.

For example, to inspect page 1314 in file 1.

DBCC PAGE('AdventureWorks2022', 1, 1314, 1) WITH TABLERESULTS;

Simulating Page Corruption

To demonstrate how corruption can occur, let's intentionally modify a page.

ALTER DATABASE [AdventureWorks2022] SET  SINGLE_USER WITH NO_WAIT;
DBCC WRITEPAGE('AdventureWorks2022', 1, 1314, 0, 1, 0x11, 1);

Verifying the Corruption

Now run DBCC CHECKDB or CHECKTABLE again to see how SQL Server detects the integrity corruption. Do not use CHECKALLOC because it only checks for allocation problems not integrity.

DBCC CHECKDB('AdventureWorks2022');
DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

These commands will perform an integrity check on the database and report any issues they find, including corruption in the affected pages.
If we select the whole table we have an error.

Msg 824, Level 24, State 2, Line 32
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x246e3cb2; actual: 0x24663cb2). It occurred during a read of page (1:1314) in database ID 5 at offset 0x00000000a44000 in file 'C:\MSSQL\DATA\AdventureWorks2022.mdf'. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

More about DBCC Commands on my earlier topic here:
MSSQL DBCC How good are they really ? 👌
.
.
.

Now lets talk about the ways how to fix this and what to keep in mind..

🤞First Option: Try REPAIR_REBUILD

DBCC CHECKTABLE ('Person.Person', REPAIR_REBUILD);

REPAIR_REBUILD can fix certain types of index corruption or inconsistent structures without causing data loss. It is safer than REPAIR_ALLOW_DATA_LOSS because it doesn't drop data but will rebuild corrupted indexes and structures.
However REPAIR_REBUILD won't help in our case.

Msg 8928, Level 16, State 1, Line 39
Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data): Page (1:1314) could not be processed. See other errors for details.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8939, Level 16, State 98, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data), page (1:1314). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
Repairing this error requires other errors to be corrected first.
Msg 8976, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data). Page (1:1314) was not seen in the scan although its parent (1:1568) and previous (1:1313) refer to it. Check any previous errors.
Repairing this error requires other errors to be corrected first.
Msg 8978, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data). Page (1:1315) is missing a reference from previous page (1:1314). Possible chain linkage problem.
Repairing this error requires other errors to be corrected first.
There are 19967 rows in 3807 pages for object "Person.Person".
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'Person.Person' (object ID 2101582525).

😥Second Option: REPAIR_ALLOW_DATA_LOSS

DBCC CHECKTABLE ('Person.Person', REPAIR_ALLOW_DATA_LOSS);

This should be your last option and only to be used if you are okay with the possibility of data loss. It attempts to repair the corruption in the table, but any corrupted data that cannot be repaired will be removed. Before proceeding, it is crucial to do BACKUP DATABASE to ensure you have a recovery point in case the repair process causes unintended consequences.

🤔Third Option: PAGE RESTORE
Page restore is a technique used to restore specific corrupted pages in a database from an available backup. The goal is to replace the corrupted pages with uncorrupted pages from a previous backup, minimizing the impact on the rest of the database and avoiding the need for a full restore. However, to successfully restore a page, it's critical to identify when the page was last consistent and healthy.

When you have regular log backups as part of your Recovery Time Objective (RTO) strategy, you can typically recover your database to the point of the last log backup. However, even when using CHECKSUM and VERIFY during backups, these options only validate the integrity of the backup file and do not detect database corruption.

Now let's check and confirm the corrupt page id using command bellow.
This select shows those pages that SQL Server has flagged as suspect, which means it gives you a history of past corruption events.

SELECT * FROM msdb.dbo.suspect_pages;

Execute commands below and do not forget to apply the log restore after a page restore, if not it will break the continuity of your database's transaction LOG chain. After that make another log backup and restore, and check again the whole database with DBCC CHECKDB. This should be the way to do it with large databases, because we will not restore the whole database only page and logs.

RESTORE DATABASE [AdventureWorks2022] PAGE='1:1314' 
FROM  DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\FULL\w19$SQ_AdventureWorks2022_FULL_20260214_122842.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 5, ONLINE   -- online is supported by Enterprise edition only
RESTORE LOG [AdventureWorks2022] FROM  DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\w19$SQ_AdventureWorks2022_LOG_20260214_122900.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 5

BACKUP LOG [AdventureWorks2022] TO DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' WITH INIT
RESTORE LOG [AdventureWorks2022] FROM DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' WITH RECOVERY

🫡Fourth Option: Remote restore and recreate
Restore to a new location and recreate that Table (For small databases).
Keep it simple if the corruption is isolated to a single table or a small part of the database, and the database size allows for it. An alternative approach is to restore the database to a new location and then recreate the corrupted table on primary db. While doing that you could also make a Trigger to deny any interaction if SQL Server still allows insert, update, delete to be performed.

CREATE TRIGGER tr_tableDENY ON Person.Person
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    RAISERROR ('This table is temporarily locked for investigation.', 16, 1);
    ROLLBACK TRANSACTION;
END

Final Advice

Make sure to run DBCC CHECKDB regularly. This is crucial for maintaining the integrity of your database. However, don't rely solely on DBCC checks. Set up real time alerts for corruption detection.

Here's my advice, create an SQL job that constantly monitors the msdb.dbo.suspect_pages table for new entries and sends you an alert as soon as any corruption is detected. You can clean this table after alert was processed.

Moreover, since Error 823, 824, and 825 are associated with corruption in database pages, you should set up an alert for these specific errors as well. That way, you can be notified immediately when they happen.
To do this go to SQL Server Agent, Right click on Alerts and select New Alert. Make Alert for every Error and set to use Operator for mail response.

Error 823: This error indicates that SQL Server encountered an I/O error while reading or writing to a disk.
Error 824: This error occurs when SQL Server detects a logical consistency error in the data read from a page.
Error 825: This error signifies a page failure during an I/O operation.

Reference:

Show DEV: AbeMON MS SQL Tool for Monitor and Debug

Amar Abaz — Sun, 01 Feb 2026 17:28:37 +0000

AbeMon is a powerful, lightweight toolkit designed for database administration, offering all the essential checks needed for troubleshooting and maintenance in your daily tasks.
Enjoy a 30 day free trial to explore its full potential and capabilities.
If you find it useful, feel free to get in touch.
This App is designed to deliver smooth performance while using minimal system resources, consuming just 15-20MB of RAM.

The project is fully live and public on GitHub. Check it out!


git clone "https://github.com/abeamar/AbeMon.git"

What this tool provides:

Disk usage and growth
Memory and CPU utilization
Database health, security and size checks
Backup and restore history checks
Monitor active backup and restore operations with predictions
Table sizes and index fragmentation checks
SQL Server error logs
Windows Events error logs
SQL Agent job failures checks
Schema object definitions
Raw SQL object extraction
Active sessions and blocking queries
Deadlock, block tracking
Custom session level monitoring for your SQL Server instance
FULL EXPORT of db objects

This is a fully functional and very useful tool to have in your DBA arsenal.

😎
Stay tuned for regular updates as I dive deeper into how this tool can be used in various DBA scenarios. Whether you're troubleshooting issues or optimizing your SQL Server environment, this tool will be your best friend.

APP PREVIEW:

LIVE Examples in Action with CASES:

CASE: Tracking Restore & Backup Operations with Real Time Insights

Database restore is dragging on longer than expected, and you have no idea who initiated it or which backup was used. Or maybe you've got conflicting reports about when a restore actually happened, and you're stuck chasing down clues through endless logs.

With just a few clicks, you can access detailed restore history that shows exactly who performed the restore, which backup was used, and when it happened.
Or Instantly see who's performing restores, which backups are in use, and predict how long they'll take.
Whether it's active restores or ongoing backup jobs, this tool will provides the crucial data you need.

CASE: Diagnosing long running transactions when resources are stuck

You show up to work, and your services are down. They are not processing anything. Maybe it’s just waiting for resources, or worse, it's hung up because your tempdb or transaction log is full. The usual suspects, but it's hard to figure out exactly where the bottleneck is.

That's where this tool comes in. One quick check will show you all the active sessions in your databases, sorted by execution time, so you can pinpoint the longest running transactions that might be blocking resources. But you don't have to stop there, you can combine this with other checks for tempdb usage, overall session activity, and transaction log health to get a full picture of what's happening and address it before it affects the rest of your system.
Sometimes the answer is hiding in plain sight, SQL Server logs and Windows Event logs often tell the full story once you know where to look.

CASE: Handling Max Identity value reached in your production

Just a quick example of how this tool can save you from unexpected surprises. Imagine you're in production and service app suddenly can't insert data into a key table because your identity column (whether INT, BIGINT, or SMALLINT) has maxed out. It happens.

With one of the many checks the tool runs this is just one of the daily checks you would keep an eye on. For every table with incremental identity column in your databases you can get a heads up before that becomes a problem. If you know this early, you can take action, like altering the column to a bigger type or creating a new table, so things don't grind to a halt.

Always keep an eye on your table size.
In SQL Server, a well maintained index and table size go a long way in ensuring fast queries and low overhead.

Take it for a spin..

Download AbeMON and explore its full range of features at your own pace. From real time monitoring to detailed backup and restore insights, this tool is designed to make your workflow smoother.
I am sure you'll appreciate the difference once you see it in action.

MSSQL DBCC How good are they really ?

Amar Abaz — Wed, 21 Jan 2026 20:48:58 +0000

What does DBCC mean ? Well, it stands for Database Console Commands, of course.
When you're working with SQL Server, these commands are like the secret weapons for any DBA. They're super powerful tools for troubleshooting, monitoring, and ensuring your data stays in check and safe.

But how effective are these commands really ? Are they as good as they seem ?
Let's dive into some of the most useful DBCC commands, how they work, and when you might want to use them.

To explore all commands (documented and undocumented) we first need to enable the trace flag for detailed output, then use the command to view them.

DBCC TRACEON(2588) WITH NO_INFOMSGS;
DBCC HELP ('?') WITH NO_INFOMSGS;

No, if you're not sure how to use some of those commands like for example DBCC CHECKDB, you can simply run it like this,

DBCC HELP ('CHECKTABLE') WITH NO_INFOMSGS;

There's an extensive list of DBCC commands, each serving a specific purpose.
This will give you a complete breakdown of how to use the command and its various options.

Bellow are some of the most common ones, with real practical scenarios and steps how to use them ..

DBCC USEROPTIONS

Very useful command to check and troubleshoot session settings for a user, including the language, date format, read settings and other stuff on session level.

DBCC USEROPTIONS;

DBCC CHECKDB

This is the GO TO command for verifying the physical and logical integrity of an entire database. It's typically executed when corruption is suspected, or as part of regular health checks. Create an SQL job to execute this daily or weekly depending on your database size.

When corruption is detected, especially if multiple objects are affected then this command often becomes your first line of defense.

Before attempting any repair, isolate the database to prevent active connections from interfering with the process.

ALTER DATABASE [AbeDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

This ensures only privileged users can access the database while SQL Server rolls back all active transactions.

You can now rerun DBCC CHECKDB with a repair parameter.

DBCC CHECKDB ('AbeDB', REPAIR_FAST); --minimal repair
DBCC CHECKDB ('AbeDB', REPAIR_REBUILD); --rebuild bad index
DBCC CHECKDB ('AbeDB', REPAIR_ALLOW_DATA_LOSS); --can result dataloss

REPAIR_ALLOW_DATA_LOSS option should never be your first choice in production. If REPAIR_FAST fails, restoring from a clean backup is almost always the safer option.

Avoid data loss unless absolutely necessary. Ensure consistent and verified log backups.
Also do not use command DBCC REPAIRDB. This is an older and not recommended command that has been replaced by CHECKDB (Since SQL Server 2005).

DBCC CHECKALLOC

This command is especially useful when you suspect on storage level or allocation related corruption. It checks and ensures all pages are correctly allocated, for data integrity you should use CHECKDB.

DBCC CHECKALLOC ('AbeDB'); --check
DBCC CHECKALLOC ('AbeDB', NOINDEX) WITH NO_INFOMSGS; --no index check

DBCC CHECKTABLE & CHECKINDEX

Another good option if you don't want to scan the whole database and want to focus on specic table or index, or you already know where the problem.. then use this commands with WITH EXTENDED_LOGICAL_CHECK.
It will include better logical integrity checks and check index consistency, foreign keys, null and unique constraints.

Go by logic "Start broad, then narrow down".

DBCC CHECKTABLE ('AbeTable');
DBCC CHECKTABLE ('AbeTable', REPAIR_REBUILD); --repair
DBCC CHECKTABLE ('AbeTable', REPAIR_ALLOW_DATA_LOSS); --data loss

ALTER INDEX IndexName ON AbeTable REBUILD; --rebuild if errors exist

DBCC OPENTRAN

Used to display information about the oldest active transaction within a specified database. With this helpful command you can find blocking processes that need to be terminated to free up resources. Used when troubleshooting why the transaction log is not shrinking or is growing uncontrollably, as open transactions can prevent log truncation during backup.

DBCC OPENTRAN('AbeDB');

DBCC FREEPROCCACHE & DBCC FREESYSTEMCACHE

FREEPROCCACHE is used if you suspect that the cache contains bad or suboptimal execution plans. You can clear specific cached plan.
When a query is executed, sql server reuses an existing execution plan from the cache if it exists, which can improve performance because it avoids the need to recompile the query each time.

DBCC FREEPROCCACHE (0x05004320D5B7S12701B91BBE5B5082B6) WITH NO_INFOMSGS;

FREESYSTEMCACHE allows you to clear various caches based on the context you choose. The PLANCACHE option is less impactful than FREEPROCCACHE because it only clears the execution plans, without affecting other system caches such as the buffer pool.

DBCC FREESYSTEMCACHE ('ALL');
DBCC DROPCLEANBUFFERS; --pages of data read from disk
DBCC FREESYSTEMCACHE ('PLANCACHE'); --recompile queries with the new index
DBCC FREESYSTEMCACHE ('COLUMNSTORE');--columnstore indexes

DBCC SHOWCONTIG

This command displays fragmentation info. I use this a lot to check status of indexes using T-SQL. Second parameter is for the index on the table you focus on.

DBCC SHOWCONTIG('Person.Person',2);

DBCC SQLPERF

Quickly checks the transaction log space usage in SQL Server. Great for monitoring and intervening with log backups or shrinking when needed.

DBCC SQLPERF('logspace');

DBCC TRACEON & TRACEOFF & TRACESTATUS

For deeper analysis, these commands allow you to enable, disable, and inspect SQL Server trace flags.

Trace flags are commonly used for debugging, performance troubleshooting, and internal behavior analysis. They can be enabled at the session level or globally, and you can even configure them at server startup if permanent behavior changes are required.
They are primarily used to either change the behavior of the SQL Server engine or to log additional details in the SQL Server logs.
Be cautious not to overwhelm your server with excessive logging. That's why it's important to disable trace flags after you've completed your analysis.

Bellow is a few examples and most commont traces you should use.

Trace 1222: Gives detailed information about deadlock into SQL Server error log..

DBCC TRACEON(1222); --enable flag
DBCC TRACEON(1222,-1); --enable globaly
DBCC TRACEON(1204, 1222); --enable multiple flags
DBCC TRACESTATUS(1222); --check active flags
DBCC TRACEOFF(1222); --turn off

To ensure your configuration persists after every server restart, you need to add the trace flag at the server level,

Open SQL Server Configuration Manager,
Right click on SQL Server Services,
Select Properties, go to the Startup Parameters tab,
Add a new line with the trace flag: -T1222.

Here are some other useful Trace Flags,

1222 - log deadlock in server logs
1204 - locking info with quick analysis
3226 - stops successful database backups from logging
9567 - compression for fast AG Automatic seeding
3042 - disable pre allocation of space during backup
3604 - send output to client
1117 - autogrow all files together
460 - truncation enhance info ..... And many more.

So, how good are they really ?

While DBCC commands can catch many types of corruption, they aren't a one-stop solution. They may not catch all possible issues, and some might require deeper troubleshooting.
The answer lies in your knowledge of commands and the specific needs of your database.
The best way to truly understand the power of DBCC is to experiment with different commands in your own test environment.
With the right knowledge and caution, DBCC commands can be incredibly effective tools in your DBA arsenal.

Let's have a look at those arsenal again 🤯🤯

dbcc sqlperf
dbcc inputbuffer
dbcc checkdb 
dbcc memorystatus
dbcc freeproccache
dbcc useroption
dbcc ind
dbcc page
dbcc writepage
dbcc freesystemcache
dbcc show_statistics
dbcc dropcleanbuffers
dbcc showcontif
dbcc opentran
dbcc addtempdb 
dbcc checkalloc 
dbcc checkcatalog 
dbcc checkindex 
dbcc checkstorage
dbcc checktable
dbcc checkverify 
dbcc complete_xact
dbcc engine
dbcc fix_texT
dbcc forget_xact
dbcc indexalloc
dbcc monitor
dbcc pravailabletempdbs
dbcc reindex
dbcc serverlimits
dbcc stackused
dbcc tablealloc
dbcc zapdefraginfo 
dbcc quorum
.....
...
..

References:

Modular Approach to Database Performance and Maintainability

Amar Abaz — Sun, 18 Jan 2026 12:12:03 +0000

Let's talk about a smart, modular approach to filegroups on MSSQL.
The idea is simple but powerful, isolate your schemas into their own filegroups.
This not only gives you better control over performance and backups, but it also opens up options for maintenance and recovery that you wouldn't have with a one filegroup strategy.
Sounds good ? Let's dive in.

Why You Should Care About Filegroup Architecture

Here’s the deal, your filegroup setup might seem like a minor part of database design, but it can affect how well your database performs, how fast you can back it up, and how easily you can manage it over time.
The way you set up your filegroups can also determine how easy it is to recover from an issue or even move data around when needed.

Instead of tossing everything into the PRIMARY filegroup or continuing adding more filegroups on top (just to split size of files), isolating schemas into separate filegroups gives you more control over your environment.

That means:

Better Performance: Distribute the workload across different physical disks for faster I/O.
Faster and Smarter Backups: Want to restore just part of the database ? No problem.
Minimal Downtime for Maintenance: You can work on one part of the database while the rest stays up and running.

Benefits You'll Love..

1. Selective Restore means Less Downtime

One of the coolest things about isolating schemas into their own filegroups is that you can restore them individually.
Let's say you have a corruption issue in the "[abe_eu]" schema. Because "[abe_eu]" is in its own filegroup "[abe_eu_fg]", you can just restore that filegroup without affecting the rest of the database.
This can save you hours (or even days) of downtime, depending on your database size.

Plus, because the PRIMARY filegroup holds system objects, SQL Server can still bring the database online even if other filegroups are down.
Your database stays mostly available while you're restoring just the affected part.

2. Offline Filegroups for Easy Maintenance

Want to move a file around, or switch up the storage location of some .ndf files ?
If you need to do some heavy maintenance (like moving data to a new disk), you can take just one filegroup offline while leaving the rest of the database online.

For example, if you need to migrate the abe_eu_fg FileGroup but don't want to disrupt everything else, you can just take abe_eu_fg offline temporarily.
The rest of your database will still be accessible to users, and you're free to do your thing with minimal impact on the service.

3. Better Schema Isolation and Flexibility

By isolating each schema in its own filegroup, you give yourself more flexibility for a ton of future use cases. Here's why it matters:

Security: You can set specific access policies at the filegroup level. If you want more control over who can access what, this gives you an easy way to manage that.
Data Movement: Moving a schema around becomes way easier. If you need to archive or migrate.
Performance Tuning.

SQL Code example:
Set up schema and filegroup

USE [AbeDB]
CREATE SCHEMA [abe_eu]
CREATE SCHEMA [abe_us]
GO

ALTER DATABASE [AbeDB] ADD FILEGROUP [abe_eu_fg]
ALTER DATABASE [AbeDB] ADD FILEGROUP [abe_us_fg]
GO

ALTER DATABASE [AbeDB] 
ADD FILE ( NAME = N'abe_eu_fg', FILENAME = N'D:\MSSQL\YourInstance\abe_eu_fg.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 
TO FILEGROUP [abe_eu_fg]
GO
ALTER DATABASE [AbeDB] 
ADD FILE ( NAME = N'abe_us_fg', FILENAME = N'D:\MSSQL\YourInstance\abe_us_fg.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 
TO FILEGROUP [abe_us_fg]
GO

Set up your tables when creating them

CREATE TABLE [abe_eu].[TableTest]
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
) ON [abe_eu_fg];

Set up permissions on role

CREATE ROLE [abe_eu_r]
GO
ALTER ROLE [abe_eu_r] ADD MEMBER [testUser]
GO
GRANT SELECT ON SCHEMA::[abe_eu] TO [abe_eu_r]
GO

One of the most powerful aspects of this filegroup architecture is the ability to restore individual filegroups, rather than the entire database.
For example, imagine you encounter a corruption in one of your schema/filegroup. Since it is isolated in its own filegroup, you can restore other filegroups, leaving the rest of the database operational.

SQL Code example:
Do your maintenance

ALTER DATABASE [AbeDB] MODIFY FILE ( NAME = abe_eu_fg,   
   FILENAME = 'F:\MSSQL\TempFolder\abe_eu_fg.ndf'); 
GO
ALTER DATABASE [AbeDB] MODIFY FILE (NAME = 'abe_eu_fg', OFFLINE);
GO

After maintenance

RESTORE DATABASE [AbeDB] FILEGROUP = 'abe_eu_fg' WITH RECOVERY
GO

By taking the time to design your filegroup strategy early on, you're not only optimizing for current requirements but future-proofing your database architecture.
Whether you're dealing with large datasets, frequent schema changes, or high availability requirements, a modular filegroup approach will give you the tools to keep your SQL Server environment running smoothly and efficiently.
Check your database status, with filegroups, and sizes

 SELECT [databasefile].NAME  AS [FileName],
  [filegroup].NAME           AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id

Don’t miss my other posts for more tips and best practices.

Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver17

MSSQL Encryptions, Certificates, A-Symmetric Keys and more (Vol 1)

Amar Abaz — Sun, 11 Jan 2026 15:36:22 +0000

In today's world, protecting sensitive data is a top priority.
I'll be covering this topic in two volumes. I will explore encryption strategies within Microsoft SQL Server, focusing on best practices for securing your data. The second volume will dive into practical examples and some extra tips. In this section, we will focus on the best practices and available options, including

Service Master Key (SMK) and Database Master Key (DMK): Key components of key management in SQL Server. Important in securing your database and ensuring key recovery.
Symmetric and Asymmetric Keys: Difference and use cases for symmetric (single key) encryption versus asymmetric (public/private key) encryption, and how they protect data at rest and in transit.
Certificates: They are a more secure and scalable solution for key management, and they improve your encryption strategy.
Transparent Data Encryption (TDE): TDE automatically encrypts an entire database to protect data at rest.
TLS Encryption: Protecting data in transit, ensuring that the data moving between your SQL Server instance and client applications is encrypted and secure.
Column-Level Encryption: A look at how you can protect sensitive data at the column level, offering flexibility for encryption on specific data within your database.

Properly managing Certificates, Symmetric keys, and Asymmetric keys can be tricky. That is why it's important to avoid relying on simple passwords, and why you should instead leverage Database Master Keys (DMK), Service Master Keys (SMK), and Certificates for a more robust and recoverable encryption strategy.

Database Master Key (DMK) and Service Master Key (SMK)

Both the DMK and SMK are crucial for key management in SQL Server.
The DMK is used to protect certificates and symmetric keys within a database. It's essential for managing encryption at the database level.
The SMK is tied to the SQL Server instance and is used to encrypt the DMK and other server level secrets. After restarting SQL Server if the SMK and DMK are in place and were correctly created, they are reloaded into memory automatically when SQL Server starts. All your certificates (including those signed by the DMK) should be accessible because the SMK and DMK are available in memory to decrypt them.

Why Backups Matter
It's important to back up both the SMK and DMK to ensure that your encryption strategy is recoverable. Backups are critical for disaster recovery, key restoration, and server migrations. They are especially necessary if your server crashes, is rebuilt, or if keys are accidentally deleted or corrupted.

To check if you have SMK and DMK use this command:

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DBMasterKey##';

Command to create them:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password$1';

BACKUP MASTER KEY 
TO FILE = 'C:\ABEDMK' ENCRYPTION BY PASSWORD = 'Password';

BACKUP SERVICE MASTER KEY 
TO FILE = 'C:\ABESMK' ENCRYPTION BY PASSWORD = 'Password';

To check whether your certificates are protected by password or DMK use this command:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates;

Certificates

While SQL Server allows you to use passwords for encryption, using Certificates for key management is a much more secure and scalable solution.
The flexibility of certificates ensures that you can easily back up and recover keys without relying on passwords alone.
Using certificates not only enhances encryption but also allows you to elevate User rights by signing stored procedures. This can be incredibly useful in special cases where users need elevated permissions to perform certain tasks at the system level, such as sending emails or executing jobs from user database.
Certificates offer multiple encryption options, including algorithms like SHA-256 and RSA.
These algorithms ensure that your encryption processes are not only secure but also meet industry standards, providing a higher level of protection for your data.

Create and Backup your certificate with PK and secure it by this command.
Don't forget to write expiry date because by default it is one year.
RSA 2048 is the default encryption algorithm.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='dbPassword$1';

CREATE CERTIFICATE ABECert
WITH SUBJECT='Data_Encryption',
EXPIRY_DATE = '2040-12-31';

BACKUP CERTIFICATE ABECert
TO FILE = 'C:\ABECert'
WITH PRIVATE KEY (file='C:\ABECert_key.pvk',
ENCRYPTION BY PASSWORD='Password');

Because i haven't use ENCRYPTION BY PASSWORD, my certificate is protected by DMK.

Symmetric Keys combined with Certificate (for server-side encryption)

With symmetric keys, you can encrypt sensitive columns at the database level, ensuring that data is encrypted at rest and can only be accessed by authorized users or applications.
My advice for you is not to use encryption by passwords while creating them but rely solely on your protected Certificate.
This approach ensures that sensitive information is kept secure even if the database is compromised, as the data will only be readable when the permission to Certificate is available to user.
Once set up, you can encrypt your columns and decrypt them during SELECT, UPDATE, or INSERT operations.
We'll dive into a practical example of how to implement this case in the next volume.

List all symmetric key by this command:

SELECT * FROM sys.symmetric_keys;

With this command, I am creating a symmetric key that will rely on my certificate that I created earlier.

CREATE SYMMETRIC KEY ABESymmKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ABECert;

Asymmetric Keys and Always Encrypted (for client-side encryption)

Always Encrypted is an excellent feature that uses asymmetric keys for encrypting and decrypting sensitive data transparently.
This way allows you to encrypt sensitive columns directly from the application, ensuring that the decryption key never leaves the client-side application. This ensures maximum data protection.
Always Encrypted ensures that data is encrypted on the client side and stays encrypted when it’s stored in the database, with no possibility of anyone (including DBAs) reading the sensitive data without the decryption keys.

Check if exist by using this command:

SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys;

When you INSERT, UPDATE or SELECT, SQL Server will automatically encrypt it using the column encryption key (CEK).
There are two ways of encryption, DTERMINISTIC and RANDOMIZED. Difference is that with Deterministic encryption, identical values (example "Abeamar", "Abeamar") will always encrypt to the same hash.

Once you create your Certificate (in my case CMK_ABEAE_Cert), import it into the server's credentials. Then, to enable Always Encrypted on a column, use the following command:

CREATE COLUMN MASTER KEY [AbeMK]
WITH
(
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', 
    KEY_PATH = 'LocalMachine/My/AbeMK_Cert');

CREATE COLUMN ENCRYPTION KEY [AbeCEK]
WITH VALUES
(
    COLUMN_MASTER_KEY = [AbeMK],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0064003900620064006600370032003100650033003400660031003000640033006600390065003200310062003800310035003100310033003300640062003700610061003700660039003100620035001532B80FF86E6773B5DDB0E4A83B1568DA8646400A9612556FED5210F598D8198F7F89E440E891A3DC99ACE3B381FD40E92FFCE7351F1F74DADBE7C591CBA95C94F9640181AA8D3111F20B39FF4F4A0DBA34F1CB1402B7429893BCA3F89607D208FE63236FA13032CB546F5D521360E761E4555DA78D42C061D09B3C55287928CE25FE182C237CA94B57697BE6F0935655DD0F208E2EEEDA357EB99FC27816D79D217E8D913FCDD1690AF7291EF8D452D340266F4A9AE9F964F05B7718F211A400850AB4C95AF417F5F3080CFAD51CA7AE01634FE24DBF050D7C2FAB6759FB92D1302EB4467E55591C1AD44E3F44051BC545F979A6B951962C6C7237B92ABF1FD94E1197AF7C26A3D9C9BF6CF6C7C42C4FE1032084F49233743C5831F38199633A0F86145FEF95EA4187E6F98A67BF206DE5A36258D0A37B3E2D6E353ACF357F1FAEE7B9E59A81C8D223B3A4F70E55C3614AD7134A7C564F0A386C61998CB9E859423D855F23E57E069A70FD568BA9783472EC44354DACA35D5D4FA109DFC444F39143E2A4BD76810D184F7A36CFBF1449D25A0C011DB502D355AB41C47BCF48A6A74D9B53D51C68C431D4CE47FD5C7CF3A050B2CB58B540CDFBDE977C3BCB1CD3C7C7EB7783F30B8BCA9FD3BD30CBF5C8A71AC0480357CB50410E832DD70F790888FE9F14825E64639B5CA59980AAD20C20C22B9207E98AB4CF1D817EF104B4
)
GO

CREATE TABLE Abe_Users
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    phones NVARCHAR(11)
);

Now, all you need to do is provide your clients with the CEK and inform them to enable Always Encrypted so they can decrypt the data.

To encrypt a column "phones", simply right click on the table and select "Encrypt Column".

Keep in mind that if your application involves frequent data imports or exports, or handling large volumes of data every day the encryption and decryption process may slow down overall performance. Each time data is accessed, it must be decrypted for processing and then encrypted again before storage, which can introduce overhead.

Final thoughts on A-Symmetric options

Use Symmetric Encryption when performance is a priority and the database or infrastructure team controls the environment, with appropriate safeguards for key management.
Use Always Encrypted with Asymmetric Keys if data security is more important than performance, especially if you need to ensure that even DBAs can't access the data.

And if the level of security required isn't extremely high, and you're more focused on preventing unauthorized access in a lighter way, data masking function is an excellent option. It strikes a balance between usability and security, with practically no performance overhead, no encryption needed. Make it simple, just mask it.

ALTER TABLE [Abe_Users]
ALTER COLUMN [phones] ADD MASKED WITH (FUNCTION = 'partial(2,"XXXXXXX",1)');

Transparent Data Encryption (TDE)

This is another powerful encryption method that encrypts the entire database, including the transaction log, automatically and transparently.
It relies on a Database Encryption Key (DEK), which is encrypted by a certificate stored in the master database.
TDE is useful for securing data at rest, particularly in scenarios where you need to protect the physical files of the database.
We'll dive into a practical example of how to implement TDE in the next volume.
You can check if your database is encrypted, or how much time would it take after triggering encryption using this command:

        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
                 WHEN '0' THEN 'No encryption'
                 WHEN '1' THEN 'Unencrypted'
                 WHEN '2' THEN 'In progress'
                 WHEN '3' THEN 'Encrypted'
         WHEN '4' THEN 'Key change in progress'
         WHEN '5' THEN 'Decryption in progress'
         WHEN '6' THEN 'Protection change in progress'
                 ELSE 'No Status'
              END,
        percent_complete, 
        create_date, 
        key_algorithm, key_length,
        encryptor_thumbprint, 
        encryptor_type  
        FROM sys.dm_database_encryption_keys;

By using certificates for encryption, employing Database Master Keys (DMK) and Service Master Keys (SMK) for recovery and security, and leveraging Transparent Data Encryption (TDE) for database protection, you can create a robust, recoverable encryption strategy that ensures your data is safe even in the worst case scenario.

😎
Don't miss my other posts for more tips and best practices.
I'll be covering this topic in two volumes, with the second one focusing more on practical examples and also on TLS protections to encrypt data transmitted between a SQL Server instance and a client application, ensuring data privacy and integrity.
Stay tuned..

References:

Craft and Control Resource Pools on MSSQL

Amar Abaz — Wed, 07 Jan 2026 21:52:46 +0000

Time to take control of your server's resources..
On your MS SQL instance not all workloads should be treated equally.
One heavy query executed from SQL Server Management Studio (SSMS) can easily consume CPU and memory, impacting production workloads.

Resource Governor is an underused but powerful feature in SQL Server.
With a simple function and resource pool, you can dramatically improve stability and predictability in multi-user environments.

In this post, I’ll show how to use SQL Server Resource Governor to,

Detect connections coming from SSMS,
Assign them to a limited workload group,
Restrict their CPU and memory usage.

What is Resource Governor ?
Resource Governor is a SQL Server feature that allows you to control how much CPU and memory different workloads can consume.
This approach is especially useful for production safety, or DBA-controlled access.

After this setup any query executed from Microsoft SQL Server Management Studio will be assigned to a limited resource pool.
All other connections remain in the default pool.

This also means you can customize resource pools to suit your specific needs by modifying the function below.

You can control CPU, RAM resources for:

Specific SQL users,
Reporting or BI tools,
ETL jobs,
Ad-hoc users,
Third-party applications,
Nightly or background workloads. All you need to do is modify the function and route those sessions into the appropriate workload group.

Let's begin with example on limiting CPU & RAM for sessions comming from SSMS Studio,
1. Enable Governor.

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

2. Create your function.
This will decides which workload group a session should use.

CREATE FUNCTION [dbo].[ResourceGroup_Users]
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup SYSNAME;
DECLARE @ProgramName SYSNAME;

SET @ProgramName = CONVERT(SYSNAME, PROGRAM_NAME());
IF @ProgramName like 'Microsoft SQL Server Management Studio%'
BEGIN
    SET @WorkloadGroup = 'LimitedResourceGroup';
END
ELSE
BEGIN
    SET @WorkloadGroup = 'default';
END

RETURN @WorkloadGroup;

END
GO

Also, if you want to restrict specific SQL users, you can easily modify this function by adding an additional condition - IF, or ELSE IF.

IF SUSER_NAME() = 'testuser' THEN ...

This allows you to route specific users to a resource pool with customized limits.

3. Create a Resource Pool.
This ensures SSMS queries can never consume more than 30% CPU, 30% memory.

CREATE RESOURCE POOL [LimitedResourcePool]
WITH
(min_cpu_percent=0,
max_cpu_percent=30,
min_memory_percent=0,
max_memory_percent=30
)

4. Create a Workload Group.
Now we link a workload group to the limited resource pool.

CREATE WORKLOAD GROUP [LimitedResourceGroup]
USING [LimitedResourcePool];
GO

5. Enable the Classifier Function.
Finally, tell Resource Governor to use our function and reconfigure it.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=[dbo].[ResourceGroup_Users]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Now you check what you have created with these commands,

SELECT * FROM sys.resource_governor_configuration
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups

Keep in mind that,

These changes apply only to new sessions, not to existing ones. You should restart the SQL service or terminate sessions for changes to affect.
Resource Governor is available in SQL Server Standard Edition starting with SQL Server 2025. For all previous versions, it is an Enterprise-only feature.

To monitor which sessions are currently using your resource pool, you can run the following query. This will help you ensure that the sessions are properly classified and using the intended resources.

SELECT
s.session_id,
s.login_name,
rg.name AS resource_pool_name
FROM
sys.dm_exec_sessions AS s
JOIN
sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
JOIN
sys.dm_resource_governor_resource_pools AS rg
ON wg.pool_id = rg.pool_id
WHERE
s.is_user_process = 1;

6. Limit RAM/CPU usage for the entire SQL Server instance
While Resource Governor allows you to assign specific resource limits to workloads, it’s also important to know that you can limit the total memory and CPU usage for the entire SQL Server instance itself.
This prevents SQL Server from consuming all the available memory, leaving resources for other processes.
You can limit the maximum amount of RAM using this command:

EXEC sp_configure 'max server memory', 4096;  --Set maximum memory to 4 GB
RECONFIGURE;

You can limit the CPU usage by altering the worker thread using this command:

EXEC sp_configure 'max worker threads', 512;  --Limit worker threads
RECONFIGURE;

Now go ahead and put those resource pools to work.
I hope this helps you keep your SQL Server running smoothly!
Don’t miss my other posts for more tips and best practices.

Track Blocking and Deadlocks in MSSQL with my custom script

Amar Abaz — Sun, 04 Jan 2026 13:05:14 +0000

Blocking happens when one process holds a lock on a resource (a row or table..) and another process is waiting for that resource to be released.
The second process is blocked and will have to wait for the first process to release the lock.

Deadlocks, on the other hand, occur when two or more processes are waiting for each other to release locks on resources, creating a circular dependency.
Process 1 has Resource A locked, and Process 2 has Resource B locked.
They each need the resource the other holds to proceed, and since neither can proceed without the other releasing a lock, the sessions are at a stalemate. One of them is going to be a victim and the other session will be processed.

I wanted to have accurate information about locks on my databases. I did that using the right extended events and then created a custom SELECT query to extract the right information from the created file and present it in my custom reports.

- Key columns for deadlock sessions are deadlock victim, deadlock object, sql_text and the users in process. All with clear overview.

- Key columns for blocking sessions are blocking start and end time, duration, sql text and users in process. It's all displayed in ONE line, so it gives you a clear overview.

Below is an overview of the information you get at the end.

First we need to enable blocked process treshold and then create Extended Event. Treshold min value is 5 sec to check for blocks.

exec sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
exec sp_configure 'blocked process threshold (s)',5;
GO
RECONFIGURE;
GO

Blocking Event

CREATE EVENT SESSION blckCapture
ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.client_hostname
    ))
ADD TARGET package0.event_file(SET filename=N'C:\blckSessions.xel',max_file_size=(100),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 kB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=36000 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION blckCapture ON SERVER STATE = START;
ALTER EVENT SESSION blckCapture ON SERVER WITH (STARTUP_STATE=ON);
GO

Deadlock Event

CREATE EVENT SESSION deadlckCapture
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\dlckSessions.xel',max_file_size=(10),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION deadlckCapture ON SERVER STATE = START;
ALTER EVENT SESSION deadlckCapture ON SERVER WITH (STARTUP_STATE=ON);
GO

Create Views

Last step is to create Views in database of your choice. And after that you can create your reports.

CREATE VIEW vw_blckSessions AS
WITH blckData AS (
    SELECT
        DATEADD(HOUR, 1, event_data.value('(event/@timestamp)[1]', 'DATETIME')) AS EventTime,
        blocked_process.value('@spid', 'INT') AS BlockedSPID,
        blocking_process.value('@spid', 'INT') AS BlockingSPID,
        blocked_process.value('@hostname', 'NVARCHAR(256)') AS BlockedHostname,
        blocked_process.value('@loginname', 'NVARCHAR(256)') AS BlockedLoginName,
        blocked_process.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS BlockedSQLText,
        blocking_process.value('@hostname', 'NVARCHAR(256)') AS BlockingHostname,
        blocking_process.value('@loginname', 'NVARCHAR(256)') AS BlockingLoginName,
        blocking_process.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS BlockingSQLText
    FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file('C:\blckSessions*.xel', NULL, NULL, NULL)) AS Data
        CROSS APPLY event_data.nodes('//event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report') AS XEventData (blocked_report)
        CROSS APPLY XEventData.blocked_report.nodes('blocked-process/process') AS BlockedProcessNode (blocked_process)
    CROSS APPLY XEventData.blocked_report.nodes('blocking-process/process') AS BlockingProcessNode (blocking_process))
    ,blckData2 AS (SELECT
                    CONVERT(VARCHAR(19), MIN(EventTime), 120) AS Eventime_start,
                    CONVERT(VARCHAR(19), MAX(EventTime), 120) AS Eventime_last,
                    DATEDIFF(SECOND, MAX(EventTime), MIN(EventTime)) as Duration,
                    BlockingSPID,
            BlockingHostname,
            BlockingLoginName,
            BlockingSQLText,
            BlockedSPID,
                BlockedHostname,
                BlockedLoginName,
                BlockedSQLText
            FROM blckData
            GROUP BY BlockedSPID, BlockedHostname, BlockedLoginName, BlockedSQLText, BlockingSPID, BlockingHostname, BlockingLoginName, BlockingSQLText)
    SELECT
                    Eventime_start
                    ,Eventime_last
                    ,ABS(Duration) AS Duration
                    ,BlockingSPID
                    ,BlockedSPID
                ,BlockingHostname
                ,BlockingLoginName
                ,BlockingSQLText
                ,BlockedHostname
                ,BlockedLoginName
            ,BlockedSQLText
    FROM blckData2;

CREATE VIEW vw_dlckSessions AS
    SELECT
    event_data.value('(event/@timestamp)[1]', 'DATETIME') AS DeadlockStartTime,
    deadlock_node.value('@hostname', 'NVARCHAR(256)') AS Hostname,
    deadlock_node.value('@loginname', 'NVARCHAR(256)') AS LoginName,
    deadlock_node.value('@spid', 'INT') AS SPID,
    deadlock_node.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS SQLText,
    resource_node.value('@objectname', 'NVARCHAR(256)') AS ObjectName,
    CASE
        WHEN deadlock_node.value('@id', 'NVARCHAR(256)') = victim_node.value('@id', 'NVARCHAR(256)')
        THEN 1
        ELSE 0
    END AS Victim,
    CASE
        WHEN deadlock_node.value('@id', 'NVARCHAR(256)') = victim_node.value('@id', 'NVARCHAR(256)')
        THEN 'Yes'
        ELSE 'No'
    END AS Evicted
    FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\dlckSessions*.xel', NULL, NULL, NULL)) AS Data
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/process-list/process') AS ProcessNode (deadlock_node)
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/resource-list/keylock') AS ResourceNode (resource_node)
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/victim-list/victimProcess') AS VictimNode (victim_node);

With these two views you can monitor Session IDs on your sql instance with all the necessary information about time, history, sql text and more to debug.

SELECT * FROM vw_dlckSessions 
ORDER BY DeadlockStartTime DESC;

SELECT * FROM vw_blckSessions 
ORDER BY Eventime_last DESC;

To wrap things up, here are some tips to prevent deadlocks and blocking by optimizing your queries for better performance.

Deadlock Prevention Tips,

Lock Resources in Order. Always acquire locks in the same sequence to avoid circular waiting.
Keep Transactions Short. Minimize the duration of locks by limiting transaction size.
Use Retry Logic. Automatically retry transactions that are killed due to deadlocks.

Blocking Prevention Tips,

Optimize Queries. Efficient queries reduce execution time and lock duration.
Use Proper Indexing, speed up data access to minimize lock contention.
Limit Simultaneous Writes, reduce conflicting updates.

Linked Server vs OPENQUERY vs OPENROWSET vs OPENDATASOURCE

Amar Abaz — Sat, 03 Jan 2026 23:04:58 +0000

MS SQL Server provides several ways to execute remote (distributed) queries, but choosing the wrong approach can easily lead to performance bottlenecks, security exposure, and long-term maintenance issues.
Remote queries allow you to access tables on different servers or instances, perform data filtering and transformation across servers.

In this article, I will compare the four most common approaches,

For each method, I will cover,
How it works, where is it useful, what are the cons and a simple example.
At the end, I will explain why OPENQUERY is usually the best choice in real-world production systems, when combined with proper Linked Server configuration.
Before each test, enable statistics with command below.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

These settings will help us compare Logical reads, CPU time, execution behavior.

Linked Server

This is the most common way to query remote data in SQL Server.
Easy to write and read. No need for dynamic SQL for execution and variables.
Linked Server passwords are not hashed, but they are stored encrypted and never exposed in clear text, it provides a secure authentication.
Has mapping options (impersonate current login, use a specific remote login, or a fallback).
Using Linked Servers ensures that there is a clear, controlled configuration of the remote connection with proper authentication, encryption, and validation.
Filters are not always pushed to the remote server and this can result in large data transfers, high network usage, poor performance on large tables. In many cases, SQL Server will pull all rows from the remote table and apply the WHERE filter locally.
Connection is reusable.

sp_addlinkedserver 'w19';
SELECT * FROM [w19].[AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > '2011-05-31';

CPU Time: 4717 ms
Elapsed Time: 7734 ms

OPENQUERY

Query is executed entirely on the remote server. You must use Dynamic SQL. Filters and joins are applied remotely. It is a best choice for large tables and production workloads. OPENQUERY inherits Linked Server security and configuration. When querying non-SQL Server databases (ORACLE, MySql, PostgreSQL) this is the way. Connection is reusable.

SELECT * FROM OPENQUERY([w19], '
SELECT * FROM [AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > ''2011-05-31''');

CPU Time: 3367 ms
Elapsed Time: 5443 ms

OPENQUERY is designed to work with linked servers, meaning it sends a query to another server, which could be a remote database or a file system via a linked provider (like OLE DB or an ODBC driver) but you can do for example CSV also. For large files OPENROWSET tends to be faster.
You would need first to create path with Linked Server, and then you can use OPENQUERY. Code below:

EXEC sp_addlinkedserver 
    @server = 'FileServer', 
    @srvproduct = '', 
    @provider = 'Microsoft.ACE.OLEDB.12.0', 
    @datasrc = 'C:\Temp\EmployeeData.csv',
    @provstr = 'Text;HDR=YES;FMT=CSVDelimited';
SELECT * 
FROM OPENQUERY(FileServer, 'SELECT * FROM [EmployeeData.csv]')

One of the major advantages of using Linked Servers with OPENQUERY is the ability to centrally control and redirect connections. In a single script, you can point your links to different servers, for example switching between DEV, UAT, or production environments, without changing your query code.

SELECT name, provider, data_source FROM sys.servers;
DECLARE @name sysname = 'w19', @datasource sysname = 'w19\sql';
EXECUTE sp_setnetname @server = @name, @netname = @datasource;

The next two options - OPENROWSET and OPENDATASOURCE require AdHoc Distributed Queries to be enabled on the instance.
They are disabled for security reasons. When this feature is enabled, it allows users to execute queries that can directly access remote data sources without needing to configure a Linked Server.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

OPENROWSET

It is mainly designed for ad-hoc access to external data sources. Harder to govern. Unlike Linked Server and OPENQUERY, it does not reuse connections. Each execution performs a new connection handshake, authentication, query execution, and disconnect. This makes it unsuitable for frequent or high-volume workloads. However it is good for reading external files (CSV, Excel), temporary data access without creating a Linked Server (connection is direct). It allows 3-part-name in connection.
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended. Requires DSN or inline credentials (Password Exposure).

SELECT * FROM OPENROWSET(
    'SQLNCLI',
    'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;',
    'SELECT * FROM Sales.SalesOrderDetail 
WHERE ModifiedDate>= ''2011-05-31''''
);

-- CSV/Excel example:
SELECT * FROM OPENROWSET(
    BULK 'C:\\Temp\\EmployeeData.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) AS data;
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=NO;
   Database=C:\Temp\EmployeeData.xlsx',
   [Sheet1$]);

CPU Time: 4503 ms
Elapsed Time: 7594 ms

OPENDATASOURCE

This is very similar to OPENROWSET and shares most of the same limitations. It also performs a new connection handshake on each execution.
You do not send a query string, you expose the remote server as a temporary data source and reference objects (4-part-name in connection)..
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended.
Requires DSN or inline credentials (Password Exposure).

SELECT * FROM OPENDATASOURCE(
    'SQLNCLI',
    'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;'
).AdventureWorks2022.Sales.SalesOrderDetail;

-- CSV example:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\Temp;HDR=YES')..[EmployeeData#csv]

CPU Time: 4592 ms
Elapsed Time: 7718 ms

I was using AdventureWorks sample database for these examples.

WinForms in 2025? Here's the Slick MSSQL Monitoring App I Built

Amar Abaz — Tue, 07 Oct 2025 22:49:26 +0000

Monitor MSSQL performance with this custom app

Ever had to dig through multiple tools just to debug a failed MSSQL backup or trace an error switching from multiple MS SQL Instances ? 😥🤯

That’s why I built a small tool in Visual Studio, using C#, WinForms, Powershell, T-SQL, WMI to monitor SQL Server health, logs, and performance stats in one place.

The primary focus of my project was to make an app that will have all the necessary tools for database administrator in their everyday monitoring work.

What this tool monitors:

Disk usage and growth
Memory and CPU utilization
Database health and size
Backup and restore history
Active backup and restore operations
Table sizes and index fragmentation
SQL Server error logs
SQL Agent job failures
Schema object definitions
Raw SQL object extraction
Active sessions and blocking queries
Deadlock tracking
Custom session-level monitoring for your SQL Server instance

Feel free to contact me if you like what you see or have ideas what to improve or add....
I'd love to hear your feedback! 🤞

Video link: https://vimeo.com/1124492178

I've published the app also on,
https://www.producthunt.com/products/abemon

🧑‍🚀 Check the app on my GitHub:

abeamar / AbeMON

This App is for DBA everyday work on monitoring and troubleshooting on MSSQL. App created using Visual Studio, C#, WinForms, T-SQL, WMI.

AbeMon App 1.0.0

This App is for Light DBA everyday work on monitoring and troubleshooting on MSSQL.
App created using Visual Studio, C#, WinForms, T-SQL, PowerShell, WMI.

1. About
2. Options
2.1. System
2.2. Errors
2.3. Sessions
2.4. Locks
2.5. Export
2.6. Debug
3. Conclusion

1. About

The primary focus of this project is to make an App that will have all the necessary tools for database administrator in their everyday monitoring work. This app is a fully functional tool.
The app is provided as a trial for 30 days, allowing you to experience its full functionality. Feel free to contact me if you like what you see. This App is designed to deliver smooth performance while using minimal system resources, consuming just 10-20MB of RAM.
Full Article on dev.to: https://dev.to/abeamar/abemon-ms-sql-tool-v100-monitor-and-debug-60m

🐔 AbeMon Install options:

git clone "https://github.com/abeamar/AbeMon.git"

Important

Choose the installation method that works best for you!

Using the…

View on GitHub

Cheers,
Amar.