October 15, 2012 at 7:39 am
Hi all,
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
Cheers
October 15, 2012 at 7:43 am
what about windows group access if it requires only same level of access..
Regards
Durai Nagarajan
October 15, 2012 at 7:47 am
Thanks for your response but i am not worried about giving access through AD group.
My question is how much CPU, Disk etc is required so that users can access the database without any bottlenecks
October 15, 2012 at 7:52 am
What is the server meant for ? Reporting or transactions? What is the budget ?
October 15, 2012 at 7:58 am
but i am not worried about giving access through AD group.
what is the thing you are worried about?
Regards
Durai Nagarajan
October 15, 2012 at 7:59 am
This is a archived database and data is viewed via a managed solution. Budget is not an issue
October 16, 2012 at 5:44 am
..
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2012 at 5:45 am
Sqlsavy (10/15/2012)
How to plan the resources for such a massive requirement?
there is not any hard and fast rule for hardware upgradation , it depends on variuos factors like
no of concurrent users (in future)
Disaster recovery plan
data growth
how will you handle reports (same or different server ) in future
disk management
AND how much rich or complex application would be in future from resource comsumption point of view
etc etc
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2012 at 6:31 am
Thanks!
October 16, 2012 at 7:17 am
Sqlsavy (10/15/2012)
Hi all,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
Cheers
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. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 16, 2012 at 7:23 am
can we know what are you planning to do in terms
1. Are you talking about application on DB users.
2. will the users use a common DB user or individual?
3. what about reports access individually or common.
4. will the users insert or update any data or just reports?
as the user suggested above sql server will not support that huge no on concurrent connections, keep in mind
Regards
Durai Nagarajan
October 16, 2012 at 9:33 am
Thanks Kevin.
I ‘d certainly update you on the progress of this project we might end up having MS DSE assisting us .
Here is one more requirement from the Project which is bit tricky.
The existing database Stored Procedure hit rate during peak hours is 20,000 .
New Mobile application will be introduced on the same database and Procedure hit might increase to 40,000 and 50,000 /hour form 20,000.
Would you be able to give any advise on what will be the impact on Database/Memory/CPU?
October 16, 2012 at 9:38 pm
There are few DMV's thru which we can estimate. Also, if you have Idera's SQL diagnostic manager you have the facility to estimate database growth, resources.
October 16, 2012 at 9:39 pm
We can estimate the resources using DMV's, also if you have access to Idera's SQL diagnostic manager v7.5 you can generate reports on expected database growth, expected resources.
October 16, 2012 at 11:59 pm
Sqlsavy (10/16/2012)
Would you be able to give any advise on what will be the impact on Database/Memory/CPU?
its purely depends on what you have encapsulated in that SP.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply