SQL Server replication is a powerful feature that allows us to move data from one database to another. However, there may be situations where we need to replicate tables with the same names as existing tables without overwriting them in the subscriber database. In this article, we will explore a solution to this problem.

By default, when setting up replication, the Replication Schema is set to the Destination Object Owner, which means that the schema of the replicated tables in the subscriber database will be the same as that of the source tables. However, by adjusting the subscriber schema, we can create the replicated tables under a different schema without impacting the existing tables.

Let’s walk through an example to demonstrate 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','Associate',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 configuring the Replication Publication using the New Publication Wizard, we can change the default behavior for the destination object owner. By default, it is set to the source table owner, which in our case is the Employee schema. However, we want to create the replicated tables under a different schema called NewSchema.

After making this change and setting up replication on the subscriber, we can see that the articles are created under the ‘NewSchema’ schema, while the existing table remains intact.

If we are setting up replication with scripts, we need to ensure that we update the scripts to point to the correct destination schema. Here is an example:

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. However, it is important to document these settings to ensure that if we need to reconfigure replication, we use the same settings without causing any issues on the subscriber(s).

In conclusion, by adjusting the subscriber schema, we can replicate tables with the same names as existing tables without overwriting them in the subscriber database. This allows us to efficiently manage data replication in SQL Server.