Setting Up a Publishing Subscriber

Establishing a hierarchy of replication servers can minimize the amount of communications traffic that is passed over a slow or expensive network link. In this example, a hierarchy of replication servers is established, with a single subscription server that is physically located at the far side of a network link from the publisher. This subscriber receives all changes to the published tables and then republishes these changes to other nearby subscription servers.

Because any server can act as both publication and subscription server, setting up this configuration is simple. For this example, we will consider the publication of a table that exists in New York and needs to be distributed to four different cities in Europe: London, Oslo, Paris, and Lisbon. The server in London is chosen to subscribe to the published table originating in New York, because the London site meets these conditions:

• The network link back to New York is relatively reliable
• The New York-to-London communication costs are acceptable
• There are good network communications lines from London to all other European subscriber sites

The step-by-step process for setting up replication has been presented in Chapter 14, Setting Up Replication. The following summaries assume you know how to set up replication, and only address the changes to a standard configuration that are required to set up this hierarchy of replication servers.

The information that follows discusses considerations when:

• Setting up the first-level publisher (New York)
• Setting up the subscriber/publisher (London)
• Setting up the second-level subscribers (Oslo, Paris, Lisbon)

Setting up the First-level Publisher

1. On the New York server, authorize the London server as a server permitted to subscribe.

   Set this up in the Replication Publishing dialog box by selecting the London server in the Enable Publishing to These Servers list.

   Even though this publication will eventually be replicated to many servers in Europe, the publication server in New York defines only the London server as a server permitted to subscribe.

2. On the New York server, reduce the setting for Commit Every <x> Transactions to Subscriber.

   Set this up in the Replication Publishing dialog box by selecting the London server from the Enable Publishing to These Servers list, choosing the Distributions Options button, and entering a smaller number (for example, 1) in the Distribution Options dialog box that appears.

   Because the London server sits on the far side of a slow network link, selecting a smaller size for replicated transactions will allow published table changes to be distributed across the network using reasonably sized transactions.

3. Publish the table.

   When creating the article, you must be sure to replicate the primary keys. To do this, choose the Include DRI-PK option for the article's synchronization script. This is set by choosing the Generate button from the Manage Article dialog box. For information see Chapter 14, Setting Up Replication.

Setting up the Subscriber/Publisher

1. On the London subscription server, subscribe to the table published by the New York server.
2. While subscribing, decide the initial synchronization option.
   • If the published table is fairly large in size, consider performing a manual initial synchronization.

     This prevents the distribution process from having to send a large data file across the slow or expensive network link. When manual data synchronization is selected, the initial Synchronization task will still create a .TMP file containing an image of the published table, but it is not automatically applied. The .TMP file must be physically sent to the remote site and manually applied.

   • If you know that both the table on the publication server and the destination table on the subscription server already exist and are completely synchronized with one another, consider choosing the No Data Synchronization option.

     This allows replication of table data changes to begin without producing or applying a .TMP initial synchronization file.

3. On the London server, install a distribution database, or select a remote distributor.

   To do this, select the Replication Configuration/Install Publishing command from the Server menu, and then complete the dialog box that appears.

4. Set the publication options on the London server.
   • Authorize the Oslo, Paris, and Lisbon servers to act as subscribers.
   • Authorize the publication of the appropriate database on the London server. This is the same database that has subscribed to the publication from the New York server.

   Both of the settings are performed in the Replication Publishing dialog box.

5. Publish the table.

   This is the local table that has been replicated from the New York server.

6. On the London server, set the Commit Every <x> Transactions to Subscriber option for the Oslo, Paris, and Lisbon servers.
   • Unless the Distribution process from New York to the London server was set to commit for each individual transaction using the Distribution Option of Commit Every 1 Transaction, then groups of transactions from New York may have been applied as a single transaction at the London server. When the table changes are republished, this group of transactions will appear as a single transaction on the London server.

     To avoid the problem of exponentially increasing groups of transactions, the distribution option to second-level subscribers should be set to Commit Every 1 Transaction.

     Set this up in the Replication Publishing dialog box by selecting a server from the Enable Publishing to These Servers list, choosing the Distributions Options button, and entering a number in the Distribution Options dialog box that appears.

   • Setting the Commit Every 1 Transaction option can also be done for just the specific database receiving the republished table changes. This can only be done once the second-level subscriber has subscribed to a publication within the republishing database. To change the commit option for an individual database use the SQL Enterprise Manager Task Scheduling tool, edit the appropriate distribution task, and set the -c parameter within the command window to -c1.

Setting up the Second-level Subscribers

The second-level subscribers (Oslo, Paris, Lisbon) subscribe to the London publication using normal subscription procedures. As long as all replicated data is treated as read-only, it can remain completely transparent to the second-level subscription servers that the original publisher of the replicated table was not the server in London but a server in New York.

An exception to this occurs if a second-level subscriber needs to send a change back to the primary owner of the replicated table. In this case, the change needs to be applied not to the London server but to the server in New York.

SQL Enterprise Manager allows you to display origination information to second-level subscribers. When creating a second-level publication, the publication description field can be used to provide the origination server information, and the article description field can provide the original table name if it has changed. This information can then be accessed by subscribers in two ways:

• From the Manage Subscriptions dialog, the description for each publication and article is displayed when the subscriber selects the appropriate icon.
• The publication information can also be displayed by selecting the More Info button within the Manage Subscriptions dialog. However, the table name provided here for each article is the name of the table on the second-level publication server and is not necessarily the same as the table name on the original publisher.