Additional Facts About Configuring Transactional Replication

Many DBAs don’t go beyond the default settings when they configure transactional replication. They don’t know how rich a tool the Microsoft SQL Server Development team has provided. Here are some of the options you can use when setting up replication. Microsoft Product Support Services fully supports all these options, and you can find detailed explanations of all of them in SQL Server 2000 Books Online (BOL).

• You can use DTS to transform data during replication. (You’ll notice performance overhead when you use this type of transformation.)
• You can publish not only tables, but also views and stored procedures.
• In configuring a table article, you can choose a different destination table on the subscriber; you can also add a description to each article definition.
• You can specify whether to create the stored procedures that transactional replication uses to replicate INSERT, UPDATE, and DELETE commands during the initial synchronization.
• You can choose whether SQL Server migrates indexes, triggers, referential integrity, table collation, and properties during the initial snapshot. (For performance reasons, I generally don’t recommend migrating these objects.)
• Article Properties lets you modify the names of individual stored procedures used for replication. I always recommend leaving the default names of stored procedures and modifying only the contents of the procedure (which is what I do when configuring forced replication in the main article).
• If you have SQL Server 2000 Service Pack 1 (SP1) or later, you can use the sp_scriptpublicationcustomprocs system stored procedure, which is particularly useful for setting up subscriptions for which you apply the snapshot manually. For more information about this stored procedure, see the updated edition of BOL. You can use the output of sp_scriptpublicationcustomprocs as a starting point for modifications that you need for forced replication, such as adding the repl_time column.
• To get scripts of individual stored procedures for a particular article, you can use the following system stored procedures:

       exec sp_scriptinsproc @articleid            - for INSERT
       exec sp_scriptdelproc @articleid            - for DELETE
       exec sp_scriptdynamicupdproc @articleid     - for UPDATE using MCALL method
       exec sp_scriptxupdproc @articleid           - for UPDATE using XCALL method

All these procedures take the @articleid parameter. You can find the article ID for each table by querying the sysarticle table in the published database on Publisher server.

• You can replicate selected columns of a table by configuring vertical partitioning.
• You can replicate selected rows of a table by configuring horizontal partitioning.
• Replication Agents can run constantly or on a specific schedule.