This month Nigel Sammy (blog | twitter) is hosting this months T-SQL Tuesday and the chosen topic is What do you think is a useful feature of SQL Server 2012? Therefore, I am going to write about a topic that isn’t getting much pub in the streets like AlwaysOn Available Groups or Columnstore. But that’s okay because Contained Databases are sexy and they know it!
Contained Databases
A contained database is a database that is isolated from other databases and from the instance that hosts SQL Server. Why could this be helpful? Well lets think about logins. What happens when you migrate a database from one instance to another? You have to copy over the logins and unorphan the logins if they are orphaned. Wouldn’t it be nice to keep the logins contained in a database so they weren’t depended on logins created on the instance? I think so, especially for some third party applications that think they need sysadmin access. Now why would I be worried about databases moving between instances? This is where Always Avaiable groups come in. I know what your thinking, he just lied and is talking about Always Available groups. Yep, your right but here is my point. If the users are contained users for a database belonging to an Always Available group, during a failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This was a cool lesson learned quickly while building my virtual sandbox to test Always Available Groups.
Before you consider using contained databases you have see if your user databases are able to be a contained databases. If you are using replication, change data capture, or change tracking then contained databases may not be for you. As always, review Books Online for more details and make sure you test this out in development before using it in production.
Bonus – Get your Learn On!
Are you interested in learning more about SQL 2012? Do you wonder where you can find free resources? If so, the links I am using are included in this paragraph to help you brush up on the good stuff. You can download a free book, get your hands dirty with several virtual labs, view several videos and demos, Watch recorded sessions from #24HOP and attend the Microsoft Virtual Academy SQL 2012 training sessions.