Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server replication between different schemas


By:   |   Last Updated: 2015-10-27   |   Comments (1)   |   Related Tips: More > 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.

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


Last Updated: 2015-10-27


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Good information.


Learn more about SQL Server tools