Step4:sql Server replication across network segments (across rooms)

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Solutions (solution)
4. Construction (process)
5. Precautions (Attention)
6. 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?

three. Solutions (solution)

In the cross-network segment, the cross-room SQL Server replication needs to distinguish between two situations: one is the external IP 1433 port corresponds to this machine SQL Server database port, the other case is that the external IP corresponds to the port of the SQL Servers machine is not 1433 ; Here are a few solutions:

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

B. Because your extranet IP port is not 1433, you cannot specify the port with the IP address in the host file, in which case, if the condition permits (security and number of ports), you can open the 1433 port of the extranet IP in the firewall corresponding to the 1433 port of this publisher. and restrict an IP can access this port, the program and other access to the publication database to use another 21433 port, to ensure the security of 1433 ports, but also to solve the port mapping problem, you can draw a map interpretation;

(Figure1: Logical structure diagram)

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

Four. Construction (process)

(i) Environmental information

System environment: Windows Server + SQL Server 2008

Publisher: 192.168.1.101,1924,192.168.1.101,1433, 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

(b) Construction steps

The above publisher's extranet IP opened two ports, one is the default of 1433, one is 1924, if it is the default of 1433, can pass the host file, and if only port 1924, only through the SQL Server alias way to implement, These 2 ports are opened together for testing.

A. Here's how to create 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 Transactional publishing, only after the successful construction of the next steps to proceed;

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

3) Set the host file for the Distributor C:\WINDOWS\SYSTEM32\DRIVERS\ETC directory to add subscriber information: 192.168.1.102 QuZhoushiwei105

4) Create a subscription at the Subscriber at the following steps:

(Figure2: Connecting to the publisher)

(Figure3: Successfully connected to the publisher)

(Figure4: Select Push subscription)

(FIGURE5: Select subscription database)

(Figure6: Push subscription account password)

(Figure7: Agent program)

(Figure8: Initialize)

(Figure9: Post-Success subscription information and jobs)

(Figure10: Status information for new subscription)

B. Here are the steps to create a subscription in the form of a SQL Server alias:

1) Create a publication at the publisher, you can refer to the following: SQL server replication Transactional publishing, only after the successful construction of the next steps to proceed;

2) Create the Distributor at the Subscriber (my environment is that the Publisher and Distributor are together, so here is the name of the publisher address);

(Figure11: Alias at Subscriber)

At the Distributor, if the settings for the alias include: SQL Native Client 10.0 configuration (32-bit) and SQL Native Client 10.0 configuration, you need to set the Subscriber alias, if not set up like Figure13,

(Figure12: Alias on distributor)

(Figure13: Alias on distributor)

3) The next steps in accordance with Figure2 to Figure8 can be, the same, the last can achieve Figure9, Figure10 effect;

Five. Precautions (Attention)

1. Using a pull subscription, the distribution job is created at the Subscriber, and the distribution job is created at the Distributor using a push subscription;

2. When a publication is created at the Publisher, the following error occurs if the SQL Server DB instance name does not match the server name:

(FIGURE14: Publish error)

When a subscription is created at the Subscriber, the following error occurs if the SQL Server DB instance name does not match the server name:

(FIGURE15: Subscription error)

3. The following SQL script can be used to modify the inconsistent problem, after the modification, remember to restart the SQL Server service to take effect;

/*sql Server database instance name inconsistent with server name resolution */if serverproperty (' SERVERNAME ') <>@ @SERVERNAME  BEGIN    DECLARE @ Server SYSNAME    set @[email protected] @SERVERNAME    EXEC sp_dropserver @[email protected]      SET @server =cast ( Serverproperty (' SERVERNAME ') as SYSNAME)    EXEC sp_addserver @[email protected], @local = ' local ' END

4. Will the snapshot files on the Distributor be deleted?

Careyson:after the snapshot is applied at all subscribers, replication cleanup deletes the associated. bcp file for the Itial snapshots automatically.

5. If you are deploying a publish subscription by modifying the host file, you must use the push subscription mode, and if you use pull subscription mode, because you cannot read the snapshot file, the following error will be reported:

(Figure16: The pull subscription cannot read the snapshot file error message)

Deploying a publish subscription in the form of an alias also has the same problem that Careyson can use the Pull subscription method on Azure by opening the relevant permissions, but the process is rather complicated and is not discussed here;

6. If the Distributor is specified only on the host file of the Subscriber (this is the publisher, because my publisher is with the Distributor) and no subscriber address is specified on the Distributor's host file, the following error will occur:

(Figure17: Publisher connection is not on subscriber)

7. If the server has both "SQL Native client 10.0 configuration (32-bit)" and "SQL Native Client 10.0 Configuration", the test finds that the alias of the "SQL Native Client 10.0 configuration (32-bit)" setting is provided to: find S The QL server Publisher uses an error message similar to the one shown in Figure19; the alias for the SQL Native Client 10.0 Configuration setting is provided to: the Distributor job is pushed to the subscriber, and the error message is similar to FIGURE17; the exact reason is unclear , to seek popular science;

(Figure18: Aliases)

8. When setting aliases at the Distributor and subscribers, the alias should be the same as the server instance name, or the following error will be reported:

(FIGURE19: Error message)

Step4:sql Server replication across network segments (across rooms)