Pre request: Create replication using How_to_configure-Transactional_Replication_Issues_Reproduce.docx Note: After creating publication, we need to take differential backup and restore to subscriber. Test 1 Try with new publication using normal snapshot and initialize from backup and add table from GUI and T-SQL. Try start the snapshot agent and see.  – Working data is coming after initialize from backup and snapshot agent also starts and pushing it if we reinitialize it. -- This is one DB as publisher with four publication and subscriber model. This will have 4 snapshot agent & Dis agent, 1 log reader agent use master exec sp_adddistributor @distributor = N'Node1', @password = N'G0d$peed' GO -- Adding the agent profiles --…

Adding a table Adding article / table – we can do from GUI and T-SQL. But that will not replicate to subscriber until snapshot agent run again. The drawback is if we start snapshot agent that will generate snapshot for all article, to avoid this we can use T-SQL method to add single article. How to add table GUI and T-SQL To add table GUI Expand replication and local publication – Right click the publication property – Article à uncheck show only checked article – Add article and ok. This will not replicate until we start the snapshot agent as said above. To generate snapshot and replicate that single article…

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…