Configuring SQL Server

To configure Internet replication, key parameters must be set on both the Publisher and Subscriber. SQL Server must be configured to interoperate with the FTP and WinSock Proxy services on the proxy server. There are four key steps to configuring SQL Server replication:

  1. Configure SQL Server to work with Microsoft Proxy Server.
  2. Configure SQL Server to work with the FTP service on the proxy server.
  3. Configure a SQL Server Subscriber for the Internet.
  4. Verify SQL Server is working with Microsoft Proxy Server.

Configuring SQL Server to Work
with Proxy Server

After Microsoft Proxy Server has been installed on the proxy server, you must configure the server running SQL Server to be a proxy client. This requires binding SQL Server to a WinSock port to allow communication over the Internet. 

Binding a WinSock Port for Data Communications

Configuring SQL Server to work with Microsoft Proxy Server requires changes to the file \Mssql7\Binn\Wspcfg.ini on the server running SQL Server. If the file does not exist, create it by using Notepad. The file must contain these entries: 

[sqlservr]

ServerBindTcpPorts=1433

Persistent=1

KillOldSession=1

  

SQL Server listens on service port 1433. When a user or anonymous subscriber is authenticated by SQL Server, a session is established between the user or anonymous subscriber and SQL Server using service port 1433.

Configuring SQL Server as a Proxy Client

When the server running Microsoft Proxy Server is set up, it creates a share called mspclnt that points to the directory C:\Msp\Clients. You must connect to this share and run the proxy client configuration utility (mpclnt) to establish SQL Server as a proxy client. After setup is complete, SQL Server is an internal client to the proxy server.

To run the Proxy Client configuration utility

  1. From the server running SQL Server, click Start, and then click Run.
  2. Under Open type: \\servername\mspclnt, and run the Proxy Server client setup program.
  3. When setup is complete, restart the SQL Server server for the changes to take effect.

Configuring SQL Server to Work
with the FTP Service

Before you can publish articles over the Internet, the Publisher and Distributor must be enabled to listen on either the TCP/IP or the Multiprotocol network protocol. Microsoft SQL Server uses TCP/IP Sockets or Multiprotocol Net-Libraries over TCP/IP to establish the initial ODBC connection between the Distributor on one side of the Internet and the Subscriber on the other. TCP/IP Sockets Net-Library is enabled during the default SQL Server setup, but may not be enabled if you did a custom installation.

To enable access control for SQL Server

  1. On the Start menu, click Programs/MS SQL Server 7.0, and then click Client Network Utility.
  2. In the SQL Server Client Network Utility dialog box, on the General tab, click TCP/IP in the list.
  3. Click Add.
  4. At Server alias, enter the protocol name defined for the WinSock Proxy Service above.

    Note There is no requirement to use the same name for the Server Alias and the Protocol Name created in WinSock Proxy.

  5. At Network libraries, ensure that the TCP/IP option is selected.
  6. At Connection parameters, ensure that port 1433 appears.

SQL Server must be informed of the FTP home directory location on the proxy server. The snapshot folder (by default \Mssql7\Repldata\Ftp) is redirected to the FTP home directory on the proxy server, enabling data to be transferred to the Subscriber. A new snapshot folder is set up during the FTP service configuration.

To set the FTP home directory

  1. On the Start menu, point to Programs/Microsoft SQL Server 7.0, and then click Enterprise Manager.
  2. Expand SQL Server Group, and then click the Publication Server.
  3. On the Tools menu, choose Wizards.
  4. In the Select Wizard dialog box, expand Replication.
  5. Click Configure Publishing and Distribution Wizard.
  6. Click the Publishers tab.
  7. Double click the Publishing server that will be placing files into the snapshot folder.
  8. Enter the UNC path name \\ProxyServerName\Repldata\ftp, and then click By impersonating the SQL Server Agent Account on PublishingServername (Trusted connection).

Configuring a SQL Server Subscriber
for the Internet

To ensure the Subscriber can access information on the Publisher by using Microsoft Proxy Server, a pull subscription must be configured with information that helps it resolve the address of the Publisher.

