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:
Thanks a lot for sharing such a good source with all, i appreciate your efforts taken for the same. I found this worth sharing and must share this with all.
ReplyDeleteDigital Marketing Training in Chennai
Digital Marketing Course in Chennai