SQL Server 2012 Contained Database Agenda What does

SQL Server 2012 Contained Database

Agenda § § § § What does word contained mean? What is contained database? Types of contained database How to set up and create contained database Contained database authentication and security best practices Contained database collation Contained database modified features How to migrate partially contained database Benefits of contained database Limitations of contained database Future contained database Reference sources Q and A

What does word contained mean? Dictionary definition: § § To To hold or include within its volume or area be capable of holding; have capacity for keep under proper control; restrain prevent or limit the advance or spread

What is contained database? �A contained database is isolated from other databases and SQL server instance. In fact it lives in its own world. It is self contained. � SQL Server 2012 introduces the notion of boundaries thru contained database that define where a database, its metadata, and settings end and where server-level features, metadata, and dependencies begin. � Metadata maintained in database � All metadata defined using same collation � User authentication possible by database rather than depending on server instance level

Types of contained database? SQL Server Books Online hints 3 types: § § § None – by default uncontained, no boundaries defined or visible Partial – supported currently, clearly defines boundaries between database and server Full – currently not available as an option, will allow greater database portability, better isolation from other databases and underlying hosts

How to set up and create contained database demo? � Enable the Contained Database feature at the server level either from the user interface or from a script � Create database enabling partial containment � Create user � Connect to database

Contained database authentication and security best practices � Contained database’s new authentication feature to create a user without login � Authentication directly against database and successful authentication grants access to that database only � Guest account enabled - contained database user can access other databases on the server � Duplicate login � Attaching contained database-reset all pwd to avoid weak pwd � Use Windows authentication

Contained database collation Demo � Two types of collation-Database default and catalog default � New catalog collation Latin 1_General_100_CI_AS_WS_KS and cannot be changed � Tempdb uses collation of the contained database � Catalog collation � Crossing between contained and noncontained database

Contained database modified features § § § ALTER Database Application level Instance level CREATE Database Temporary tables-local, collation of contained db used not tempdb Collation - db collation and catalog collation User options set to 0

§How to migrate partially contained database § § Prepare checklist to migrate Partially contained database model Risks Features that can not be used List of database features Query sys. dm_db_uncontained_entities Enable contained database Convert database to partially contained DB Migrate users to contained database users

Benefits of contained database? § § § Easy to move databases, portable users Helps with different collations Useful during failover and disaster recovery Initial database development Ease of database administration Two DMVs to identify uncontained entities that may potentially break if database moved to another server sys. dm_db_uncontained_entities and database_uncontained_usage event

Limitations of contained database? § § Can not use replication, change data capture, change tracking Numbered procedures(because these are deprecated) Schema-bound objects that depend on builtin functions with collation changes Binding changes resulting from collation changes, including references to objects, columns, symbols, or types

Future contained database wish list § § Mere "workloads" that can be moved from one SQL Server host to another Move from one SQL Server instance to another to handle load-balancing requirements and ensure continuity like virtualized environment Cloud where isolated from the host Fully contained

Reference /sources � SQL Server 2012 Books Online � http: //msdn. microsoft. com/enus/library/ff 929139. aspx � http: //www. sqlmag. com/content 1/topic/sqlserver-2012 -data-security 142461/catpath/sqlserverdenali/page/2 � http: //blogs. lessthandot. com/index. php/Dat a. Mgmt/DBProgramming/MSSQLServer/contai ned-databases-temptables-and-their