newly inserted records disappear

[jmille34]

I'm doing a big insert, a few hundred records into 4 related tables. I'm basically using a custom non-sql script to execute commands one at a time to do a bulk insert of data. So I run my script, which takes maybe 10 seconds, and then I view the data in the app that uses this database. Ok, all is well so far. So I use the app for a few minutes, and then suddenly the data is gone. The part that's killing me is that there is a delay where the data is there and available and even usable for exports, and then after some undetermined length of time later, it's gone.

The database is replicated between two servers, and I'm doing the insert into the subscriber. I have no tried insert into the publisher. But I do uploads like this all the time, but this one particular type of upload is especially prone to this problem. I really don't even know where to start. I read another thread on here that blame a bad trigger as the culprit, but I have never used triggers, aside from whatever is created by the replication setup wizard, and then of course I have never touched those.

It's just that delay that I can't wrap my head around. The data will be available for, literally, a few minutes, before it disappears. Any advice on where to start looking?

 

[jmille34]

This is SQL 2000, btw

 

[Mich]

Exactly what type of replication have you implemented? Is the data disappearing from the publisher, subscriber, or both?

-If it ain't broke, break it and make it better.

 

[jmille34]

It is merge replication. All traffic is taking place on the subscriber, because a year or so ago we had to leave our isp, so we directed traffic to the secondary server, and that's how it has been ever since. So the import routine is against the subscriber, the check is against the subscriber, and replication still seems to be working perfectly, ie, identical. Another weird part is that if I run the import 2-3 more times, eventually the data will stay put.

 

[Mich]

Sounds like you might have some conflicts in your merge replication structure. Do you only have one publisher and one subscriber? If you have multiple subscribers, do you know the subscriber types and assigned priorities?

replication still seems to be working perfectly

Does this mean you can verify the data on the publisher then it disappears from there as well?

I have several other questions, but let's start here.

-If it ain't broke, break it and make it better.

 

[jmille34]

Unfortunately, I have never even really looked at the publisher during the couple minutes when the data exists, but now I am curious. I wonder if the data every shows up there. It is a 2 system setup, btw, one pub, one sub.

 

[Mich]

Have you figured this out?

-If it ain't broke, break it and make it better.

 

[jmille34]

Yes, I'm doing the insert on the subscriber, but there is a conflict at the publisher. My merge runs once per minute, so as soon as that merge runs, it deletes my records. The reason is that the records I'm inserting have a foreign key to a parent table, and that parent table has fewer records than the subscriber table. So the replication tries to copy these new records to the publisher, but when it gets there, there is no parent record to satisfy the constraint, so it sits in the conflicts queue on the publisher and then deletes the records from the subscriber. The question now is: why is there a different record count between the publisher and subscriber? I'm looking at doing a query to pull all records that are in the sub but not pub and then insert those records again (talking about the parent table here). Those new inserts should propagate up to the publisher, and then I'll delete the orphaned records. The other problem is that I found two other tables that are off by 1 record each. The problem past that is that I'm having similar symptoms on another site with a much huger database. Arg.

As a side note, I put these servers in just when SQL 2005 came out and before the "mirroring" feature was even available. Would it be worth upgrading from SQL 2000 to 2005?

 

[Mich]

I thought there was a conflict there somewhere. You might want to "blow up" your replication structure and start over. Sounds like the server you work with (subscriber) should be the publisher. Record count differences are scary. I'd snapshot my primary dbs and start over.

The main advantage of db mirroring, in my opinion, is in high speed (LAN) environments. A key feature of mirroring is automatic failover using a witness server. This feature is called high availability. In this mode transactions must first commit to the mirror, then to the principal, then a ack is sent to the application. As you can see, if you're working over a relatively slow connection your application will suffer.

If you're not in a high speed environment I wouldn't suggest upgrading for db mirroring alone. However, there are a lot of other reasons to upgrade.

-If it ain't broke, break it and make it better.

 

[jmille34]

Yes, I've been considering redoing the replication from scratch to make the current server the publisher, but I'm sure you know how huge a hassle that is. But the deleted record thing bit me on the butt again today. I copied a big relational structure, and it was all there, and then 5 minutes later I realize huge chunks of it are missing. Baaahhh.. I guess it needs to be done. Thanks for the info, Mich.