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', "object_type", "[table_owner].object_name", "path_name" |
'unbind', "object_type", "object_name", {"path_name" | all} |
'config', "path_name",  "config_parameter", "config_value" |
'list', ['object_type'], ['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 logical RepAgent paths that you can use to distribute data and objects bound to a physical path to multiple Replication Servers.
- logical_path – name of the logical path.
drop – Remove a Replication Server as a destination from a physical replication path that is not the default primary replication path.
drop, logical – Remove elements from a logical replication path such as physical paths.
bind – Associate an object with a physical or logical primary replication path. The bound object always follows the same path during replication.
- object_type – specifies the type of object that can be either table or sproc (stored procedure).
- [table_owner].object_name – the table or stored procedure 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 path name.
unbind – Removes the association between a bound object and a physical or logical replication path.
- object_type – specify the type of object: path, table , or sproc (stored procedure).
- [table_owner.]object_name – name of the table, stored procedure, or path that you want to unbind.
  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 | all – specifies a physical or logical path name, or all paths. If you specify path as the object_type, provide the path name as object_nameand specify the all option, Replication Agent unbinds all objects from the path name you specified.
config – Set parameter values in alternate replication paths.
- config_parameter – is rs username or rs password
- config_value – rs_username for rs username, and rs_password for rs password.
list – Displays information on bindings and replication objects.
- object_type – specify the type of object: path, table , or sproc (stored procedure).
- object_name – display the binding relationships for a particular object. You must specify object_type when 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"
```
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 bound objects:

sp_replication_path 'pdb','list'
go

You see:

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 information on all bound tables:

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 12 – To display information on all stored procedures:

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

You see:

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

(3 rows affected)
(return status = 0)

Example 13 – To display information on only the sproc1 stored procedure:

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 14 – To display information on all replication paths:

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

You see:

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

(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 15 – To display information only on the pdb_1 physical path:

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

You see:

Path                 Type     Binding
-------------------- -------- --------------------
pdb_1                P        dbo.sproc1
pdb_1                T        dbo.t2

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

(1 rows affected)
(return status = 0)

Example 16 – To display information only on the "logical_1" logical replication path:

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 bind objects to the path. See Replication Server > Performance Tuning > Multi-Path Replication.
You can bind tables and stored procedures to an alternate physical or logical path that you can create for multi-path replication.
Any object that you bind to a path always follows the same path during replication.

Permissions

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