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