Category: Replication

Remove replication completely –https://jonathancrozier.com/blog/sql-server-replication-how-to-completely-remove-replication /* –on sub — Remove replication objects from the subscription database on MYSUB. DECLARE @subscriptionDB AS sysname SET @subscriptionDB = N’Muthu_Replica’ — Remove replication objects from a subscription database (if necessary). USE master EXEC sp_removedbreplication @subscriptionDB GO */ –on pub –Manually remove subscriber –Manually remove publication — Declare and set variables.…

Move distribution database Move [distribution] DB — Very smooth change   ———-note the existing details with logical name sp_helpdb ‘distribution’ distribution 1 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\distribution.MDF PRIMARY 58532864 KB Unlimited 65536 KB data only distribution_log 2 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\distribution.LDF NULL 180224 KB 2147483648 KB 65536 KB log only –Step 2 — change to new…

Change the snapshot location of all publication You can change the snapshot location without having to perform a re-initialization. The only caveat to this is you do need to generate a new snapshot. This snapshot will not be applied to your subscribers, but has to go through the generation process. I recommend doing this at…

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…

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…

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…

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…

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…

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…

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.…