Yes, you can use more that one subscription. But you have to be very careful in setting them up. If there is ant RI between the tables of the different subscriptions, replication can fail. SQL replication works at the subscription level when moving data. And the order that the data move is not guaranteed the same order that actually occurred. As an example, say you have two tables that have RI defined between them. Due to the 200 table limitation, you have to put them in separate subscriptions. On the "Source" DB someone inserts a new row in the parent table then very shortly after inserts/updates a row in the child to point to the new row in the parent. All of this happens "between" refresh cycles of APPLY. Then APPLY does a refresh and starts with the subscription that the child table belongs. It tries to do the insert/update that occurred on the source, but it fails because because the RI prevents the insert/update since the "new" parent row has not been inserted yet (APPLY has not gotten there yet).
This scenario can be fixed if you put all RI related tables in the same subscription. The problem still occurs if you have more than 200 tables all with RI to each other. There is no way to split it that will guarantee that the Units of Work (UOW) are processed in the exact same order on the destination as they were performed on the source. It has been awhile since I had tried using SQL replication, so IBM may have done something to fix this problem.
Andy
dBforums
>
Replication
06-08-06, 20:35
Linear Mode
