sp_replication

Configures and manages alternate replication paths between a primary database and Replication Server.

Syntax

sp_replication_path 'dbname', {
'add' 'physical_path', 'repserver_name', 'rs_username', 'rs_password' |
'add', 'logical', 'logical_path', 'physical_path' | 
'drop', 'physical_path' |
'drop', 'logical', 'logical_path', [,'physical_path'] |
'bind', '{table | sproc | filter}', '[table_owner].object_name', 'path_name' |
'unbind', '{table | sproc | filter | path}', 'object_name', {'path_name' | all} |
'config', 'path_name', 'config_parameter', 'config_value' |
'list'[, 'all | table | sproc | filter' [, 'object_name']]

Parameters

dbname – the name of the database for which you want to configure RepAgent
add – adds an alternate physical RepAgent path from dbname to a Replication Server:
- physical_path – name of the alternate RepAgent path
- repserver – name of the Replication you are connecting from dbname
- rs_username – user name with the appropriate priveleges to connect to repserver. This is usually the maintenance user.
- rs_password – password for rs_username
add, logical – adds a logical RepAgent path that you can use to distribute data and objects that is currently bound to a physical path to multiple Replication Servers
- logical_path – name of the logical path.
drop – removes a Replication Server as a destination from a physical replication path that is not the default primary replication path
drop, logical – removes elements from a logical replication path such as physical paths
bind – associates an object with a physical or logical primary replication path. The bound object always follows the same path during replication.
- table | sproc | filter – specify the type of object that can be either a table , stored procedure (sproc), or filter
- [table_owner].object_name – specify the table, stored procedure, or filter name, and optionally specify the table owner name
  Note: If you do not specify a table owner if the object is a table, the binding applies only to tables owned by dbo, the database owner.
- path_name – a physical or logical replication path name.
unbind – Removes the association between a bound object and a physical or logical replication path.
- table | sproc | filter | path – specify the type of object that can be either a table , stored procedure (sproc), filter, or path.
- [table_owner.]object_name – specify the table, stored procedure, or filter name, and optionally specifiy the table owner name
  Note: If you do not specify a table owner if the object is a table, the unbinding applies only to tables owned by dbo.
- path_name | all – specify a physical or logical path name, or all paths. If you specify path as the object_type, specify the path name in object_name, and specify the all option, Replication Agent unbinds all objects from the path name you specified.
config – Sets parameter values in alternate replication paths.
- config_parameter – rs username or rs password
- config_value – rs_username for rs username, and rs_password for rs password.
list – Displays information on replication objects in the active and inactive binding states. Active bindings are the bindings that RepAgent uses to replicate data under the current distirbution model.
- no option – do not specify any option to display only the active bindings
- all | table | sproc | filter | path – use all to list all active and inactive bindings to replication paths or specify the type of object that can be either a table , stored procedure (sproc), filter, or path.
- object_name – display the binding relationships for a particular object. You must specify the object type such as table , stored procedure, filter, or path, if you want to specify the name of an object.

Examples

Example 1 – Create alternate physical replication paths.
- Create the pdb_1 alternate physical replication path between the pdb database in the PDS data server, and RS2 Replication Server, using the RS2 user ID with the RS2_password. At PDS, enter:
```
sp_replication_path 'pdb', 'add', 'pdb_1', 'RS2', 'RS2_user', 'RS2_password'
```
- Create the pdb_2 alternate physical replication path between the pdb database in the PDS data server, and RS1 Replication Server, using the RS1 user ID with the RS1_password. At PDS, enter:
```
sp_replication_path 'pdb', 'add', 'pdb_2', 'RS1', 'RS1_user', 'RS1_password'
```
There are now three physical replication paths from pdb: pdb_1, pdb_2, and the existing default path replication path that you must create to either RS1 or RS2 before you create the alternate physical replication paths.
Example 2 – Create the logical_1 logical path supported by the pdb_1 physical path. At PDS, enter:
```
sp_replication_path 'pdb', 'add', 'logical', 'logical_1', 'pdb_1'
```
Example 3 – Add the pdb_2 physical path to support the existing logical_1 logical path.
```
sp_replication_path 'pdb, 'add', 'logical', 'logical_1', 'pdb_2'
```
Example 4 – Drop the RS1 Replication Server as a destination for a physical path:
```
sp_replication_path 'pdb', 'drop', 'RS1' 
```

Example 5 – Drop physical paths from a logical path.

Remove pdb_1 from logical_1:

sp_replication_path 'pdb', 'drop', 'logical', 'logical_1', 'pdb_1'

Remove pdb_2 from logical_1:

sp_replication_path 'pdb', 'drop', 'logical', 'logical_1', 'pdb_2'

Example 6 – Remove the logical_1 logical path:

sp_replication_path 'pdb', 'drop', 'logical', 'logical_1'

Example 7 – Bind objects to a physical or logical replication path.
To bind the:
- t1 table to the pdb_2 replication path:
```
sp_replication_path 'pdb', 'bind', 'table', 't1', 'pdb_2'
```
- t2 table belonging to owner1 to the pdb_2 replication path:
```
sp_replication_path 'pdb', 'bind', 'table', 'owner1.t2', 'pdb_2'
```
- sproc1 stored procedure to the pdb_2 replication path:
```
sp_replication_path 'pdb', 'bind', 'sproc', 'sproc1', 'pdb_2'
```
- dt1 dimension table object to the everywhere logical path:
```
sp_replication_path 'pdb', 'bind', 'table', 'dt1', 'everywhere'
```
- F1 filter to the pdb_1 replication path:
```
sp_replication_path 'pdb', 'bind', 'filter', 'F1', 'pdb_1'
```
  You must create the F1 filter with the create replication filter command before you can bind the filter to a path.
Optionally, use the asterisk "*" or percent "%" wildcard characters, or a combination of both in object_name to specify a range of names or matching characters that you want to bind to a path. For example, to bind tables with names that match various wildcard character combinations to the pdb_2 replication path:
- ```
sp_replication_path 'pdb', 'bind', 'table', 'a*', 'pdb_2'
```
- ```
sp_replication_path 'pdb', 'bind', 'table', 'au%rs', 'pdb_2'
```
- ```
sp_replication_path 'pdb', 'bind', 'table', 'a*th%s', 'pdb_2'
```
- ```
sp_replication_path 'pdb', 'bind', 'table', 'authors%', 'pdb_2'
```
Example 8 – Unbind objects from replication paths.
To remove:
- The binding on the t1 table to the pdb_2 replication path:
```
sp_replication_path 'pdb', 'unbind', 'table', 't1', 'pdb_2'
```
- All bindings on the t1 table:
```
sp_replication_path 'pdb', 'unbind', 'table', 't1', 'all'
```
- The binding of all objects to the pdb_2 replication path:
```
sp_replication_path 'pdb', 'unbind', 'path', 'pdb_2', 'all'
```
Example 9 – Change the password and user ID for alternate replication paths.
To change the:
- User name that the pdb_1 alternate replication path uses to connect to RS1 to 'RS1_user':
```
sp_replication_path 'pdb', 'config', 'pdb_1', 'rs_username', 'RS1_user'
```
- Password that pdb_1 uses to connect to RS1 to 'january':
```
sp_replication_path 'pdb', 'config', 'pdb_1', 'rs password', 'january'
```

Example 10 – Display the path relationships of all objects that are in the active binding state when the distribution model is object binding:

sp_replication_path 'pdb','list'
go

You see the output showing the object types—T for table and and P for stored procedure, and the physical or logical paths the objects are bound to:

Binding              Type     Path
-------------------- -------- --------------------
dbo.dt1              T        everywhere
dbo.sproc1           P        pdb_1
dbo.sproc1           P        pdb_2
dbo.t1               T        pdb_2
dbo.t2               T        pdb_1

(5 rows affected)

Logical Path                  Physical Path
----------------------------  ---------------------
everywhere                    pdb_1
everywhere                    pdb_2

(2 rows affected)
Physical Path                  Destination
----------------------------   --------------------
pdb_1                          RS2
pdb_2                          RS1

(2 rows affected)
(return status = 0)

Example 11 – Display the path relationships of all objects that are in the active binding state when the distribution model is filter:

sp_replication_path 'pdb','list'
go

You see the output showing the object type— RF for replication filter and the paths the filters are bound to:

Binding              Type     Path
-------------------- -------- --------------------
dbo.F1               RF       pdb_1
dbo.F2               RF       pdb_2

(2 rows affected)


Physical Path                 Destination
----------------------------- --------------------
pdb_1                         RS2
pdb_2                         RS1
default                       RS_default
(3 rows affected)
(return status = 0)

Example 12 – Display the path relationships to include all objects in the active and inactive binding states when the distribution model is filter:

sp_replication_path 'pdb','list', 'all'
go

You see the output showing the object type—filters (RF), tables (T), and stored procedures (P), the paths the objects are bound to and whether the object is in the active or inactive binding state:

Binding              Type     Path
-------------------- -------- --------------------
dbo.F1               RF       pdb_1
dbo.F2               RF       pdb_2
(2 rows affected)


Inactive Binding     Type     Path
-------------------- -------- --------------------
dbo.dt1              T        everywhere
dbo.sproc1           P        pdb_1
dbo.sproc1           P        pdb_2
dbo.t1               T        pdb_2
dbo.t2               T        pdb_1

(5 rows affected)


Physical Path                 Destination
----------------------------- --------------------
pdb_3                         RS2
pdb_4                         RS1

(2 rows affected)
(return status = 0)

Example 13 – Display information on all bound tables when the distribution model is object binding::

sp_replication_path 'pdb','list','table'
go

You see:

Binding              Type     Path
-------------------- -------- --------------------
dbo.dt1              T        everywhere
dbo.t1               T        pdb_2
dbo.t2               T        pdb_1

(3 rows affected)
(return status = 0)

Example 14 – Display information on all bound stored procedures when the distribution model is object binding::

sp_replication_path 'pdb','list','sproc'
go

You see:

Binding              Type     Path
-------------------- -------- --------------------
dbo.sproc1           P        pdb_1
dbo.sproc1           P        pdb_2
dbo.sproc2           P        pdb_1

(3 rows affected)
(return status = 0)

Example 15 – Display information on all bound filters while the distribution model is object binding:

sp_replication_path 'pdb','list','filter'
go

You see that filters are in the inactive binding state:

Inactive Binding     Type     Path
-------------------- -------- --------------------
dbo.F1               RF       pdb_1
dbo.F2               RF       pdb_2

(2 rows affected)

(return status = 0)

Example 16 – Display information on only the sproc1 stored procedure while the distribution model is object binding:

sp_replication_path 'pdb','list','sproc','sproc1'
go

You see:

Binding              Type     Path
-------------------- -------- --------------------
dbo.sproc1           P        pdb_2
dbo.sproc1           P        pdb_1

(2 rows affected)
(return status = 0)

Example 17 – Display information on only the F1 filter while the distribution model is object binding:

sp_replication_path 'pdb','list','filter','F1'
go

You see that filters are in the inactive binding state:

Inactive Binding     Type     Path
-------------------- -------- --------------------
dbo.F1               RF       pdb_1


(1 row affected)

(return status = 0)

Example 18 – To display information on all replication paths when the distribution model is filter:

sp_replication_path 'pdb','list','path'
go

You see:

Path             Type  Binding      Active
---------------  ----  ----------   ------------
everywhere       T     dbo.dt1      No
pdb_1            P     dbo.sproc1   No
pdb_1            RF    dbo.F1       Yes
pdb_1            T     dbo.t2       No
pdb_2            P     dbo.sproc1   No
pdb_2            RF    dbo.F2       Yes
pdb_2            T     dbo.t1       No

(7 rows affected)


Logical Path                  Physical Path
----------------------------- ------------------------
everywhere                    pdb_1
everywhere                    pdb_2

(2 rows affected)


Physical Path                 Destination
----------------------------- ----------------------
pdb_1                         RS2
pdb_2                         RS1

(2 rows affected)
(return status = 0)

Example 19 – To display information on all replication paths when the distribution model is connection:

sp_replication_path 'pdb','list','path'
go

You see:

Path             Type  Binding      Active
---------------  ----  ----------   ------------
everywhere       T     dbo.dt1      No
pdb_1            P     dbo.sproc1   No
pdb_1            RF    dbo.F1       No
pdb_1            T     dbo.t2       No
pdb_2            P     dbo.sproc1   No
pdb_2            RF    dbo.F2       No
pdb_2            T     dbo.t1       No

(7 rows affected)


Logical Path                  Physical Path
----------------------------- ------------------------
everywhere                    pdb_1
everywhere                    pdb_2

(2 rows affected)


Physical Path                 Destination
----------------------------- ----------------------
pdb_1                         RS2
pdb_2                         RS1
default                       RS_Default
(2 rows affected)
(return status = 0)

Example 20 – To display information only on the pdb_1 physical path when the distribution model is filter:

sp_replication_path 'pdb','list','path','pdb_1'
go

You see:

Path             Type  Binding      Active
---------------  ----  ----------   ------------
pdb_1            P     dbo.sproc1   No
pdb_1            RF    dbo.F1       Yes
pdb_1            T     dbo.t2       No

(2 rows affected)
'
Physical Path                  Destination
------------------------------ -------------------
pdb_1                          RS2

(1 row affected)
(return status = 0)

Example 21 – To display information only on the "logical_1" logical replication path when the distribution model is object binding:

sp_replication_path 'pdb','list','path','logical_1'
go

You see:

Path                 Type     Binding
-------------------- -------- -------------------------
logical_1           T        dbo.dt1

(1 rows affected)
Logical Path                  Physical Path
----------------------------- --------------------------
logical_1                    pdb_1
logical_1                    pdb_2

(2 rows affected)
Physical Path                 Destination
----------------------------- -------------------------
pdb_1                         RS2
pdb_2                         RS1

(2 rows affected)
(return status = 0)

Note: You also see the physical paths underlying the logical path.

Usage

You must create an alternate primary connection between the primary database and Replication Server and associate the connection to the alternate RepAgent replication path from the primary database to the Replication Server before you can bind objects to the path. See Replication Server > Performance Tuning > Multi-Path Replication.
You can bind tables and stored procedures to physical or logical paths that you create for multipath replication.
Any object that you bind to a replication path always follows the same path during replication.
You can bind a table, stored procedure, or filter to several paths. During replication, the table, stored procedure, or filter replicates through all the paths you specified.
You can set the distribution by filter model and then bind a table or stored procedre to a replication path. Similarly, you can set the distribution by object binding model and then bind a filter to a path. However, the replication of the bound object or filter does not take effect until you enable the corresponding distribution model. For example, if you attempt to bind a table to a replication path while the distribution model is filter:
```
> sp_replication_path primdb, bind, 'table', 'T2', 'PP1'
go
```
You see:
```
Warning: Under the current 'filter' distribution model this binding will be ignored.
The table 'T2' is bound to path 'PP1'.
```
You cannot add an alternate path named "n/a".

Permissions

sp_replication_path requires “sa” or “dbo” permission or replication_role.