Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. The snapshot process (which creates a copy of all of the objects and data specified by a publication) is also … Read more
Recientemente me enfrenté a un escenario muy interesante en el que para una base de datos se habilitaban tanto la captura de datos modificados como la replicación transaccional. Según la documentación de MS, ambas funciones pueden funcionar juntas, pero debemos tener algunas cosas en cuenta al implementar ambas funciones juntas en una base de datos. En … Read more
Recently I was faced with an interesting issue where upon rerunning distributor agent, complete snapshot was being applied even though we had got “snapshot has been delivered successfully” message. The activity was about adding newly created tables into the replication. Below were high level steps followed for the activity. After step 5, once we got … Read more
Replication is one of the trickiest things in SQL Server and especially when you don’t know the solution of the issue. One such issue we faced a few days back which we were able to solve using a small trick. Here is what happened.: Snapshot got generated on publisher but failed to apply on subscriber. … Read more
Continuing to previous post link, where I explained the reason of CDC capture job failure for a database enabled in transactional replication with remote distributor; here I would like to discuss the scenario of enabling change data capture for a database with local distributor. In this article we will discuss about interoperatibility of SQL server … Read more
We got one replication related alert for one of the critical database. Distribution agent was failing with below error message: The row was not found at the Subscriber when applying the replicated DELETE command for Table ‘[dbo].[TableXXX]’ with Primary Key(s): [PrimaryKeyID] = 12345678 (Source: MSSQLServer, Error number: 20598) Above error could be seen in replication … Read more
Recently I faced very interesting scenario where for a database both change data capture and transactional replication was enabled. As per MS documentation, both features can work together but we need to keep few things in mind while implementing both features together on a database. Coming to my case, although change data capture job was … Read more
Log File Truncation Pending for Replication in Snapshot Replication
I am writing this post as I recently faced error mentioned in title while I was migrating all objects from one SQL instance to another SQL instance as part of decommission plan of source instance. On source server, there was transactional publication was configured with local distributor. If you restore a replicated database to another … Read more
Symptoms: You are using SQL Server replication (transactional/merge/snapshot) subscribers for transactions. You have provided users some object level permissions on specific objects (tables/stored procedures etc.) at subscriber database. After applying the snapshot users complaint about missing permissions on subscriber database. Cause: In SQL server replication, while applying snapshot default behavior is to drop existing objects … Read more