Setting Up a Replication Topology

This example will show you how to set up servers in basic replication roles and how to establish replication relationships between servers. In this example:

• The server TERRIER will be configured as a publication server. It will also act as its own distribution server.
• The servers AIREDALE, COLLIE, and MUTT will be configured as subscription servers to TERRIER.
• The pubs database on TERRIER will be enabled for publishing.
• On each subscription server, one destination database will be created and enabled for subscribing.

The following example assumes that the servers AIREDALE, COLLIE, MUTT, and TERRIER have had SQL Server installed and that the servers are registered with the copy of SQL Enterprise Manager that is used to set up replication.

    To set up the replication topology example

1. On TERRIER, verify that at least 16 MB of memory is assigned to SQL Server.

   Start SQL Enterprise Manager; from the Server Manager window, select TERRIER; from the Server menu, choose Configurations; and from the Server Configuration/Options dialog box that appears, select the Configuration tab. Verify that the memory setting is 8192 or greater. If it is not, change it, and then restart the server.

   This computer must have at least 32 MB of memory installed.

2. Install a distribution database on TERRIER.
   • From the Server menu, choose Replication Configuration, and from the drop-down menu that appears, choose Install Publishing. The Install Replication Publishing dialog box appears. By default, the Local option is selected, and the Database Name box has an entry of distribution.

     

   • Add a device for the distribution database by choosing <new> from the Data Device box and completing the New Device dialog box that appears. Name the device distdata and give it a size of 30 MB.
   • Add a device for the distribution database transaction log by choosing <new> from the Log Device box and completing the New Device dialog box that appears. Name the device distlog and give it a size of 15 MB.
   • Choose OK. A dialog box asks whether you will configure publication options for this server at this time.

     

   • Choose Yes. The Replication-Publishing dialog box appears.
3. Set the publishing options for TERRIER.

   

   • Under Enable Publishing to These Servers, select the Enable checkboxes for AIREDALE, COLLIE, and MUTT.
   • Under Publishing Databases, select the checkbox for pubs.

     This does not publish the database but does authorize the database to publish.

   • Choose the OK button.
4. Create a database on each subscription server, to be used as the destination databases.

   On AIREDALE, create a database named inaire; on COLLIE, create incollie; and on MUTT, create inmutt. For information on using SQL Enterprise Manager to create a database, see Chapter 6, Managing Databases.

5. Set the subscription options for each subscriber. For AIREDALE, COLLIE, and MUTT, perform these steps:
   • From the Server Manager window, select the subscription server (for example, AIREDALE); from the Server menu, choose Replication Configuration; and from the drop-down menu that appears, choose Subscribing. The Replication-Subscribing dialog box appears.

     

   • Under Enable Subscribing from These Servers, select the Enable checkbox for TERRIER.
   • Under Subscribing Databases, select the Enable checkbox for the database that will be authorized to be a destination database (for example, inaire).

     This does not subscribe the database to any publications, but it does authorize the database to subscribe.

   • Choose OK.

   You have finished setting up your example replication topology.

6. To view a graphic depiction of the replication topology that you have set up, from the Server Manager window, choose TERRIER, and then from the toolbar, choose the Replication Topology button.

   

   The Replication Topology dialog box appears.

   

The replication topology you have created will be the basis for most of the subsequent examples in this chapter.