some records are not getting synched

RBohannonRBohannon Posts: 25
We are using the SDC 6.0 programmatically to compare yesterday's backup to today's backup and produce a SQL script of the changes at the store. The SQL script is later executed on a remote server to produce a consolidated database for all stores.

One particular table in our database is a financial daily summary table. The PK is compound with a varchar(8) field and a datetime field. The varchar(8) field is a location code and is typically identical for all records in the table. The datetime field has a date value for each date that the store has been open, the time values are all zero. The rest of the columns are numerical and contain financial summary information.

At the end of the day, the record with today's date gets populated with financial data and a record with all zeros in the financial fields gets generated with tomorrow's date in the PK.

The problem is occasionally the UPDATE statement is not getting generated resulting in random dates with all zeros in the financial fields in our central database.

The collation is the same between the two databases and there are no trailing spaces.

Do you know how this could happen? Were there any bug fixes in 6.1 or 6.11 that addressed this issue specifically?

Thank you.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    Does this table map automatically in the SQL Data Compare UI? If not, it could be that the order of the columns comprising the compound key are different. If this is the case, the tables cannot be mapped and there is no way of manually mapping them if the comparison is done between a live database and a backup -- with backups you're not allowed to map tables and columns manually.

    I hope that this is the problem. If it is, just recreating the table with the compound key columns in the same order will fix it.
  • We are comparing two backups. The backups were both made from the same database (at different times). So the keys are in the same order.

    The tables do map because we receive 99% of the changes. It's just occasionally a change is not reported.

    Thanks.
  • Hi,

    The only possibility I can think of at the moment is that the missing record is getting inserted or updated during the time the backup happens. If this happens, SQL Server appends a mini transaction log backup to the full backup with any changes that happen during the backup. SQL Data Compare doesn't currently use this, so there's a chance that data changing during the backup may be missed in a comparison.
    Robert Chipperfield
    Red Gate
  • I don't think that could be it because if a user created the record during the first backup then it would be missing in the first backup but present in the second and therefore would appear as a change in the RedGate comparison and consequently appear as in INSERT statement in the change script.
  • True - but if they created it before the first, then updated during the second, it could appear as the same in both backups, if that makes sense. Or, in a truly terrible diagram:

    - Insert record
    - Perform backup 1
    - .... one day later...
    - Start backup 2
    - Update record with previous day's data
    - Backup 2 completes

    As a first step, I'd check to see if your backups are running at the same time as the insert / update - if not, then we can rule it out as a possible cause.
    Robert Chipperfield
    Red Gate
  • To continue with your example, what happens with the third backup? Won't it show the changes that were made during the second backup? Won't it show the changes between the first and third backup?
  • Yup, agreed - it should show up in the third even if not in the second.

    If you've got a couple of backups that exhibit the problem, and you're able to send them to us, I can try and see what's going on. Let me know if so and I'll arrange some means of you getting them to us.

    Thanks,
    Robert
    Robert Chipperfield
    Red Gate
  • I don't have copies of backups that exhibit the problem yet. Our clients don't typically notice the issue until days after it happens (after the backups have been discarded). I've been trying to get representative backups. If I do, I'll let you know. Thanks for your help.
Sign In or Register to comment.