Tuning and Troubleshooting Transactional Replication – Kendal Van Dyke From this video – https://www.youtube.com/watch?v=UBdAAvMMGwo Types can be configured: Central publisher (One Pub & DIS and multiple subscribers) – Common used Central subscriber (On Sub, many DIS and many Bub) – This is mostly for data warehouse Republishing subscriber (Pub –> Dis àSub/Pub àDIS àmultiple subscriber) – Ex: Pub & DIS are in datacentre 1 & multiple subscriber in DC 2   Replication agent will not use SQL memory, it will use other buffer cache maybe OS or within SQL. It is recommended to have separate Publisher and Distributer for high transaction databases. For every DML command each transaction from publisher to subscriber will…

Use backup to initialize without drop Publication Subscription Test – For existing Replication which was initialized using snapshot files, remove both publication (Script out Pub) and subscription and try it , which is configured by using snapshot method. Better script out Publication and distributor. Both removing only subscription (OR) publication and subscription – both are working. Drop subscription from the publication and remove the publication. While creating the publication we need to change parameter @sync_method = N’concurrent’ to @sync_method = N’native’ Try add new table in GUI and start the snapshot agent and check –Working Try adding new table by using sp_addarticle  (Do not run sp_refreshsubscriptions) T-SQL for single article and…

Test 2 For existing Replication which was initialized using snapshot files, remove only subscription and test. Try with existing publication initialize from backup and add table from GUI and T-SQL. Try add new table in GUI and start the snapshot agent and check –Working Try adding new table by using sp_addarticle  (Do not run sp_refreshsubscriptions) T-SQL for single article and start the snapshot agent and check – Working Note: Only issue if we leave all tables in subscriber database after the restoration, if you want to replicate later remaining tables that’s not replicating it. If you drop tables in subscriber and add it in the replication it is working. Add only single table…

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…