Page 228 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

Notes
Broker Hub front end had to be modified a bit in terms of process flow. In this case, each
user logging into Broker Hub had to be connected to a specific database based on his user
id. The task of identifying which database has the information related to the user trying to
log in was handled by a new module added to the application called DB Load balancer. The
process flow in this approach looked like this:
• User foo with User ID (n) logs in.
• Application calls Custom DB load balancer to find out that all data specific to user foo
is on database server DB 2.

• Application creates a connection to DB 2 on behalf of this user.
• All user transactions from User 1 are directed to DB 2 thereafter.

• Periodically user data is synchronized to the master database.
Design Decisions for This Approach
This approach was pretty simple to implement and could achieve the results we were looking
for. But there were a few practical issues:
• We have to introduce concept of ID buckets. So every transaction table on each server
was assigned unique bucket from which it could allocate IDs. With this we overcame
the problem of how to maintain unique IDs.
• All the masters would be replicated using transaction with update option. So a change
on one gets replicated all over.

• Adding new users was not as simple as before. Every database was configured for a
specific set of users and this set increased sequentially. This meant that we always had
to add the new user in the last server available and if last server was full then we had to
deploy a new database even for a single user (and probably even before the last server
was at its full capacity). This was accepted as an acceptable fact as this would happen
once a while.

• Any admin report had to combine the data from all the servers to be useful which meant
an additional job to aggregate all the data. This was accepted as a design reality.
Questions

1. Explain briefly broker hub.
2. What do you mean by DB Load balancer?

13.3 Summary

• A transaction is a sequence of operations performed as a single logical unit.

• A stored procedure is a set of SQL commands that has been compiled and stored on the
database server.
• Stored procedures improve performance by reducing network traffic and CPU load.

LOVELY PROFESSIONAL UNIVERSITY 223