SQL Server cross-network segment replication across the engine room

I. BACKGROUND

When you build SQL Server replication, if the network environment is within the LAN, through the host name can be achieved, but if it is across the network segment, across the computer room to build replication when you need to pay attention to, because SQL Server replication does not support through IP connection Distributor, then what method to solve the cross-network segment, What about the cross room problem?

Ii. Solutions

There are two situations that need to be distinguished when replicating SQL Server across a network segment or across a computer room: one is the 1433 port of the extranet IP that corresponds to the database port of this machine SQL Server; the other is that the extranet IP corresponds to the SQL Server machine's port is not 1433 , here are a few solutions:

A. If the extranet IP port is 1433, you can specify the IP address in the Windows host file that corresponds to the host name, and the hostname must be the same as the real host name?

B. Because your extranet IP port is not 1433, you cannot specify a port with the IP address in the host file; In this case, if the conditions allow (security and port number), you can open the firewall in the extranet IP 1433 port corresponding to this publisher 1433 port, and limit an IP access to this port, programs, such as access to the release database to use another 21433 ports, to ensure the security of 1433 ports, but also to solve the problem of port mapping, you can draw a picture interpretation;

(Figure1: Logical structure diagram)

C. Another scenario is to create a SQL Server alias in SQL Server Configuration Manager that needs to be the same as the hostname and does not require the SQL Server Browser service to be enabled;

Third, the construction process

(i) Environmental information

System environment: Windows Server 2008 + SQL Server 2008

Publisher: 192.168.1.101,1924,192.168.1.101,1433 Server name: User-h2b2a89pek

Distributor: Same machine as publisher

Subscriber: 192.168.1.102,1433, server name: QuZhoushiwei105

Publish database: Task

Subscription database: Tasksubscribe

Database account Number: Replicationuser/replicationpassword

(ii) Steps to build

The above publisher's extranet IP opened two ports, one is the default 1433, one is 1924, if the default is 1433, you can pass the host file, and if only port 1924 can only be implemented through the SQL Server alias, In order to test this, we opened the 2 ports together.

A. The following are the specific steps for creating a subscription in the form of a host file:

1 Create a publication at the publisher, you can refer to the following: SQL Server replication Transaction Publishing, only after the success of the next steps to do;

2 Set the host file for the Subscriber C:\Windows\System32\drivers\etc directory and add the Distributor (my environment is the Publisher and distributor together, So here is the name of the Publisher address) information: 192.168.1.101 User-h2b2a89pek

3 Set the Distributor server C:\WINDOWS\SYSTEM32\DRIVERS\ETC directory host file, add subscriber information: 192.168.1.102 QuZhoushiwei105

4 Create a subscription at the Subscriber, as follows:

(Figure2: Connecting to Publishers)

(Figure3: Successfully connecting to the publisher)

(Figure4: Select push subscriptions)