Load test in mirrored DB

• yocki

  SSC-Addicted

  Points: 473

  More actions

  October 29, 2014 at 10:15 pm

  #312586

  Dear Experts,

  I was just inherited databases that are a part system as specified below:

  - 2 DB Servers SQL 2012 with mirroring (active - active, all synch'ed)

  Sometimes, due to business transactions, these servers have to withstand up-to 200 simultaneous and continuous transactions up-to 5K transactions during normal day before the system slowing down (during a busiest day its like 50K transactions). The database is still up and running though.

  The database is running very well if there are a few concurrent transactions..

  Think about a amusement park with 50 cashiers and each cashier has 100 people queuing. And each person can have 100 transactions (say each person can buy 100 tickets in 1 transaction). The system is cashles, so the people have to use bracelet / card with non-storing RFID.

  All the transactions hitting the same table (INSERT). each transaction has to be validated as well. The validation itself is pretty simple and only involves 1-3 tables. Some tables has triggers on it too.

  Now :

  1. How can i know the maximum number of simultaneous-continuous transactions on this mirrored DB ?

  2. How can i check the configuration of mirrored DB ?

  3. Whats is the best practice to handle concurrent transactions over period of time ?

  4. How can i check "whats going on in the database" during transaction ?

  5. What parameters are checked during the transaction ?

  6. How can i improve this situation ?

  Thank you so much for your sharing of knowledge and experience.