735
Chapter 28: Replicating Data
28
- When you get to the option to select which server you want to use as your distribu-
tor, select the Use the Following Server as the Distributor option. - Click the Add button, and enter the connection information to connect to the
remote distributor. You are prompted for the password you confi gured to access the
remote distributor. - Click Next, Next again, and Finish.
Your remote distributor is now ready to use.
Creating a Snapshot/Transactional Publication
After a distributor is set up for your server, you can create your publications. A publication
is defi ned as a collection of articles, where an article is an item to be published. An article
in SQL Server can be a table, a view, an indexed view, a user-defi ned function, or even a
stored procedure or its execution. If you choose to replicate the execution of a stored pro-
cedure, the stored procedure call is executed on the subscriber.For example, if you fi re a stored procedure that updates 10,000 rows on a table, this table is
replicated, and the execution of the stored procedure is executed; only the stored procedure
call will be executed. If the replication of the stored procedure execution were not replicated,
10,000 update statements would need to be replicated by the publisher through the distribu-
tor to the subscriber. As you can imagine, there are considerable benefi ts to doing this.Typically tables are published, but views can also be published. You just need to ensure
that the base tables referenced by the views are also published.To create a publication, execute the following steps:- Connect to your publisher using SQL Server Management Studio, and expand the
Replication folder; then right-click the local publication folder, and select New
Publication. - After clicking through the initial splash screen, select the database you want to
replicate from the Publication Databases section. - Click Next. In the Replication Types dialog that appears, select the replication type
you want to use. You then get an Articles dialog, from which you can select the
type of objects you want to replicate. - Expand each object type tree, and select the articles you want to replicate. For
example, if you want to replicate tables, expand the table tree, and select the indi-
vidual tables you want to replicate. You can elect to replicate all tables by selecting
the check box next to the table tree. You also have the option to replicate only a
subset of the columns in tables you replicate.
If you see a table with what appears to be a red circle with a slash through it next to the table, this table does not
have a primary key, and you cannot replicate it in a transactional publication. Snapshot and merge replication
enables you to replicate tables without primary keys.c28.indd 735c28.indd 735 7/31/2012 9:51:12 AM7/31/2012 9:51:12 AM
http://www.it-ebooks.info