Replicating a Table Locally

A table can be replicated locally, to another database on the same server. In this example, on the server TERRIER, the pubs..titles table will be replicated to the localdest database.

The following example assumes that the replication topology has been configured following the example in Setting Up A Replication Topology, earlier in this chapter.

    To replicate a table to a local database

1. Create a database named localdest on TERRIER.

   For this example, this will be used as the destination database. For information on using SQL Enterprise Manager to create a database, see Chapter 6, Managing Databases.

2. Authorize TERRIER to publish to itself.
   • From Server Manager, select TERRIER; from the Server menu, choose Replication Configuration; and then from the drop-down menu that appears, choose Publishing. The Replication-Publishing dialog box appears.
   • Under Enable Publishing to These Servers, select the check box for TERRIER.

     This authorizes the server to replicate to itself.

   • Choose OK.
3. Authorize localdest to be a subscription database.
   • From the Server menu, choose Replication Configuration, and then from the drop-down menu that appears, choose Subscribing. The Replication-Subscribing dialog box appears.
   • Under Enable Subscribing from These Servers, the Enable check box for TERRIER should already be selected. If it is not, select it.
   • Under Subscribing databases, choose the Enable check box for localdest.

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

   • Choose OK.
4. Publish the pubs..authors table.

   

   • From the toolbar, choose the Manage Publications button. The Manage Publications dialog box appears.
   • Select the pubs database, and then choose the New button. The Edit Publications dialog box appears.
   • In the Publication Title box, type:

     allauthors
     

   • In the Description box, type:

     Publishing a table from the pubs database.
     

   • From the Database Tables, list select the authors table, and then choose the adjacent Add button. An article named authors_Table is added to the Articles in Publication column.
   • From the upper-right corner of the dialog box, choose Add. The Manage Publications dialog box returns.
   • Choose Close.
5. Subscribe the localdest database to the allauthors publication.

   

   • From the toolbar, choose the Manage Publications button. The Manage Subscriptions dialog box appears.
   • Open the published database tree for TERRIER (choose the "" box next to the server name), and then from the displayed list of published databases, open the publications list for the pubs database (choose the "" box next to the database).
   • Select the allauthors publication, and then choose Subscribe. The Subscription Options dialog box appears.
   • From the list in the Destination Database box, select the localdest database.
   • Choose OK. The Manage Subscription dialog box returns.
   • Choose Close.

This replication example is now set up. If the default synchronization setting for the publication allauthors was not changed, synchronization will occur within five minutes. Synchronization will copy the schema and data from the published tables to the destination database. After that, replication of changes to table data will occur as changes are made.