Common issue and fix – Test it out  Transaction log grow very larger DML operation done on subscriber will stop replicate data DDL changes   With the default profile do the following test.   Ex: 1 – Insert Manually insert rows in subscriber 2. Insert the same data in publisher as well– That will break the replication, since PK violation. Fix: Whatever is inserted manually needs to be deleted from subscriber with where condition. Ex: 2 – Delete Insert three rows on publisher, after rows replicate 2. Delete in wrong order i.e delete first from subscriber 3. Then delete it from publisher Fix: Insert that deleted rows in the subscriber Ex: 3 –…

Transactional Replication Design: Node1 – distributor & Publisher. One publisher with many subscribers. Pre request: Repl_Dist_Pub_connection — sp_adddistpublisher@login mainly used to connect distributor and publisher initial configuration. Remember or store the password for adding another publisher for this remote distributor will need this password. Create new threeAD service account or SQL login for replication job agents run i.e. @job_login. Some prefer to run all 3 in single account as well. Muthu\Svc_repl_snapshot , Muthu\Svc_repl_logreader & Muthu\Svc_repl_distributor We need 4 SQL or windows login for connect publisher and subscriber to do data movement. Ex: 4 login are SQL login names: Repl_Connect_Snap_Agent — sp_addpublication_snapshot @publisher_login Repl_Connect_Log_Agent — sp_addlogreader_agent @publisher_login Repl_Connect_Dist_Agent — sp_addpushsubscription_agent @subscriber_login   Total we need 7 accounts and…

Check Distributor property most of the details available there. Transaction and history retention of replication object how long to store.   Publication Retention Change each publication from 336 hours to never expire. Transaction Retention Period The first setting is the Transaction Retention Period. This is defined on the distribution database. It specifies how long the transaction information is kept in the distribution database. So that subscriber can read it. History Retention setting This defines how long information about the health of the replication setup is kept in the distribution database. This setting does not affect the subscribers.   Profile Profile is much important to validate the data and pass throw…

Transactional Replication  Important note: If you have replication in your environment. First script out distributor and publication including property. Script out each publication. List out how many tables / objects are added into replication with table name, any filter applied etc. Also, generate script out of subscriptions database, in case if we reapply the snapshot the existing object will drop and created. If we have any triggers, additional index that won’t come. We need to create those after reapply. Design: Node1 – distributor & Publisher. One publisher with many subscribers. Pre request: Repl_Dist_Pub_connection — sp_adddistpublisher@login mainly used to connect distributor and publisher initial configuration. Remember or store the password for adding another publisher…

When you have a replication in your environment what pre requesting we need to do. 1. Generate script out of subscriber database, include Trigger, indexes etc, useful for snapshot reapply. 2. Find which server is Distributor (Local same server or remote / different server), Publisher (All publication) And subscriber (All subscription) 3. Find out how replication topology designed. Ex: How many distributor, publisher and subscriber, one publication with many subscriptions or one publication with one subscription etc. 4. Script out all distributor, publication and subscription Go to replication folder in SSMS -> generate script of Distributor alone and save it Then choose generate script out of publication property and save…