December 18, 2003 at 1:03 pm
We need to become a much larger organization. Our current DB is 7 GB and we are doing replication between main server and report server. We have approximately 35 customers we are servicing.
Hardware is maxed out 4 proc server with 4 GB of RAM.
We are considering breaking up our customers into each having their own version of our DB, or maybe 5 to 10 customers each being in their own version of our DB. Then when the server gets maxed out, we can migrate a customer to a new SQL Server in order to balance the load gradually.
This makes sense from a DB and customer management scenario to grow the company. My concern becomes the management of the transactional replication between prod and report server.
If we break the current DB into 10 copies of the same schema, each with a few customers in it, then we also need to do 10 differnt replications also. There are 300 tables on each DB.
It takes a certain amount of time to generate the snapshot and apply it to the report server. Rebuilding it could be a nightmare.
Questions:
1) In Transactional Replication, does reinitialization involve a new snapshot? How often are new snapshots done?
2) I see the potential for data explosion by making so many copies of our core DB. An empty version of our DB with no customer data will have 1 GB of data before any customer transactions start going thru it. So break that up and all of sudden we have 10 times more storage requirements. It could even grow much larger with 100 customers and 100 copies of the DB. All of that replication seems massive to me.
3) Managing so many replicated databases between production servers and report servers could be a nightmare logistically.
Can you think of another way to scale this system up without breaking our DB into so many copies?
December 18, 2003 at 1:36 pm
Obviously you report from your reports server. What kind of queries are the customers issuing? Mostly inserts and updates or a lot of selects?
If there are a lot of selects, one strategy is to set up a bunch of servers from which your customers can select and direct all deletes, inserts and updates to another server. The one server replicates to all of the others. Of course this involves some rewriting of the app so that selects go one place and deletes, inserts and updates go to another. It also requires a CSS network switch which can load balance your traffic to the various select servers. But if most of your load is from selects (such as you might have for an eCommerce site where people tend to browse), this solution is highly scalable.
December 18, 2003 at 2:21 pm
That is what I suggested. Offload the select queries and just use production for insert, update, delete queries. Then just replicate to multiple subscribers based on the amount of reporting that we are doing.
Management believes that we need to have seperate database for each customer and replication going all over the place.
Not sure how best to demonstrate otherwise. Any ideas?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply