Today's post is in regards a feature that was released with SQL Server 2012, it hasn't gotten the attention or used that it deserves even when it helps solving a lot of problems you face when upgrading or migrating the database servers and is Contained databases.
What is a Contained database?
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another, this is incredibly useful when you have HA environments. The Contained databases feature is available at the instance level and is not enabled by default.
What type are available?
Contained database feature provides two containment modes:
None – By default each database has its mode set as NONE. This means there is no contained database feature being used.
Partial – With partially contained databases, we can define boundaries between databases and the server, so the metadata will exist inside the databases. It makes SQL Server databases more portable and less dependent on underlying hosts.
Advantages of contained databases :-
1. User authentication can be done at database level, so you only need to be sure to grant the users permissions in your database.
2. Have less dependency on instance than 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.
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. This is the preferred mode to 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 permissions.
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, so make sure to enable it before releasing your server and database to production.
4. To connect to a contained database, you need to specify the database name in the default database option tab.
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, which can be a security issue.
Changes cross versions:
This feature hasn't gotten any change since it got released with SQL 2012, mostly as the new versions have been focused in improving the performance, security and integrating Artificial Intelligence and Machine learning features, however, personally if you have environments where you need High Availability and every time that you failover your groups you need to resynch your users and principals, this might work for you, however, you will need to remove you database from your availability group if you want to enable this in your database, but we will be working with it in next week's post.
Thanks for reading!
Resources:
