SQL Server replication between different schemas

By: Rajendra Gupta   |   Updated: 2015-10-27   |   Comments (1)   |   Related: > Replication

Problem

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?

Solution

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 
go

Create Schema Employee
go

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

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.

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

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.

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

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.

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

• Check out these other Database Replication Tips

About the author

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