SQL Server cross-network segment (cross-room) FTP replication

SQL Server cross-network segment (cross-room) FTP replication

2013-09-24 17:53 by listening to the wind blowing rain, 1497 reading, 4 Reviews, Favorites, compilation

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Construction (process)
4. Precautions (Attention)
5. References (References)

two. Background (contexts)

When building SQL Server replication, if the network environment is within the LAN, through the host name can be achieved, but if it is cross-network segment, across the computer room to build replication when you need to pay attention to, because SQL Server replication does not support the distribution of servers through IP connections, there is no way to solve the cross-network segment, What about the cross-room problem?

I have mentioned two solutions in SQL Server cross-network segment (cross-room) replication, if you want to use the pull subscription mode, the configuration of shared snapshot file permissions is more troublesome, the better and more secure way is to read the snapshot file in FTP form to initialize;

Three. Construction (process)

(i) Environmental information

System environment: Windows Server + SQL Server 2008

Publisher: 192.168.1.101,1924, server name: User-h2b2a89pek

Distribution server: Same machine as publisher

Subscriber: 192.168.1.102,1433, server name: QuZhoushiwei105

Publish database: Task

Subscription database: Tasksubscribe

Database account Number: Replicationuser/replicationpassword

FTP Address: ftp://192.168.1.101:9721

FTP account password: Ftpuser/ftppassword

(b) Construction steps

Before you begin the following steps you need to confirm that your FTP address is available, and make sure that the generated snapshot files can be read and downloaded using the FTP client program;

A. Publisher configuration

Here are the specific steps to set up the publishing server:

(Figure1: Select Publish database)

(Figure2: Transactional release)

(Figure3: Select object)

(Figure4: Initialize subscription)

(FIGURE5: Set account password)

(Figure6: Publication name)

(Figure7: Snapshot)

is the main interface for setting up an FTP snapshot:

(Figure8: Setting up an FTP snapshot)

(Figure9: Republish)

(Figure10: Published Snapshot file)

(FIGURE11: Intranet FTP list)

(Figure12: External network FTP list)

B. Subscriber Configuration

After creating the publisher (the Distributor is also created together), you can then create subscribers, in the case of cross-network segments, you can use the host file or alias to connect to the Distributor, for reference: SQL Server across the network segment (across the room) replication, The following are the specific steps for subscriber settings:

(Figure13: Finding Publishers)

(Figure14: Select publish)

(FIGURE15: Pull subscription)

(FIGURE16: Select subscription database)

(FIGURE17: Set account password)

(Figure18: Synchronization Schedule)

(Figure19: Initialize)

(FIGURE20: Create subscription)

(FIGURE21: Before subscription)

(Figure22: After subscription)

four. Precautions (Attention)

1. The Windows Server 2008 system needs to open 21 port in the inbound rule to build ftp;

2. FTP has active and passive points, so you need to pay attention when setting up a firewall;

five. References (References)

SQL Server database synchronization scenarios in detail (including cross-network segments)

How to deliver a snapshot via FTP

SQL Server cross-network segment (cross-room) FTP replication