Ali Shaik's Database Diary

Introduction: In SQL Server, computed columns are a powerful feature that allows you to create virtual columns with values calculated on-the-fly based on other columns' data. While computed columns can simplify data retrieval and improve data consistency, they can also pose challenges when it comes to replication. This blog post explores the issues associated with computed columns in SQL Server replication and presents solutions to tackle them. Understanding Computed Columns: A computed column is essentially a virtual column within a table. It does not physically store its data in the table unless explicitly marked as "PERSISTED." Instead, a computed column derives its value based on an expression that can use data from other columns in the same table. Challenges in Replication: Replication in SQL Server involves copying data from one database (the publisher) to one or more destination databases (subscribers). However, when dealing with computed columns, replication can e

Introduction: Snapshot generation in replication involves locking mechanisms that vary based on the replication method used. Let's unveil the types of locks encountered during snapshot generation and their implications in different replication scenarios. Snapshot Generation Locks: Snapshot Replication 📸: Snapshot replication employs exclusive locks that encompass the entire snapshot generation process. These locks ensure data consistency during the snapshot creation but can temporarily suspend other concurrent operations on the same data. It's like safeguarding a room while a thorough inspection is conducted. Transactional Replication 💼: In transactional replication, locks are momentarily acquired at the outset of snapshot generation and are swiftly released. This approach minimizes disruption to regular database activities, allowing them to continue almost immediately. Think of it as a brief checkpoint on a busy road. Merge Replication 🔄: Merge replication operates uniquely

Replication is a crucial component in maintaining data consistency across distributed systems. However, there are times when data fails to reach its intended subscribers. In this blog post, we'll explore some common reasons why data may not be delivered to subscribers in a replication setup. Filtered Tables with No Changes: If your replication involves filtered tables, data will only be sent to subscribers if there are changes that match the filter criteria. Ensure that changes meeting the filter conditions are indeed present. Non-Functioning Agents: Replication relies on agents to transfer data between the Publisher and Subscribers. If one or more agents are not running or are encountering errors, data delivery will be disrupted. Check agent status and logs for any issues. Trigger-Based Deletions or ROLLBACK Statements: Triggers can be a double-edged sword in replication. Data deleted by a trigger or a ROLLBACK statement within a trigger can prevent data from being delivered as ex

  Replication Type Schema Changes Handling Snapshot Schema changes made to the published database are automatically included in the next snapshot. Subscribers must be reinitialized with the new snapshot to receive the schema changes. Transactional Schema changes are applied to subscribers using a series of system stored procedures automatically generated. Stored procedures are created and executed on the subscriber to synchronize schema changes from the publisher. Merge Schema changes are handled using SQL Server scripts generated by the replication engine. Scripts are propagated to the subscriber databases where they are executed to apply the schema changes. It's important to note that schema changes can potentially cause replication to fail if they are not handled properly.  To avoid issues, it's recommended to carefully plan and test any schema changes before applying them to the published database.  Additionally, it's important to ensure that all subscribers are updated