We've a small database (1 GB) and SQL 2005 server is clustered one. Now Business is forecasting that database will be accessed by million users by early next year.
Currently there are only 300 users using the database .
How to plan the resources for such a massive requirement?
Any help would be appreciated
Thanks in advance
There are MANY factors that come into play when trying to get SQL Server to the extremes of scale. And they aren't all inside SQL Server or the Windows server itself either. Application connection pooling, network latency/bandwidth are two that come to mind outside the server. Networking stack configuration on the server will be key. Inside SQL, wow - the list of things that could be a bottleneck or may need to be tweaked is long, everything from latching issues to minimum memory per query.
You REALLY need to get an expert consultant involved sooner rather than later. You need a VERY VERY THOROUGH scalability testing plan, because I GUARANTEE you that you will run into things that will need to be dealt with. For example, I think you will need to avoid having a different login per user too - the login validation and security/access overhead would be overwhelming I think.
Honestly my gut says SQL Server cannot do 1M concurrent users on access regardless of hardware capabilities. I imagine you will need to have a scale-out approach if you need any reasonable fraction of those 1M users connected at the same time. Of course there is (currently) no version of SQL Server that can do this out of the box, so you will need to be creative.
In any case, best of luck with it, and please keep us apprised of your activities and how things go. I have helped some clients over the years get to extreme scale on SQL Server (7400+ databases on one server for example), and it is always interesting!! BTW, I would LOVE to help you try to get this to work.
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail