question

arvindtegampure avatar image
arvindtegampure asked

Transaction replication with new schema

Hi All, 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
sql-server-2008replicationschema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sjimmo avatar image
sjimmo answered
Make sure that the replication publisher/distributor has read/write access to the database to include the tables/schema. Otherwise, you are correct and it is annoying. When you open the article properties window it does not show what the publication is set up for, but what the defaults are. If you accidently hit OK instead of cancel you have set the properties back to the default. I have verified this with MS. If you do this while a snapshot is generating, you have invalidated the snapshot and it will recreate but using the default settings. I use the feature to send to different schemas all the time, and haven't had an issue, so I know it works. You just have to pay attention to what you are doing.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.