Move schema changes and data from Publisher to subscriber in MergeReplication

Question

I have a corporate server and around 50 remote clients. Images are added to Remoteclients and these Images are merge replicated to CorporateServer. Now initially all these images were on BLOB. We have decided to use filestream and create a new table containing Image binary. So we have partioned the original Image Table to Image and new table Image_Source.This is on production and Corporate data size is arnd 250 GB.

Now we have the following Tables:-

1. Images
2. Images_Source

I have to do the following things.

1. Add this new table to publisher and merge replicate it to subscribers.
2. Copy all image blob from Images and transfer it to Images_Source.

To Achieve this i will do the following things:-

1. Add new table to Publisher on corporate and turn Replicate Schema Changes to True. This way schema will be synced across corporate and RemoteClients.
2. Now on corporate, I will disable the triggers for Images_Source table, and move data from Images to Images_Source table using a Job.
3. Once all data is there in Images_Source table, All subscrbers will sync.

Now I want some expert advice on the correct procedure for doing this kind of changes. If you guys could share your experiences and Things to remember before performing such a change.

Answer 1

I have never done this with images, but adding new objects to a publication usually follows this script:

1. create the table(s) on the publisher
2. Stop replication processes between your publisher and subscribers
3. Add these tables to the corresponding publications via sp_addmerge... Parameters shall include a request for snapshot reinitialisation (it does not mean that the whole snapshot will be retransfered to the subscriber, but new objects have to be added to the snapshot before being added to subcribers databases).
4. At this stage, a new snapshot will be built
5. replication can be launched again

Hope it helps