Losing record after synchronizing subscriptions

Hi

We got a server running SQL server 2000 SP4 with a database that’s being replicated

There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy

Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key ‘ClientID’ and then a record is inserted in a table with actions for that client with a foreign key ‘ClientID’ referring to the client table. After sync’ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.

As far as I know the tables are correctly formed with identity set not for replication and so on.

Shortly, I can’t find any problem, a specially when it doesn’t happen always.

If anyones has faced this and got a solution, please let me know.

Thanks.

Raf

Are there constraint violations that are happening?

Also look at the article property compensate_for_errors. it is turned ON by default. What this does is, say for an insert coming from publisher to subscriber, if there is a constraint violation or some other failure, a delete is sent back to the publisher resulting in data loss.

Set this propery to false and monitor your system for data integrity.

|||

Thanks

I'll check this out

Looking for suggestions Cluster or Mirror

Hello,

We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.

John

Fail-over clustering and database mirroring are both high availability solutions that don't have any direct effect on performance. Fail-over clustering relies on shared external storage between the nodes (which is a potential single point of failure), and requires higher-end hardware in most cases. It works at the instance level. Failing over a node in the cluster typically takes 1-2 minutes on a large, active database instance.

Database mirroring works at the database level(not the instance level). There are two copies of the data (one on the Principle and one on the Mirror), so you need twice the storage space. Only the Principal database is available to service clients. If you want automatic fail-over with DB Mirroring, you need a Witness Server. You have to be running in Synchronous mode with Saftey turned on to get automatic fail-over with DB Mirroring. Database fail-over with mirroring is more like 10-15 seconds.

If you are doing Replication, it will might be easier to do fail-over clustering.

|||

here are some resources that may be helpful in your decision-making:

Failover Clustering white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

SQL Server 2005 High Availability Resources:

http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx

Looking for suggestions Cluster or Mirror

Hello,

We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.

John

Fail-over clustering and database mirroring are both high availability solutions that don't have any direct effect on performance. Fail-over clustering relies on shared external storage between the nodes (which is a potential single point of failure), and requires higher-end hardware in most cases. It works at the instance level. Failing over a node in the cluster typically takes 1-2 minutes on a large, active database instance.

Database mirroring works at the database level(not the instance level). There are two copies of the data (one on the Principle and one on the Mirror), so you need twice the storage space. Only the Principal database is available to service clients. If you want automatic fail-over with DB Mirroring, you need a Witness Server. You have to be running in Synchronous mode with Saftey turned on to get automatic fail-over with DB Mirroring. Database fail-over with mirroring is more like 10-15 seconds.

If you are doing Replication, it will might be easier to do fail-over clustering.

|||

here are some resources that may be helpful in your decision-making:

Failover Clustering white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

SQL Server 2005 High Availability Resources:

http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx