Asked By Paul Buckle
19-Nov-09 08:33 AM
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