SQL Server with Mr. Denny

Jun 14 2011   2:21AM GMT

Auto Close, Contained Databases and DDoS

Denny Cherry Denny Cherry Profile: Denny Cherry


One of the features which has been announced to be coming in SQL Server “Denali” is the “Contained Database” feature.  The feature which I’m looking for the most from Contained Databases is the ability to create a user within the database without having to first create a login for the user.  This will make database consolidation and migration projects so much simpler in the future as you won’t have to first create the user in the destination instance with the same SID, or risk having to resync up the logins and users using the sp_change_users_login system stored procedure.

However, there is a little catch with having a lot of contained databases, using contained authentication on a single server, especially if the auto close flag is enabled like it often is on hosting companies servers.  If you are using contained databases, and a user attempts to log into the contained database, but has the wrong password, the database must be opened the password checked, then the database closed.  If this was to begin happening to a large number of contained databases the SQL Server could end up crashing itself as it’s trying to open and close all these databases.  The reason that I see this happening on hosting company servers more than anywhere else, is because hosting companies put lots, and I mean lots, of databases on a single SQL Server instance.  If that server was exposed to the Internet (which they often are so their customers can log into the server via Management Studio) then this becomes an even bigger problem.

Basically what I’m trying to say here is if you have a lot of databases on the server, and you use the auto-close flag on the databases to keep databases that aren’t being used from taking any memory, you’ll need to change this practice before you start deploying contained databases on SQL Server “Denali” when it releases.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: