My DB is SQL Server 2008 and I want to scale up the system by 5 fold (maybe more in the future).
The system currently has 95% read & 5% write from front end. And from backend actual data is bulk loaded using ETL.
I looked in to options like
- Scaling with shared database (using SAN)
- SODA (new caching mechanism)
- Clustering in SQL Server 2008 R2
but all either allows only read or duplicate the existing data.
Can someone point me in the right direction (replication of data would be my last but one option).
Thanks
JC
Thanks all for the suggestions.
Looks like i am left with increase the hardware and for better performance at this point of time.
SAN setup would become more administrative work because the updates from data load would happen atleast once a day and we need to bring down the servers on SAN every time. And there is also some updates happening from front end.
Other thing i am planning to do is to divide the data on to two servers based on regions, which will reduce the load on each server to almost half.
Best Answer
To take a different approach, what is driving your read workload - Is it the application, or do you have a bunch of reports hanging off the system that are driving the load? Depending on the nature of your workload you might be able to re-shuffle your hardware or push some of it off onto other machines.
A couple of ideas:
If you have a lot of read workload driven by reports (as opposed to application screens), consider moving those off onto a replicated server (more than one if necessary). More extremely, if you have the option of doing this, you could create a data mart specificually tuned for the reporting. Obviously this might depend on how up to date the data has to be.
It should be possible to do this without having to interfere in the application code, so it's not as an extreme approach as one might think. If you need it to be up to date you could do something with change data capture at the expense of extra complexity.
If your DB server is I/O bound but you have plenty of spare CPU capacity (or maybe you can upgrade the CPUs) you might get some mileage from placing some or all of your database on SSDs.
Without looking into the technical specifics of your problem it's hard to see what can actually be done with the workload.