SQL Transactional Replication with Different Schema

SQL Server Administration
1

Hi Team,

I am trying to create Transaction Replication on SQL server 2008 R2.

The subscriber database will be new and we need to set new schema for it, meaning we want to have "NewSchema"."TableName" rather than dbo.tableName.

I have created new database and created new schema and choose this new schema value while creating the replication i.e "Destination Object Owner" for all tables.

After saving the value is again set to default, I read that it is default behaviour of SQL server.

But the problem is when I see the synchronization status it says "The concurrent snapshot for publication 'xxxx' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for"

The Snapshot is complete and Log reader agent also look fine.

Can you please help me in this!

I think the because of the security/permissions of new schema its failing. Because earlier with default schema replication worked perfectly fine.

Please can you let me know the steps to follow for this, creating schema, permissions for it and then for replication.

Regards, Arvind

2

I know nothing useful about Replication, and in particular whether replication to a different Schema is possible / a good idea!, but just in case it is NOT possible:

Could you replicate to a different database, on the Target machine, which is identical to the Source machine (i.e. same schema etc.) and then in your ACTUAL Database, on the Target machine, set up SYNONYMS for the tables (in the replicated database on the target machine) which DO use the Schema that you need?

3

Thanks for the Reply Kristen.

But requirement is to do it from Replication.

There is option of doing it from the wizard.

For some reason I am getting the above mentioned error.

May be because of the access of user and/or new schema I created. Not sure!! :frowning2:

4

And the idea behind creating different schema is the target database going to data mart at the end , so it will other databases will also get merged.

We want to differentiate the tables like for "USERS" table which will be common in most of the databases.
abc.USERS , xyz.USERS etc.