Replication on sql 2005

Asked By Paul Buckle
19-Nov-09 08:33 AM
Earn up to 10 extra points for answering this tough question.
Scenario :

SQLServerA - main database server. Publishes replication to SQLServerB.

SQLServerB - reporting server - uses replicated databases.

Problem :

When we replicate a database using Transactional replication from SQLServerA to SQLServerB, the replicated databases drops the indexes.

Solution ???? :

We need to set the flag "Copy Clustered Index" to "True" for the Articles in the Local Publications job. Having to do this after we create replication is a pain. Is there a way to set this flag automatically ? By automatic I mean setting it as a default rather than having to run a script afterwards.

I have searched for a solution and have found reference to "@schema_option" which would in theory allow us to set it in a script but if we set it in a stored procedure (sp_addarticle ?) would it set it automatically/by default for every new publication or would it require manually running afterwards on each database ?

Hope this makes sense....

Regards,

Paul
Create New Account