SNHA28: How data loss might happen and how to prevent data loss from forced manual failover in AG

Welcome to the channel! In this session, we explore how data loss can occur during a forced manual failover in a SQL Server Availability Group (AG) and what you can do to prevent it.

🔍 What Causes Data Loss in AG?
In an AG setup with a Primary and an Asynchronous Secondary (DR), transactions are committed on the Primary and then sent to the DR.
If a network outage or Primary failure occurs before the transaction reaches the DR, and a manual failover is performed, the DR becomes the new Primary without knowledge of the un-transferred transaction.
When the original Primary comes back online, it demotes itself to Secondary and discards the uncommitted transaction, resulting in data loss.

⚠️ Key Risk Scenario
1. A transaction is committed on the Primary.
2. Before it reaches the DR, a failure occurs.
3. Manual failover is done to the DR.
4. The DR never received the transaction → Data is lost.

💡 How to Prevent It
The session promises a demonstration on how to do this effectively.

Full course sessions:
🚀►   • Learning path: SQL Server Availability Gro...  
🧑‍💼►Follow me on Youtube
🧑‍💼►Follow me on LinkedIn -   / bobirmirzo-arslanov-548960173  
Full course sessions:
🚀►   • Learning path: SQL Server Availability Gro...  

Watch all my playlists here:
🚀►Full course on Troubleshooting sessions:    • Troubleshooting  
🚀►Full course on Encrypting connections to SQL Server-    • Full course on Encrypting connections to S...  
🚀 ►SQL Server replication session:    • SQL Server replication  
🚀 ►SQL Server Always On Availability Group:    • SQL Server Always On Availability Groups (AG)  
🚀 ► SQL Server internals:    • SQL Internals  
🚀 ► Course in On-memory OLTP:    • Course on In-Memory OLTP  

Below is tablediff command
=====================
USE distributeddb
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- Run the difference command
SET QUOTED_IDENTIFIER ON

DECLARE @TableNames as table (
id int identity(1,1),
tableName varchar(100))

DECLARE @sTableDiff nvarchar(1000)
DECLARE @tableName varchar(100)
DECLARE @counter int
DECLARE @maxCount int

INSERT INTo @TableNames
SELECT name
FROM sysobjects WHERE type = 'U'

SET @counter = 1

SELECT @maxCount = COUNT(name)
FROM sysobjects WHERE type = 'U'

WHILE @counter @maxCount -- please add bigger sign here to make maxcount bigger
Begin
SELECT @tableName = tableName
FROM @TableNames
WHERE id = @counter
-- You should make sure that SQL Server service account has access to the location specified in '-f'
SET @sTableDiff= ' "C:\Program Files\Microsoft SQL Server\150\COM\tablediff" -sourceserver AG2019-SQLVM-1 -sourcedatabase distributeddb_snapshot -sourcetable ' + @tableName + ' -destinationserver AG2019-SQLVM-1 -destinationdatabase distributeddb -destinationtable ' + @tableName + ' -f F:\diff'

EXEC XP_CMDSHELL @sTableDiff

Set @counter = @counter + 1
End

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO