2

Here's my situation. We have cash registers that send transactions to the store server. With every transaction received, the store server updates tables... at the same time other management transactions modify tables. At the end of the night we send everything to the head office for which they have all the info for all the stores. usually stores and cash registers only use SQL Express (we support 2008 and up) as for the central head office DB, they usually use SQL Server Standard edition and up (again we support 2008 and up).

I'm trying to revamp that process due to the fact that right now our process requests from the stores all data of days that have been modified and that for 25 tables out of hundreds. The problem is that if 1 record changes for a previous day we send all data of that day that was touched for all 25 tables.

These 25 tables are daily summaries that are updated live at the store and the changes are sent to the head office once a night usually at midnight.

My first instinct was to create triggers on each of the 25 tables until I realized that every item of a transaction will trigger 25 different triggers. Which quickly became unattractive.

my next try was to send each transaction once to the head office and replay everything at the head office, but that will be too much of a load at the head office since they might be receiving data from 200 stores all at once.

Ideally, I'm looking for a way to transfer only data from the 26 tables that NEED to be transferred. Maybe by having the head office compare some type of Checksum of every table with a store's checksum of the same tables (SQL checksum won't work since the head office will always have more data).

I'm asking for different opinions to hopefully find other ideas I can explore.

very much appreciated. Thank you

  • 1
    Is it so your nightly process is a little bit faster? I'd probably argue that the investment in complexity to prevent sending redundant data for what is essentially an offline, background process might not be worth it. Does it matter if at midnight it takes 12 minutes or 14 minutes? You may be able to shave time off by sending less data but filtering is never free - identifying the rows that have changed and marking them before and after changes will take time, too. – Aaron Bertrand May 19 '17 at 20:06
  • You can also write your other triggers to check which columns are being updated, and just return if the operation is coming from the other trigger. – Aaron Bertrand May 19 '17 at 20:08
  • How is the data sent? ETL? db to db? Files? – Sir Swears-a-lot May 23 '17 at 8:41
  • @Peter we use Files to transfer between Stores and Head Office to make sure that we are not dependent on any DB connection. – JohnG May 23 '17 at 12:20
2

You might be able to do something with actual SQL replication, but if you've already got an existing routine, just modify it to filter specifically on a new bit field in every table: uploaded or whatever you want to call it.

So when the register writes the changes to the table, make sure it clears that bit. Or, if you can't alter that part of the routine, put an ON UPDATE trigger on the table that clears it when a row is updated.

Then make sure that the "upload to corporate" routine sets all those bits when it copies the data up.

Or, what might be even more useful, is to have a "last changed" field updated by your register uploads, and a "last uploaded to corporate" field. That way you can compare the two.

  • I agree with the idea of last changed date. – Sir Swears-a-lot May 23 '17 at 8:27
  • @BradC +1 I'm leaning towards something similar to this idea, where I would add a status field in each table (0=don't send, 1 for needs to be sent and 2 as sending/processing) – JohnG May 23 '17 at 12:37
0

I would take the lowest level transactions and transfer them to a central location. Load and then batch process. Preferably without triggers.

Id have create_dt and change_dt fields which id use to identify deltas that need to be resent. Id then periodically reconcile source and target.

Whether you overwrite the changed records or append deltas will depend on what your till captures, your account practices and level of detail required. I.e Cash vs accrual and whether you need to reconstruct how it looked at a point in time. (stationarity of record)

I do something similar with my Datawarehouse. We ship low level data using ssis, but also have a step in our etl which performs sums/counts on both systems and stores the result. If it varies we know to reload.

  • That wouldn't be efficient for us. since sending the low level transactions and summarizing for all 25 tables... and this happening for 200 stores at the same time would be too much processing for one server. I like what Aaron Bertrand mentioned in his comments and I'm leaning towards adding a Status column similar to the LastChange which would be 0 don't send, 1 for needs to be sent and 2 as sending/processing. – JohnG May 23 '17 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.