5

The current sql server database is overloaded and many transactions are failing. The powers that be have a proposed solution to build a separate database to handle/store transactions and then asynchronously insert them into the current system. Specifically persist new transactions to a new database then build routines to insert the data from the new database into the current/old database.

I'm looking for best method to implement this solution... should I use a non-indexed table in the proposed new db for fast inserting, then use bulk inserting from newdb -> olddb to move the data back into the main database? Is this best done @ scheduled intervals or real time?

I was also wondering if scaling up/out would help here (budget permitting)? My initial thought was to recommend purchasing a second server and configuring a cluster to reduce the load.

To be honest I thought the web transactions would just timeout and not fail (but I guess that's ultimately the same)?

bbqchickenrobot
  • 153
  • 7

3 Answers3

2

CQRS is a good angle to follow for lots of things. But I'm not sure if it is right here.

What they are really proposing is creating a service bus of some sort. The general concept is the live transaction gets written to a persistent message queue which then gets processed and pushed to appropriate services. You might want to look at [presuming .NET b/c SQL Server tag]:

To start with. There are lots of options out there but the proposed model is solid. Lots of landmines in this space, rolling your own is definitely not recommended.

The other angle here is measurably and instrumentation. You can waste a lot of time guessing why things crashed. Knowing why things crashed is invaluable.

Community
  • 1
Wyatt Barnett
  • 20,685
  • 50
  • 69
  • this is basically a stretch on event sourcing? – jasonk Apr 20 '12 at 01:50
  • Nope; you can implement something like this using a lot of different patters. Event Sourcing happens to be a particularly good option. – Wyatt Barnett Apr 20 '12 at 02:07
  • just from a theoretical/conceptual standpoint... could all three (CQRS, Event Sourcing + Service Bus) be used or are they mutually exclusive? And yes, I will definitely learn why things are crashing - good point. Having first meeting tomorrow so I don't have that info just yet! – bbqchickenrobot Apr 20 '12 at 02:21
  • All three could play together. You could use all three independently. You could also have vodka with soda or tonic or straight up. – Wyatt Barnett Apr 20 '12 at 02:23
  • lol, right about now that vodka straight up sounds good ;) Thanks again! – bbqchickenrobot Apr 20 '12 at 02:24
  • Thanks wyatt! This is what the are initially looking for and I'm going to integrate this with a basic CQRS and Event Sourcing implementation. Didn't even come to mind. You and Jason both helped tremendously. – bbqchickenrobot Apr 20 '12 at 05:58
1

Yes, this kind of thing works, but is generally achieved using an in-memory database such as TimesTen, we did something like this for handing massive amounts of CDR inputs once upon a time. Have a look at the whitepapers on that site for some good advice in configuring such a setup, the same principles apply if you're using an "on-disk" DB instead of TimesTen.

You will still need a second server to run it on, just running 2 instances on a single server is pointless, you'd be better off configuring your existing setup than running 2 DBs on the same hardware. I doubt a cluster will help as it has a bit of overhead in reconciling the transactions written to your tables - but that depends on your workload, whether each transaction is completely idempotent or not.

Timeout can be considered a failure, but technically they are different things that you could handle in different ways.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
  • Oracle not in the picture at all - but maybe I can use ravendb. One thing that concerns me about an in memory database is if that memory loses power! These are ecommerce transactions so they can't afford to lose whatever is stored in memory. – bbqchickenrobot Apr 20 '12 at 00:42
1

A very similar solution to your proposed is "CQRS" and/or "event sourcing", you should read on both and pick the particular aspects which can apply to the right slice of your application - assuming this is the right answer.

As a simple overview, separating your OLTP and reporting database, and applying "reporting" as a much more broad term than previously. Although you might not think of "list all the products a customer has bought" as a report since it's a small transaction normally done on OLTP, under CQRS you are encouraged to consider this as reporting data.

Event sourcing encourages you to log transaction data and use that to compose your data for reporting/other purposes.

Re: clustering - it depends on your pain points. If your app architecture does not support scaling then configuring a cluster will bring minimal benefits, maybe 2x server capacity brings a 10% performance increase due to key bottlenecks in processing or data structure. (Amdahl's Law).

jasonk
  • 1,693
  • 1
  • 11
  • 9
  • Well, thank you for those two things - i def will/need to read up on those! My concern here is that a code re-write may NOT be in the plans. Again, I pretty much am being told the solution rather than being asked. Awesome huh? If they do allow me to pick, this is an option. If not, what do you think about the non-indexed table + occasional dumps to the old server? Will that relieve any stress perhaps? Or maybe you're right and only a code rewrite will help... thanks again! – bbqchickenrobot Apr 20 '12 at 00:40
  • cqrs / event sourcing doesn't mandate a change in code necessarily, at least not big changes ... cqrs / event sourcing is basically the name of what you are trying to do. read them, and you will at the least give you a good way to describe it, and on the way learn about pros and cons and things to watch out for. – jasonk Apr 20 '12 at 01:49
  • Ok, read up thoroughly. Had light knowledge of them all - but now I feel I'm ready to take this challenge on! Thanks for those, they didn't come to mind initially. But also, Wyatt is correct too. They want to protect against any transaction failure - first and foremost. SO, not sure who to give the answer to!?!? He's got 10k points and u dont... what do do... – bbqchickenrobot Apr 20 '12 at 05:56