• Applied functions • Request functions
Table Replication Definitions
Table replication definitions allow you to replicate data from a primary source as read-only copies.
You can create one or many replication definitions for a primary table although a particular replicate table can subscribe to only one of them. See Multiple Replication Definitions for an example using multiple replication definitions.
You also can collect replication definitions in a publication and subscribe to all of them at one time with a publication subscription. See Publications for an example using publications. For each table you want to replicate according to the basic primary copy model, you need to: • Set up routes and connections between Replication Servers.
• Create the table you want to replicate in the primary database.
• Create the table (or tables) to which you want to replicate in destination databases. • Create indexes and grant appropriate permissions on the tables.
At the Primary Site:
• Mark the primary table for replication using the sp_setreptable system procedure. • Create one (or more) replication definitions for the table at the primary Replication Server. At the Replicate Sites:
Create subscriptions for the table replication definitions at each replicate Replication Server. See the Replication Server Administration Guide Volume 1 for details on setting up the basic primary copy model.
In this figure, a client application at the primary (Tokyo) site makes changes to the publishers table in the primary database. At the replicate (Sydney) site, the
publishers table subscribes to the primary publishers table—for those rows where pub_id is equal to or greater than 1000.
Figure 10: Basic Primary Copy Model Using Table Replication Definitions
Marking the Table for Replication
This script marks the publishers table for replication.
-- Execute this script at Tokyo data server -- Marks publishers for replication
sp_setreptable publishers, 'true' go
/* end of script */
Replication Definition
This script creates a table replication definition for the publishers table at the primary Replication Server.
-- Execute this script at Tokyo Replication Server -- Creates replication definition pubs_rep
create replication definition pubs_rep with primary at TOKYO_DS.pubs2
with all tables named 'publishers' (pub_id char(4),
pub_name varchar(40), city varchar(20), state varchar(2)) primary key (pub_id)
go
/* end of script */
Subscription
This script creates a subscription for the replication definition defined at the primary Replication Server.
-- Execute this script at Sydney Replication Server -- Creates subscription pubs_sub
Create subscription pubs_sub for pubs_rep
with replicate at SYDNEY_DS.pubs2 where pub_id >= 1000
go
/* end of script */
See also
• Publications on page 59
• Multiple Replication Definitions on page 57
Applied Functions
You can use applied functions to replicate stored procedure invocations to remote sites with replicate data.
Using applied functions to replicate primary data, lets you: • Reduce network traffic over the WAN
• Increase throughput and decrease latency because applied functions execute more rapidly • Enable a more modular system design
In the following example, a client application at the primary (Tokyo) site executes a user stored procedure, upd_publishers_pubs2, which makes changes to the publishers table in the primary database. Execution of upd_publishers_pubs2 invokes function replication, which causes the corresponding stored procedure, also named upd_publishers_pubs2, to execute on the replicate data server.
At the Primary Site:
• Create the user stored procedure in the primary database.
• Mark the user stored procedure for replicated function delivery using sp_setrepproc. • Grant the appropriate procedure permissions to the appropriate user.
• At the primary Replication Server, create the function replication definition for the stored procedure with parameters and datatypes that match those of the stored procedure. You can specify only the parameters you want to replicate.
At the Replicate Site:
• Create a stored procedure in the replicate database with the same parameters (or a subset of those parameters) and datatypes as those created in the primary database. Grant appropriate permissions to the procedure to the maintenance user.
• Create a subscription to the function replication definition in the replicate Replication Server.
Figure 11: Basic Primary Copy Model Using Applied Functions
Stored Procedures
This script creates stored procedures for the publishers table at the primary and replicate sites.
-- Execute this script at Tokyo and Sydney data servers -- Creates stored procedure upd_publishers_pubs2 create procedure upd_publishers_pubs2
(@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as update publishers set pub_name = @pub_name, city = @city, state = @state
where
pub_id = @pub_id go
/* end of script */
Function Replication Definition
This script creates an applied function replication definition for the publishers table at the primary Replication Server. The replication definition uses the same parameters and datatypes as the stored procedure in the primary database.
-- Execute this script at Tokyo Replication Server -- Creates replication definition
_upd_publishers_pubs2_repdef
create applied function replication definition upd_publishers_pubs2_repdef
with primary at TOKYO_DS.pubs2
with all functions named upd_publishers_pubs2 (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) go /* end of script */ Subscriptions
You can create a subscription for a function replication definition in one of two ways: • Use the create subscription command and the no-materialization method.
Use this method if primary data is already loaded at the replicate, and updates are not in progress.
• Use the define subscription, activate subscription, and validate subscription commands and the bulk materialization method.
Using the No-Materialization Method
This script creates a subscription at the replicate Replication Server using the no- materialization method for the replication definition defined at the primary Replication Server.
-- Execute this script at Sydney Replication Server -- Creates subscription using no-materialization -- for upd_publishers_pubs2_repdef
create subscription upd_publishers_pubs2_sub for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2 without materialization
go
/* end of script */
Using Bulk Materialization
This script defines, activates, and validates a subscription at the replicate Replication Server for the replication definition defined at the primary Replication Server.
-- Execute this script at Sydney Replication Server -- Creates subscription using bulk materialization -- for upd_publishers_pubs2_repdef
define subscription upd_publishers_pubs2_sub for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2 go
activate subscription upd_publishers_pubs2_sub for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2 go
/* Load data. If updates are in progress,use activate subscription with the “with suspension” clause and resume connection after the load. */
validate subscription upd_publishers_pubs2_sub for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2 go
/* end of script */