March 9, 2010 at 12:42 pm
Huge thanks for the help yesterday! My deployment went without a hitch! Now I have another deployment that is going to involve dropping/recreating/adding new indexes. As I have read, these are not supported in transactional replication......
So, the question is....how do you handle those kinds of changes??????
Thank you, in advance!
March 9, 2010 at 1:33 pm
Angelindiego (3/9/2010)
Huge thanks for the help yesterday! My deployment went without a hitch! Now I have another deployment that is going to involve dropping/recreating/adding new indexes. As I have read, these are not supported in transactional replication......So, the question is....how do you handle those kinds of changes??????
Thank you, in advance!
If schema change option is set to true, you don't have to anything as said yesterday. Else drop article from publisher and make any schema changes or drop\add indexes and add the article back to publisher and re-intialize subscriber it should work. Add or Drop index is same as schema changes no difference.
March 9, 2010 at 1:40 pm
March 9, 2010 at 1:55 pm
Script out the replication before you make changes so that you can rebuild replication from scratch if some thing breaks.
March 9, 2010 at 2:03 pm
March 10, 2010 at 5:22 am
Unless it is a PK or an FK that you are creating, you do not have to drop the article out of replication to add/remove indexes. You can have different sets of indexes in Publisher and subscriber. As an example, Your publisher might be a high transaction Database. And your subscriber might be a report server. For report servers you might need more indexes. So you will have different sets of indexes.
Another thing, even if you have the "Replicate Schema Changes" set as true, Indexes WILL NOT be replicated. For the indexes to be replicated, you will have to use sp_addscriptexec to replicate index changes.
-Roy
March 10, 2010 at 8:26 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply