September 9, 2015 at 4:09 pm
Our development team wanted to create a database user for each application user in the application and use these for granular data access control, which at first, sounded like a good idea but our initial testing ran into some interesting results.
Our target user base was about 15 million users with an estimated 1% concurrency rate, and finding no MS documentation on an upper limit to the number of users a database can have we began some load testing to see how the database performed. In the hundreds of thousands of users range our test database had a hard time performing well under light loads (even without any concurrent connections). When we purged the users and reverted back to just a handful of service accounts, performance went back to "normal" under the same loads. I began to wonder if this is a situation where throwing more hardware at the problem would overcome the issue or if there is a practical upper limit to the number of users a single database can handle well.
(There were of course other cons to this arrangement and I certainly was never going to expand the users tree in the object explorer for a database like this, but we thought it a solution worth investigating.)
Anybody have any insight into this? What is the largest number of users any of you have had in a single database?
September 9, 2015 at 10:00 pm
Quick thought, the first hard limit is the maximum number of user connection is (2^15)-1 or 32,767, configurable, use SELECT @@MAX_CONNECTIONS to view the current. Memory consumption of the connection: each connection requires (3 x Packet Size) + 94KB, average 106Kb, for max connection => 3473302Kb => 3.3Gb.
The second obstacle is the user management, strongly suggest neither using sql server logins nor Win/AD for this if it's simply an access control, there are plenty of good examples out there for this.
😎
It is not too uncommon to have 1-2K users but when there are 100-1000 times more users one simply has to use an alternative mechanism.
September 10, 2015 at 12:43 am
That sounds like a nightmare to administer. Do you really have such a varying range of permissions that you'd need a DB account for every user?
In reality I suspect you'll need a handful of different accounts with different permissions and the application connects with the appropriate account depending upon who is using the application.
A separate database, or a connection limited to a 'permissions' table could ascertain the connection required for the user, so the main application could connect appropriately.
September 10, 2015 at 9:35 am
Those are all very good points. In fact we had worked out a system whereby there wouldn't be any logins for the database users, but rather just one for a service account which would in turn execute statements/stored procedures impersonating the user. While we scrapped the approach before we had the opportunity to fully test concurrency, this approached appeared to be substantially functional and we hoped this would get us around the 32767 limit on connections.
The big show stoppers were an excessive amount of waits, particularly the PREEMPTIVE_OS_LOOKUPACCOUNTSID wait, as well as an inability for management tools to handle that many users well—SSMS struggled, especially when intellisense touched anything related to permissions or users, as did Visual Studio Schema Compare, for example.
As everyone has more or less stated, the root of the problem is really the requirements we were given. The cookie a user would have had to carry with them would have been over 10MB in size if we put all the claims there which is why we tried keeping all the permissions in the database. The database method didn't really work from a maintenance/management perspective so either the requirements have to change or the developers will have to get very creative and roll their own authorization mechanism (which nobody really thinks is a good idea).
Interestingly, everything regarding the database that wasn't related to security still worked great.
I appreciate everyone's input on the matter. Hopefully this situation never becomes more than a theoretical exercise for anyone.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply