Contained databases are the new feature in SQL Server 2012 and are defined on BOL (http://technet.microsoft.com/en-us/library/ff929071.aspx) as below :
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2012 helps user to isolate their database from the instance in 4 ways.
· Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
· All metadata are defined using the same collation.
· User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
· The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.
The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.
Containment setting :-
SQL Server has 2 types of objects or features one that can be contained like user objects, indexes and second that cannot contained, which depends on instance Like login details etc.
1. None : Database settings when all objects inside or outside database boundaries managed by SQL instance.
2. Partial : New feature that comes with SQL Server 2012, where some possible objects managed by databases & other uncontained objects by SQL instances. You can use DMV sys.dm_db_uncontained_entities to check uncontained objects or features of your database.
3. Full : Full containment setting is a condition when database can handle everything by itself. When database not required SQL instance. This feature is not feasible for SQL server till yet.
Advantages of contained databases :-
1. User authentication can be done at database level
2. Contained databases have less dependency on instance then conventional databases. Objects & features of each database can be managed by them self, reduce workload of system database & SQL instance
3. Easier & Faster to migrate databases from one server to another. Errors related to missing users and orphan users are no longer an issue with contained databases
4. Contained database users can be Windows and SQL Server authentication users
5. Contained database user can access only contained database objects. They cannot access system databases and cannot access server objects
6. Bestto be used with HADR (Always On)
7. Maintaining database settings in the database, instead of in the master database increase security & flexibility. Each database owner have more control over their database, without giving the database owner sysadmin permission
8. To close collation issues in contained database. New feature catalog collation introduced with contained database. Now database collation works for user objects & catalog collation works for system objects in database. Catalog collation will be same for all contained databases on all SQL instance, also this collation cannot be changed.
Disadvantages and Limitations of contained databases :-
1. A database owner has more control on contained database, User can create contained database users without the permission of a DBA that can lead to security issues & data theft threat
2. Contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes
3. Before changing containment settings at database level from NONE to PARTIAL , contained databases feature needs to be enabled at instance level
4. To connect to contained database, you need to specify database name in default database option
5. Temporary stored procedures are currently permitted. But can be removed from future versions of contained database
6. Contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account
Issues Faced :-
1) SQL Server instance is running on windows mode and you are using SQL User with password to connect with contained database. You must enabled mixed mode authentication for this
2) Contained database feature is not enabled on SQL Server instance where you have moved your contained database
3) Known Issue(http://support.microsoft.com/kb/2894326) : An application cannot connect to a contained database when connection pooling is enabled in SQL Server 2012 or SQL Server 2014
4) Default database not mention at the time of connecting contained database
Steps to try Contained database :-
1) Enable “contained database authentication”
· By Script
sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE
GO
· By GUI
o Go to Server Property
o Select Option from False to True
Note : This GUI option will not be available, If you are using SSMS older than SQL Server 2012.
2) Create partially contained database
· By Script
USE master
GO
CREATE DATABASE[ContainedDB]
CONTAINMENT=PARTIAL
GO
· By GUI
o You can select Containment type during creation of new database from option page
o You can change Containment type of existing database from option page
3) Create the SQL user with password in contained database
· By Script
USE [ContainedDB]
GO
CREATE USER [ContainedUser] WITH PASSWORD=N’Password@123′
GO
Exec sp_addrolemember ‘db_owner’, ‘ContainedUser’
GO
· By GUI
4) Create the Widows user in contained database
· By Script
USE [ContainedDB]
GO
CREATE USER [ROHITGARGUser1]
GO
· By GUI
5) Migrate existing user to contained
–Create Login
USE [master]
GO
CREATE LOGIN [MigrateUser] WITH PASSWORD=N’Pass@123′
GO
–Create User Mapped with Login
USE [ContainedDB]
GO
CREATE USER [MigrateUser] FOR LOGIN [MigrateUser]
GO
–User created & mapped with login
–User migrated to contained user
USE [ContainedDB]
GO
EXECUTE sp_migrate_user_to_contained
@username =N’MigrateUser’,
@rename = N’keep_name’,
@disablelogin = N’disable_login';
GO
–User changed from SQL User with password from SQL User with Login
–As a result login disabled on SQL instance
6) Connect to Contained Database
· Enter User ID & Password
o SQL User
o Windows User
· Mention Default Database to Connect
· Connection Established
o By SQL User
o By Windows User
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
