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 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.