How to Handle Database design change in Merge Replication

Question

How to Handle Database design change in Merge Replication

ruchirdhar

SSC Enthusiast

Points: 129
More actions
September 6, 2004 at 3:21 am

#86368

Hi Friends,
I am implementing Merge replication, I have around 50 subscribers which are located at different places, Due to some reasons I need to change my database design some of the changes requires dropping of primeary key and creating new primary key.
To make it possible to make my replication work fine I need to drop publication and subscritpion, make design change at 50 subscriber sites/ publication site and then recreate subscription/publication at pulisher.
My Major concern is my subscription which are at different location. Doing above will become a programming nightmare for me.
Can anyone suggest me better solution in the above mentioned situation.
Isn't it possible that such changed are automatically propagated to subscribers.
Please help me.
Thanks in Advance.
Ruchir Dhar Dwivedi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 1

ruchirdhar,

As you described this is the way the DDL changes have to be made. ( Drop, alter, re-create ) hurray for Microsoft. There is a more sofisticated way of hadeling changes and that is using the BEFORE replication action ( the exact name slipt my mind ). You can sent al subscribers a script and let it execute before replicating but I do not know if this can be done in your case.

I've been digging in to this stuff and can give you some extra advise ( something to think about );

( from my notes on this )

Is the Merge replication the right form of replication for you?

This is the nasties and most unrelyable form of replication.

Snapshot...

Better use a backup and restore iso a shapshot because if you have to snapshot a 5 Gb database to 50 subscribers you have to wait forever ( network load ) and better DO NOT AUTO SYNCHRONIZE for this same reason ( each time this is activated it generates a tremendous server and network load ).

Which part

Avoid replication of the whole database. Replicate only the data you realy need

Server load

Avoid RAID 5 because of the many write of a replication. Better use RAID 1 or 10 disk array

Restrictions / limitations

Avoid replicating columns with TEXT, NTEXT or IMAGE data types ( they require an enormous overhead )

Pay attention to Triggers , Auto increments ( use NOT FOR REPLICATION in the ( base )database )

Always use a Id column with is a primary key

Inside SQL server 2000

Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes. The reason the restriction for merge replication is stricter than the restriction for transactional replication is because conflict tables have the same structure with additional columns that store information about the origin of the conflict and the specific reason for the conflict. Because additional space is needed to record this conflict information, the maximum row size is less than the maximum row size for transactional replication

Conflicts

Merge replication allows various sites to work autonomously and at a later time merge updates into a single, uniform result. The initial snapshot is applied to Subscribers, and then SQL Server tracks changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers continuously, at a scheduled time, or on demand. Because updates are made at more than one server, the same data may have been updated by the Publisher or by more than one Subscriber. Therefore, conflicts can occur when updates are merged. <O> </O>

Merge replication includes default and custom choices for conflict resolution that you can define as you configure a merge publication. When a conflict occurs, a resolver is invoked by the Merge Agent and determines which data will be accepted and propagated to other sites. Merge Replication is helpful when: <O>

Multiple Subscribers need to update data at various times and pass those changes to the Publisher and to other Subscribers.<O> </O>
Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.<O> </O>
You do not expect many conflicts when data is updated at multiple sites (because the data is filtered into partitions and then published to different Subscribers or because of the uses of your application). However, if conflicts do occur, violations of ACID properties are acceptable.

</O>

Handling data

Always use the MSrepl_ stored procedures ( if possible )

Backup

Do not forget to backup the subscriber(s) as part your publishers backup plan ( add / adjust your database maintenance plan ). If there is a failure ( power down oa ) during the replicating process the databases, publisher and subscriber, will disappear from the tree structure in the console pane. These database(s) can ONLY be restored from a BACKUP of this SAME database.

Authorisation

Be sure all the (user) right and permissions on both the publisher and the subscriber MUST be identical or otherwise this will cause a conflict ( if this conflict occurs during a replication this can cause loss of data or losing the whole database ).

Recommendations;

Put the database log file on its own RAID 1 disk array
If distribution and publishing components are located on the same server use "Pull subscription" ( performance issue )
When filtering data on the publisher be sure of the right index on it ( table scan on data before filtering )
Having heavy replication load use a dedicated private network and add a additional NIC to each server connecting them using a private hub, cross over cable or a switch. ( use 100Mbs or 1Gbs NICs and matching connections and hardware ).
Keep in mind that more indexes causes a slower replication
Be sure not to turn on the "Auto Shrink" and the "Auto Close" database options, as they will result in unnecessary overhead to the distribution database.

Note; I have never actual implanted a merge replication. I used a transactual replication / logshipping instead.

I hope this puts you in the right direction. If you have any questions please do not hazitate and e-mail me

Regards,

Guus Kramer

The Netherlands

more info / sources;

Snapshot replication -- http://www.sql-server-performance.com/snapshot_replication_tuning.asp

Transactional publication -- http://www.sql-server-performance.com/transactional_replication.asp

Merge publication -- http://www.sql-server-performance.com/merge_replication.asp

ruchirdhar SSC Enthusiast Points: 129 More actions · Answer 2

Hi Guus,

Thanks for your reply.

Actually In my case subscribers are salesperson of my company which will be making changes to their replicas respectively then at the end of the day/or ondemand/at regular interval they will synchronize their replicas with central server.

I do think that this case is best suited for Merge application. I mean working autonomously, independently making changes to their copies and then synchronizing.

The Hurdle with me as I already Told is how to handle things in the case when I am making design change which require all hell things to do (dropping/re-creating subscription and publisher).

For this I'll need to inform all my subscribers to make design change in their replicas respectively and then run script to re create publisher/subscription at publisher and since all publisher as well as subscriber might be at different geographic locations this make my job tough.

Please enlighten me what should I do ??

Thanks.

Attinder Singh SSC Journeyman Points: 77 More actions · Answer 3

After making the changes, if you generate the snapshot and ask your users to re-intialize thier subscription then I think it will work, but as GUUS suggested keep the size of your snapshot in mind.