SQL Server Contained Databases and Oracle Multitenant: Advantages ComparedApr 18, 2019 / Posted By:Robert Buda
Oracle introduced multi-tenant databases in version 12c. SQL Server answered with container databases in version 2012.
These capabilities add efficiencies and reduces deficiencies, but in different ways. Pre-multitenant Oracle required more administration activities for individual databases than SQL Server, but offered more isolation and control for each database. Pre-container SQL Server required less administration activities for individual databases than Oracle, but offered less isolation and control for each database.
Essentially, these new functionalities moved both Oracle and SQL Server to a more satisfying middle ground.
With the new changes:
- They both allow individual databases to stand alone and be ported from one environment to another more easily.
- They isolate much of the admin work that needs to be done on the Oracle Software level (patching, configuration, etc.) from the work that is more database-specific (backup/recovery, performance tuning, etc.).
- They isolate all of the metadata about a particular database within the database itself, as opposed to intermingling the metadata from multiple databases.
Each approach also touts some specific advantages. For example, Oracle Multitenant is said to be “designed for the cloud” because it simplifies database consolidation. A multitenant container database can hold multiple pluggable databases, and application code connects to specific pluggable databases. This makes it easier to consolidate multiple databases onto private clouds without changing their applications, yet still control resource prioritization as you had previously.
Another advantage of the Oracle Multitenant approach is easier provisioning and cloning. Moving and cloning databases is an everyday admin task that multitenancy can help accelerate. For example, you can quickly copy a database and “plug it in” to a different container. Filesystems that support copy on write allow you to clone pluggable databases almost instantly.
SQL Server’s contained database feature offers advantages in the area of user authentication. A contained database is isolated from other databases and from the SQL Server instance that hosts it. Support for user authentication at the database level for contained databases reduces a database’s dependency on the logins of the SQL Server instance. You can also have separate firewall rules for contained database users versus users who authenticate at the server level. This user model makes it much easier to move databases between systems, including Microsoft Azure.
In short, the main goal of multitenancy is to facilitate database consolidation, while the main purpose of contained databases is to enable isolation and thus portability. Yet each approach addresses both these concerns.
If you are considering using either or both of these models, or would benefit from guidance on how to use either or both more efficiently or effectively, contact Buda Consulting to schedule a free consultation.