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. DECLARE @distributionDB SYSNAME; DECLARE @publisher SYSNAME; DECLARE @publicationDB SYSNAME; SET @distributionDB = N'distribution'; -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. SET @publisher = N'node1'; -- CHANGE THIS TO THE PUBLISHER NAME. SET @publicationDB = N'muthu'; -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. -- Remove all replication objects from the publication database. USE muthu…

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 location for both mdf & ldf OR only LDF -- If you have more MDF and LDF for a single database, you have to do it for each use master ALTER DATABASE distribution MODIFY FILE (NAME = distribution, FILENAME = 'C:\Dist_DB\distribution.mdf'); ALTER DATABASE distribution MODIFY FILE (NAME = distribution_log, FILENAME = 'C:\Dist_DB\distribution.ldf'); --Step 3 --…

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 night or weekends to minimize the impact of the snapshot agent locking tables and contention on the publisher database.   Change snapshot location for all publication   /*Change path will generate new snapshot BCP file in the new location but this will not apply into subscription*/   USE distribution exec sp_changedistpublisher @publisher = ‘Node1’, @property…

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…