SQL Server transactional replication setup and synchronization experience

0. Environment

1. No domain environment
2. Publishing services and distributors on the same host

role	Host name	IP	Publication name	Publish library name/subscription library name
Publishing Server	Server1	192.168.1.100	Test3	Db1
Distribution server (same instance as publisher)	Server1	192.168.1.100	-	-
Subscriber server	Client1	192.168.1.200	-	Db1

1. Modify the Host File

At both the Publisher and the Subscriber, modify the C:\Windows\System32\drivers\etc file to include the IP and host name. If it is a pull subscription, the Publisher does not have to modify it, and if it is a push request, it must be set. Because SQL Server replication cannot make related settings over IP.

2. Create the snapshot folder and the appropriate users and permissions

For example, the snapshot file is D:\ReplData under the publisher. Create the same Windows users at the Publisher and Subscribers (if you do not want the publisher to create a new Windows user, you need to turn on the guest user at the publisher), such as Repl_admin, and join the respective administrator group, and set the password cannot expire, cannot change the password. Set the D:\ReplData file for the new user repl_admin full read and Write permissions, and set the share for this folder.

Here, there may be a question why repl_admin is already a user of the Administrator group, the D:\ReplData folder is Read permission, why do you want to set repl_admin full read and Write permissions? Later, when you generate a snapshot file, you cannot write to the folder \\Server1\ReplData . This folder is mentioned in the 4th step later \\Server1\ReplData .

3. Setting up the SQL Server agent startup user

At the Publisher settings, the SQL Server Agent startup user is repl_admin (preferably set in SQL Server Configuration Manager) and restarts the agent. Similarly, the same steps are done at the Subscriber. Note that restarting the agent may affect the SQL job's scheduled tasks, and check the impact of the restart on your production environment.

4. Create a new publication and distribution at the publisher

On SSMs, follow the wizard step-by-step to create a new publication. If there is no distributor, it is created in the wizard. (Distributed properties) sets the snapshot file to \\Server1\ReplData . Finally, you can generate the SQL script that creates the publication, save it, restart it later, or the other server needs to create the publication, just modify the script and then execute it.

Why not set it directly D:\ReplData ? It is possible, but by default \\Server1\ReplData This file is also read by default on the Subscriber \\Server1\ReplData , so it is convenient to use this snapshot folder.

Of course, you can modify it manually (you can modify the snapshot folder path for a publication separately, or you can modify the snapshot folder path at the time of subscription), but it is also recommended to use the network path format of the default file for the settings you distribute.

You can also use FTP or other methods to copy the snapshot files to the Subscriber for initialization. SQL Server replication also supports the way the database is backed up for subscription initialization.

In the 1th step, the default snapshot folder can also be placed on a real network disk, not on the publisher. Consider the direct write to the network disk, but need to consider this may cause network impact, if the generation of large snapshots, resulting in network congestion, or even interruption, production impact.

5. Create a new subscription at the Subscriber

On SSMs, follow the wizard step-by-step to create a new subscription (in the case of Pull subcription request subscription). In the distribution Agent security step, the setting is this:

• Run under the SQL Server Agent service account
• Connect to the distribution:using the following SQL Server login
  The SA user and password for the publisher is used here. Of course, you can also create a separate SQL Server replication account to connect.
• Connect to the Subscriber:by impersonating the process account

In the case of push push Subcription, 2nd, 3 options are reversed. The using the following SQL Server login fills in the SQL Server account of the Subscriber, giving the distributor permission to push the data to the Subscriber.

6. Set add New item do not initialize entire snapshot

The publisher often creates new tables or other database objects, such as stored procedures or functions, that you need to initialize if you want to add these new items to an existing subscription. But the default setting is to initialize all items of the entire snapshot. This is expensive for large snapshots.

The following settings initialize only newly added items.

use db1;GOEXEC sys.sp_changepublication     @publication = ‘test3‘,     @property = N‘immediate_sync‘,     @value = N‘false‘GOEXEC sys.sp_changepublication     @publication = ‘test3‘,     @property = N‘allow_anonymous‘,    @value = N‘false‘GO

You can then start the Snapshot Agent to generate a snapshot of the newly added project.
(Right-click to publish, select "View Snapshot Agent Status"--"start")

Equivalent to running the following script:

EXEC sys.sp_startpublication_snapshot@publication = ‘test3‘go

SQL Server transactional replication setup and synchronization experience