Replication between Different Schemas

By: Dan Stewart
November 25, 2011

How do I replicate tables from different schemas?

Short Answer

Set the destination owner in the publication to be the schema name in the subscriber.

Long Answer

Here's the scenario, you have two databases. The first database is the publisher. The second database is the subscriber. You want to replicate tables from the publisher to the subscriber. You do not want to give the subscriber full rights to the replicated tables.

So you want to replicate the tables to a special schema on the subscriber. Setting up a schema will give you the power to control what the subscriber can do with the tables.

Disclaimer

I am not a DBA, but I play one at work sometimes. I welcome your feedback.

The Setup

Open SQL Server 2008. Create two databases. Name the first PublisherDatabase and the second SubscriberDatabase.

Inside the PublisherDatabase, run the following script to create a table.

USE PublisherDatabase
GO

CREATE TABLE Person
(
  Id UNIQUEIDENTIFIER PRIMARY KEY,
  FirstName NVARCHAR(50) NULL,
  LastName NVARCHAR(50) NULL
) ON [PRIMARY]
GO

Let's insert a person.

INSERT INTO Person 
  (Id, FirstName, LastName) 
VALUES 
  (NEWID(), 'Dan', 'Stewart')

The goal is to replicate this table to the SubscriberDatabase, but the user of the SubscriberDatabase should only have select rights on the table. They cannot insert, update, or delete records from the table. Creating a schema and replicating the table to this schema is a good way to limit rights.

Schema

Let's create the schema on the subscriber database.

1. On the SubscriberDatabase, go to the Security folder, Schemas.
2. Right-click the Schemas folder and choose New Schema.
3. Name it "repl".
4. Make "dbo" the owner.
5. On the Permissions page, search for your user.
6. Allow your user to Control, Select, and View definition.
7. Deny everything else.
8. Click OK.

We now have a schema named "repl" and our user has limited rights in the schema.

Replication

Let's set up replication.

1. Right-click on Local Publications and choose New Publication…
   
2. Click next through the initial screens until you get to the Publication Database.
3. Choose PublisherDatabase and click next.
4. Choose transactional publication and click next.
5. Checkmark the tables that you want included in the replication.
6. Click on the article properties dropdown and select All table articles.
7. There are a ton of settings here. I would at least set copy nonclustered indexes to true.
8. Keep all of the other defaulted values.
9. Change the destination owner to repl.
   
10. Click next until you get to Snapshot Agent. Check the box to create the snapshot immediately.
11. Click next and choose a Windows account to run the replication under.
12. Click Finish to skip to the end.
13. Give the replication a name.
14. Click finish.

You might get an error that the SQL Agent is not started. Just right-click on the SQL Server Agent and start it.

Now we need to set up a subscription.

1. Right-click on the publication and choose New Subscriptions…
2. The New Subscription Wizard will launch.
3. Click next and choose the publication.
4. Click next until you get to the Subscribers page.
5. Choose the SubscriberDatabase.
6. Click next and choose a user to run the distribution agent under.
7. Click Finish to skip to the end.
8. Click Finish.

You would think the Person table would be in the SubscriberDatabase right? Well, we have one more step to go.

1. Right-click the publication.
2. 1. View Snapshot Agent Status.
3. Click Start.

You can click the monitor button to watch the process.

Eventually you will replicate the table to the SubscriberDatabase.

Congratulations, you have now replicated a table from one schema to a different schema.

Don't forget to use the schema name with querying the table.

USE SubscriberDatabase
SELECT * FROM repl.Person