Note For a Subscriber to subscribe to a Publication over the Internet, the Publication on the Publisher must have the Allow Snapshots to be downloaded using FTP option enabled.

To configure a pull subscription

  1. In SQL Server Enterprise Manager, click the subscribing server.
  2. On the Tools menu, point to Replication, and then click Pull Subscriptions to SubscriptionServerName.
  3. Click Pull New Subscription.

    The Pull Subscription Wizard starts.

  4. When the Choose Publication screen appears, click the publishing server. If the publishing server does not appear in the list, click the Register Server button and register the Publisher.
  5. At Specify Synchronization Agent Login, enter an account with appropriate security settings.
  6. At Choose Destination Database, select an existing database in the list or click New Database to configure a new Subscription database.
  7. At Initialize Subscription, select Yes, initialize schema and data at the Subscriber.
  8. If the Snapshot Delivery option appears, select Yes, use FTP to copy the Snapshot files.
  9. At Set Distribution Agent Schedule, select an appropriate scheduling scheme.
  10. At Allow Anonymous Subscriptions, select Yes, make the Subscriptions anonymous.
  11. Continue through the remaining sections of the wizard, and then click Finish.
  12. When the Pull Subscriptions to SubscriptionServerName dialog box appears, click Properties.
  13. When the Pull Subscription Properties - PublisherName:databasename:replicationtype dialog box appears, click Snapshot Delivery.
  14. Ensure the Use File Transfer Protocol (FTP) check box is selected.
  15. At FTP parameters for Server address of the Distributor, enter the IP address of the NIC card that connects to the Internet on the Proxy Server used to interface with the Publisher.
  16. At Port enter the FTP port number of the Publisher’s Proxy Server connection (usually port 21).
  17. At Login, enter an account that has been configured with appropriate security clearance.
  18. At Password enter the password configured for this account.
  19. Click OK, and then click Close.

Verifying SQL Server is Working
with Proxy Server

To verify that a connection to Proxy Server has been established through port 1433, check the WinSock Proxy service on the proxy server. You should see a session in Proxy Server for the user account that SQL Server is running under.

The user account may not appear immediately. Allow SQL Server a few moments and click the Refresh button periodically. If the account for SQL Server Agent does not appear in the list, stop and restart SQL Server. SQL Server is a client to the proxy server and the service account in which SQL Server was configured to run should appear in the list.

Note You must use a fully qualified name; computer accounts will not work.

To verify a connection

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service and expand the server running Proxy Server.
  3. Click WinSock Proxy Service, and then select Properties.
  4. On the Services tab, click Current Sessions.
  5. Click WinSock Proxy service.

Alternatively, you can verify your IP connection and port information on any of the computers by using the netstat command. The following is output from the SQL Server server using netstat –a command. This lists both client- and server-side connections as well as their status and port numbers. Use netstat without any options to client sessions only. If you want to display only ports 1433 and 21, use netstat –an to display TCP connections only.

You may notice that several ports other than 1433 are listed. Keep in mind that 1433 is configured as the incoming port. The outgoing port is dynamic and Proxy Server assigns it when a connection is established. The dynamic port ranges between 1025 and 5000.

Active Connections

  

  Proto  Local Address          Foreign Address        State

  TCP    SQLReplServer:1026            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1031            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1033            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:ftp            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1058           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1059           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:135            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:135            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1433           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1025           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1025           localhost:1026         ESTABLISHED

  TCP    SQLReplServer:1026           localhost:1025         ESTABLISHED

  TCP    SQLReplServer:1029           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1030           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1032           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1056           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:1057           0.0.0.0:0              LISTENING

  TCP    SQLReplServer:137            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:138            0.0.0.0:0              LISTENING

  TCP    SQLReplServer:nbsession      0.0.0.0:0              LISTENING

  UDP    SQLReplServer:1059           *:*                    

  UDP    SQLReplServer:1088           *:*                    

  UDP    SQLReplServer:135            *:*                    

  UDP    SQLReplServer:nbname         *:*                    

  UDP    SQLReplServer:nbdatagram     *:*