SQL Server replication between different schemas

By:   |   Comments (1)   |   Related: > Replication


SQL Server replication is great to move data from one database to another, but sometimes there is a need to replicate tables with the same names as existing tables. How can we replicate these tables without overwriting the existing tables in the subscriber database?


Normally when we setup replication we don't change a lot of the default values. One of the default configuration values is Replication Schema which is set to Destination Object Owner ( Subscriber Articles Owner) will be same as of Source Object Owner. If we have Publication articles configured under the ABC schema then by default the Subscriber schema will also be ABC. By adjusting the subscriber schema, we can create the tables on the subscriber under a different schema without impacting the existing tables.

Let's create a test database, a table and load some data to show how this can be done.

Create Database ReplicationTest 

Create Schema Employee

Use ReplicationTest
Create Table Employee.TestEmployee(
Empid int primary key clustered,
EmpName Varchar(10),
EmpDesg Varchar(10),
EmpContactNo int

insert into Employee.TestEmployee values(101,'John','Assosiate',968755662)

insert into Employee.TestEmployee values(102,'Raki','Consultant',874565623)

insert into Employee.TestEmployee values(103,'kijnh','Sr Engg',748595765)

insert into Employee.TestEmployee values(104,'lira','Director',732145981)

insert into Employee.TestEmployee values(105,'mojs','Consultant',806598713)

When we configure the Replication Publication using the New Publication Wizard, on the Article Page click on Set Properties of All Table Articles.

sql server replication

If we look under the Destination Object section, we can see default behavior for the destination object owner to be the source Table owner.

replication properties

If we don't change this and let replication configure as is, the article will be created using the same schema as the Publication which is the Employee schema in our case and you can see below that we already have a table named Employee.TestEmployee that we don't want to overwrite.

ssms folder view

Instead, we will change this setting to a schema called NewSchema as shown below.

replication destination object owner

After making this change and replication has been setup on the subscriber, if we look at the subscriber database we can see the articles are created under the 'NewSchema' schema and the existing table stays entact.

sql server management studio replication folder

If we are setting up replication with scripts, make sure to change your scripts as highlighted below to point to the correct destination schema.

use [ReplicationTest]
exec sp_addarticle 
@publication = N'ReplicationTest', 
@article = N'TestEmployee', 
@source_owner = N'Employee', 
@source_object = N'TestEmployee', 
@type = N'logbased', 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'TestEmployee', 
@destination_owner = N'NewSchema', 
@vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_EmployeeTestEmployee', 
@del_cmd = N'CALL sp_MSdel_EmployeeTestEmployee', 
@upd_cmd = N'SCALL sp_MSupd_EmployeeTestEmployee'

As shown we can configure replication using different schemas on the publisher and subscribers, but we should make note of this in our documents to make sure if we need to reconfigure replication we use the same settings without causing issues on the subscriber(s).

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 27, 2015 - 9:44:25 AM - Pavan Back To Top (38968)

Good information.

get free sql tips
agree to terms