Inside Data 61
by Graham Keitch
Graham Keitch explains how multi-tenancy support works for Microsoft and Oracle databases.
HardCopy Issue: 61 | Published: November 1, 2013
The management of a single database can be demanding at times, but it’s nothing compared to a system that involves multiple databases, as is the norm today. Throw virtualisation and other resource sharing requirements such as clustering into the mix, and the management issues become even more complex.
The complexity is compounded because each database relies on a number of other processes being run at the same time. In the case of Oracle, these include memory handling components such as the System Global Area (SGA) and other background processes collectively known as an ’instance’. Virtualisation is only a partial solution in the quest for simplification and consolidation because each database requires its own set of background processes, and these accumulate to degrade performance.
It would therefore help if a single database instance could support numerous individual databases. Instead of having to manage the finance, HR, sales and warehouse systems as separate entities, it would be a lot easier if these could be ‘tenants’ of a single instance. Consolidation simplifies management and makes the data easier to aggregate and mine too. A single database serving an extended community of users and applications must be able to address the security, access and performance requirements of each of its tenants. It’s analogous to a landlord renting out rooms in a house to a number of tenants, each of whom has a key to their room.
If the concept of a single database supporting multiple tenants sounds familiar it’s probably because that’s the nature of most cloud implementations, including Microsoft Windows Azure SQL Database. This uses SQL Federations to partition tables horizontally, allowing a large database to be segmented. The process is known as sharding and can be used to redistribute the workload and create a multi-tenant environment. Partitions are members of a Federation that are physically implemented as databases and logically referenced at the application layer.
Microsoft SQL Server technology, from which SQL Database is derived, also allows you to create federated database servers and hence a multi-tenant environment using Distributed Partitioned Views. The Resource Governor introduced with SQL Server 2008 Enterprise isolates resources for database instances serving multiple clients, and is a valuable tool for this type of architecture.
The Oracle solution
As cloud technologies and their benefits become more widely accepted, an increasing number of IT departments are looking to adopt similar approaches in-house. Hybrid and private clouds are now well established, and it is likely demand will grow for multi-tenant on-premise database solutions too. The recent release of Oracle Database 12c makes this a reality.
It has always been possible to build a monolithic company-wide database, consolidating many schemas into one, but this approach requires tenancy controls to be built into the application layer. This increases development and maintenance complexity, as well as carrying other risks. With Oracle 12c, tenancy control lies in the database tier which makes it a lot easier to apply patches and carry out upgrades and backups. Developers and testers benefit as the new architecture makes it easier to provision and clone databases. Multi-tenancy support is also useful for Independent Software Vendors who want to deliver their software as a service (SaaS).
Oracle describes its multi-tenant architecture as a ‘super’ container database (CDB) into which you insert multiple pluggable databases (PDB). This was one of the key features introduced in July this year with their latest Database 12c Release 1. The architecture is available across all editions, although Standard Edition One and Standard Edition are restricted to a single pluggable tenant only. Full blown multi-tenancy support is provided by Oracle Multitenant, an additional cost option for the Enterprise Edition which hosts up to 252 PDBs. A Resource Manager handles competition for resources across each of these.
As all editions of Oracle Database 12c are built on the same code-base, developers can scale their applications without having to rework code. Similarly, Oracle guarantees that earlier releases such as 11g are fully compatible with the new architecture. They can be upgraded to ‘appear’ as a 12c non-CDB, or as a PDB that will run within a CDB.
It is worth adopting the new architecture even if you are only able to deploy a single tenant. The new architecture makes it easier to upgrade and clone databases as well as separate out the admin duties that relate to the container and to the PDB itself. The Multitenant option complements other Oracle Database Enterprise options such as Real Application Clusters and Active Data Guard.
Oracle Enterprise Manager 12c R3 was also released in July 2013. This is the management tool for Oracle environments and it too is now heavily focused on supporting a unified platform for both in-house and cloud-based deployments.