Set up a multi-path replication replication system comprising of two primary and replicate paths for end-to-end replication.
sp_config_rep_agent primary_database_name, ‘multithread rep agent’, ‘true’
sp_config_rep_agent primary_database_name, 'max number of replication paths', '2'
sp_replication_path "primary_database_name", 'add', "alternate_path_name", "repserver_name", "repserver_user", "repserver_password"
create alternate connection to primary_dataserver.primary_database named primary_dataserver.alternate_path_name set error class to rs_sqlserver_error_class set function string class to rs_sqlserver_function_class set username to primary_db_maintenance_user set password to primary_db_maintenance_password with primary only
create alternate connection to replicate_dataserver.replicate_database named replicate_dataserver.alternate_path_name
sp_replication_path pdb, 'bind', "table", "[table_owner].table_name", "alternate_path_name"The other set of objects uses the default replication path. You can only bind objects to alternate replication paths. All objects that you do not bind to an alternate replication path, use the default path instead.
create replication definition authors_rep with primary at primary_dataserver.primary_database with all tables named 'authors' ... go
create subscription subscription_default_path for replication_definition with primary at primary_dataserver.primary_database with replicate at replicate_dataserver.replicate_database
create subscription subscription_alternate_path for replication_definition with primary at primary_dataserver.alternate_path_name with replicate at replicate_dataserver.alternate_path_name