SQL Server Replication: Transactional Publishing (read-write separation)

First, background

In the replication scenario, transactional publishing is the most widely used, I encountered a scenario: in the Yangjialeclub database has tables, stored procedures, views, user-defined functions, need to be provided to other programs read into the cache, the program needs to be more timely access to this data, From the point of view of permissions and performance control, I have adopted the SQL Server transactional replication technology and timestamp, the following is only the construction process of transactional replication;

Second, implementation Process

(i) environmental Information

	Operating system	Ip	Server name	Database version	Database name	Database account Information
Publishing Server	Windows 10 Enterprise Edition	192.168.2.100	Desktop-sdik64j	SQL Server R2	Yangjialeclub	Sa/bwl Reg/bwl
Distribution server	Same machine as publishing server
Subscriber server 1	Windows Server 2008r2 Enterprise	192.168.2.117	Win-db-slave1	SQL Server R2	Yangjialeclub	Sa/bwl
Subscriber server 2	Windows Server 2008r2 Enterprise	192.168.2.118	Win-db-slave2	SQL Server R2	Yangjialeclub	Sa/bwl

1. Publishing Server:

1.1 Set the stack rules in the firewall, open 1433 and 1434 ports

2. Subscribers:

2.1.SQL Native Client 10 configuration (32-bit) New alias:

2.Host server name and IP mapping (due to Publisher Subscriber name must be: Server + "\" + Instance name access)

Catalog: C:\Windows\System32\drivers\etc

3. Control Panel \ User account \ Credential Manager

Create credentials

(b) construction Steps

A. Publisher Configuration

First in the publication database and the subscription database to create the same account and password (REG/BWL), and set the security object of the Yangjialeclub database, the purpose of setting up such an account is to connect with the program to the database of the account area, you can do permissions on the control, easy to troubleshoot the problem;

--Change ownership of securable objects

ALTER AUTHORIZATION on Database::[yangjialeclub] to [Reg]

Create a folder in the F-drive directory: F:\DB\Net\ReplData, and set this folder as the shared directory, sharing the user as BIWL

(figure1_1: Folder permissions)

Here you need to set the SQL Server Agent login account for the above folder access user Biwl (publisher and subscriber need to establish BIWL same account and password settings SQL Server Agent service)

(Figure1_2:sql Server Agent login account)

(Figure2: Configure distribution-distributor)

If you set the snapshot folder path to: F:\DB\Net\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 limit for push subscriptions), unless your Publisher, distributor, and Subscriber are the same machine; you should set the snapshot folder path to: \ \ Desktop-sdik64j\repldata;

(Figure3: Snapshot folder)

(Figure4: Database)

• Snapshot Publishing: The database at the Subscriber is overwritten over a period of time, and modifications made at the Subscriber are also overwritten;
• Transactional Publishing: A method of distributing data from source to destination in near real time;
• Transactional publications with updatable subscriptions: Subscribers can update the data at the publisher;
• Merge publication: Updates to the Publisher and Subscribers are synchronized to each other, noting that IDs conflict on merge publications

(Figure5: Transactional release)

Note that the table must have a primary key in order to replicate, select your necessary fields, which will reduce the size and transfer time of the snapshot files, but also more secure in the business logic, if necessary, you can also filter records;

(Figure6_1: Table field)

(figure6_2: Stored procedure)

(Figure6_3: View)

(figure6_4: User-defined function)

(Figure7: Snapshot Agent)

(Figure8: Security Setting)

Use the Replicationuser account created above as the account number and password to connect to the publisher;

(Figure9: Use the account password you just created)

(Figure10: Creating a publication)

(Figure11: Publication name)

(Figure12: Viewing replication)

B. Subscriber Configuration

After creating the publisher (the Distributor is also created together), you are ready to create the subscriber, following the specific steps:

(Figure13: Finding Publishers)

(Figure14: Finding Publishers)

(FIGURE15: Select publish)

(Figure16: Pull subscription)

(FIGURE17: Subscription database)

(Figure18: Subscription connection)

(FIGURE19: Account password)

(FIGURE20: Agent program)

(FIGURE21: Initialize subscription)

(FIGURE22: Create subscription)

(Figure23: Subscription)

(Figure24: Subscription at the publisher)

(FIGURE25: Replication Monitor)

(Figure26: New table for subscription database)

(Figure27: Table data)

Third, Precautions

1. Under SQL Server to implement the Publisher and Subscriber communication is normal (that is, can exchange visits), open 1433 port, in the firewall set the inbound rules;

2. The publisher and the subscriber's SQL Server Agent account must be set the same, or not exchange visits;

3. If you want to copy the nonclustered index in the process of replication, you can set the publication properties-items as follows, you need to regenerate the snapshot after the modification;

(Figure28: Nonclustered index replication)

4. Replication agent: Snapshot Agent (Snapshot Agent) distributed Agent (Distribution Agent) log read agent (Log Reader Agent) Merge Agent (Merge Agent) queue read proxy (Queue Reader Agent)

5. Some scenarios for using replication include:

1) Load balancing: Reduce the load on the current server by replicating data to other database servers, for example, the most typical application is distributing data to separate OLTP and OLAP environments;

2) Partitioning: Isolation of frequently used data and historical data, and copying of historical data into other databases;

3) Authorization: To provide a portion of the data to the person who needs to use the data for its use;

4) Data merging: Each region has its own data and merges its data. For example, a large company, each region has its own sales data, headquarters need to summarize the data;

5) Failover: Copy all data for transfer in case of failure;

6. The types of snapshot replication or transactional replication generated snapshot files are:

Schema (. sch), data (. bcp), Constraints and indexes (. dri), constraints (. idx), triggers (. trg) (only for updating Subscribers), compressed snapshot files (. cab).

Four, Questions

1. Can SQL Server use replication only in full log mode?

FAQ: You can use replication in simple mode;

2. If the cross-network segment (across the room) of the publication and subscription, there is no way to achieve? What to pay attention to?

FAQ: You can build replication by modifying the host file, see: SQL Server replication across network segments (across rooms)

3. If the above situation can be set in the host, but if there is a port mapping, host can not set it?

FAQ: Creating aliases in SQL Server Configuration Manager is also a reference: SQL Server replication across network segments (across rooms)

4. What is the cost of subscribing to a subscription or a push subscription or a pull subscription that reduces the overhead of the distribution server's processing work? How to calculate the impact?

Doubt: Only when there are a lot of subscribers, it is obvious that the difference between push subscription and pull subscription is different in management;

Five, Reference Documents

snapshot replication

SQL Server Copy

TIMESTAMP (Transact-SQL)

SQL Server Copy Subscribe and publish

SQL Server Getting Started with replication ( a)---- Introduction to Replication

SQL Server Getting Started with replication ( II)---- Several modes of replication

Initializing a subscription with a snapshot

Copy

SQL Server Replication: Transactional Publishing (read-write separation)