John Liu Blog

Every drop counts

SQL Server Replication Setup

To setup SQL Server transactional replication, the publisher needs to be Standard or Enterprise edition. Subscriber can be any edition (except SQL Server Compact), but will require standard or Enterprise edition as well if configure merge (bi-directional) replication. We will need to create some local Windows accounts to run replication agents: Agent Location Account name Snapshot Agent Publisher <server name>\repl_snapshot Log Reader Agnet Publisher <server name>\repl_logreader Distribution Agnet Publisher/subscriber <server name>\repl_distribution Merge Agnet Publisher/subscriber <server name>\repl_merge On Publisher machine, create a file share for the snapshot folder.

Posted by John Liu Thursday, July 25, 2024

SQL Server Replication

There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication Peer-to-Peer Replication Column/FK/Constraint changes are replicated but trigger/index changes are not replicated. Changes are replicated almost instantly. Table with timestamp column is not supported, no work around. Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup).

Posted by John Liu Tuesday, September 6, 2022

Remove SQL Server Replication

Following script helps to cleanly remove all replication objects configured on the publisher server. ONLY run this script if you want to completely remove entire replication set. DECLARE @distributionDB SYSNAME = N'distribution' -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. default is distribution ,@publisher SYSNAME = N'' -- CHANGE THIS TO THE PUBLISHER NAME. ,@publicationDB SYSNAME = N'' -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. ,@subscriber SYSNAME = N''; -- CHANGE THIS TO THE SUBSCRIBER NAME, comma separated if more than one subscriber.

Posted by John Liu Monday, September 5, 2022