We have merge replication with SQL Server 2000 publisher and MSDE subscribers(32 of them).
Some of our subscribers have slow connection speeds and take many hours to push initial snapshots to them. Even when the snapshots are delivered, we are experiencing data loss in some of the subscriber tables as if packets are lost during the snapshot process. Sometimes this leads to a cascading delete back to the publisher. This seems to normally occur when a table at the subscriber has a foreign key and the related table has not received all of its data prior to the parent table trying to insert rows. The insert fails due to foreign key constraint violation leading to a conflict within SQL. But foreign keys are not the only tables affected. I have seen missing rows in tables with no constraints at all. The data is just missing with no report from SQL Server that anything is amiss.
On fast connections, we do not see this problem. But it is at best a coin toss on slow connections. We never know what the outcome will be. Not exactly a good feeling.
So I've tried to devise a plan of delivering snapshots more predictably thinking that a file copy of a database backup and then restore would be more reliable.
My plan:
1)Create a local subscriber database for the remote subscriber database that needs to be reinitialized.
2)Snapshot the local database with the partitioned data
3)Backup the local database
4)FTP the local database backup file to the remote site
5)Restore the backup file to the remote subscriber database
6)Subscribe the newly restored database as 'in sync'
There may be some smaller steps in the process but this is the big picture.
My question is specifically that step 4, considering that the backup may be 100MB and this is a slow connection, could take hours to deliver. So by the time it is fully copied, restored and subscribed, there is a good chance that some data coming from one of the other 31 sites has made my new subscriber out of sync even though I told it is was in sync. How can I resolve this issue? Is there any automatic process that can run checksums at the row level and determine differences and deliver only those differences. If validation is run and it fails but the only solution is a re-init of the subscriber, then the entire exercise was for nothing.
Do I have to run queries against publisher and new subscriber to try and find differences and deliver those rows manually. This will be quite time consuming.
What is the recommended way to accomplish this? Any insight into how rows are being dropped during initialization would also be welcome. It is quite unnerving to see that happen. I never really trust a snapshot until days after it as been delivered and no one has complained.
TIA!!
J
Some of our subscribers have slow connection speeds and take many hours to push initial snapshots to them. Even when the snapshots are delivered, we are experiencing data loss in some of the subscriber tables as if packets are lost during the snapshot process. Sometimes this leads to a cascading delete back to the publisher. This seems to normally occur when a table at the subscriber has a foreign key and the related table has not received all of its data prior to the parent table trying to insert rows. The insert fails due to foreign key constraint violation leading to a conflict within SQL. But foreign keys are not the only tables affected. I have seen missing rows in tables with no constraints at all. The data is just missing with no report from SQL Server that anything is amiss.
On fast connections, we do not see this problem. But it is at best a coin toss on slow connections. We never know what the outcome will be. Not exactly a good feeling.
So I've tried to devise a plan of delivering snapshots more predictably thinking that a file copy of a database backup and then restore would be more reliable.
My plan:
1)Create a local subscriber database for the remote subscriber database that needs to be reinitialized.
2)Snapshot the local database with the partitioned data
3)Backup the local database
4)FTP the local database backup file to the remote site
5)Restore the backup file to the remote subscriber database
6)Subscribe the newly restored database as 'in sync'
There may be some smaller steps in the process but this is the big picture.
My question is specifically that step 4, considering that the backup may be 100MB and this is a slow connection, could take hours to deliver. So by the time it is fully copied, restored and subscribed, there is a good chance that some data coming from one of the other 31 sites has made my new subscriber out of sync even though I told it is was in sync. How can I resolve this issue? Is there any automatic process that can run checksums at the row level and determine differences and deliver only those differences. If validation is run and it fails but the only solution is a re-init of the subscriber, then the entire exercise was for nothing.
Do I have to run queries against publisher and new subscriber to try and find differences and deliver those rows manually. This will be quite time consuming.
What is the recommended way to accomplish this? Any insight into how rows are being dropped during initialization would also be welcome. It is quite unnerving to see that happen. I never really trust a snapshot until days after it as been delivered and no one has complained.
TIA!!
J
