SQL Server replication: Transactional Publishing

I. BACKGROUND

In the context of replication, transactional publishing is the most widely used, and I have a scenario where there are basic and group two tables in the task database, and you need to provide some of the fields in the two tables to cache other programs, and the program needs to get the data in time. As a DBA you need to start with permissions and performance control, I use SQL Server's transactional replication technology and timestamp, which only covers the process of building transactional replication;

Second, the implementation process

(i) Environmental information

System environment: Windows Server 2008 + SQL Server 2008 R2

Publisher: 192.168.1.151, server name: User-h2b2a89pek

Distributor: Same machine as publisher

Subscriber: 192.168.1.152, server name: user-fjmo8l052u

Publish database: Task

Subscription database: Tasksiteinfo

Database account Number: Replicationuser/replicationpassword

(ii) Steps to build

A. Publisher configuration

First create the same account number and password (Replicationuser/replicationpassword) on the publication database and subscription database, and set the security object for the task database, so that the account is set up to be separated from the account area of the program connected to the database. Can do the control of the Authority, facilitate the investigation of the problem;

--change ownership of a security object

ALTER AUTHORIZATION on Database::[task] to [Replicationuser]

Create a folder in the E-disk directory: E:\ReplData, and set this folder as a shared directory, sharing users for barefootadmin;

(figure1_1: Folder permissions)

Here need to set up SQL Server Agent login account for the above folder access user barefootadmin;

(Figure1_2:sql Server Agent login account)

(Figure2: Distributor)

If you set the snapshot folder path to: E:\ReplData, even if your publisher is the Distributor itself, if the Subscriber is another machine, The subscription agent will not be able to access the snapshot file in the request (Pull) Subscription (if there is no such restriction on the push) subscription; You should set the snapshot folder path to: \ \ User-h2b2a89pek\ Repldata;

(Figure3: Snapshot folder)

(Figure4: Database)