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', <span style="color: red;">
@destination_owner = N'NewSchema'</span>,
@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
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book “DP-300 Administering Relational Database on Microsoft Azure.” I can be reached at: Rajendra.gupta16@gmail.com for any consulting help.
- MSSQLTips Awards:
- Author of the Year – 2022 | Author Contender – 2021/2023/2024 | Champion Award (100+ tips) – 2020