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)?