SNHA28: How data loss might happen and how to prevent data loss from forced manual failover in AG скачать в хорошем качестве

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