Databases as a Multitenant Provider 

 

The previous article described the multitenancy in databases using schema-based consolidation and within-database virtualization by partitioning the data dictionary horizontally. This article focuses on elastic pools, sharding patterns, row-level security and key management.

Elastic pools share compute resources between several databases on the same server. This helps to achieve performance elasticity of each database. The sharing of provisioned resources across databases reduced their unit costs. There are built-in protections against noisy neighbor problems.

Resource management in dense elastic pools is achieved by implementing resource governance.

Within a database there can be multiple resource pools and workload groups, with resource limits set at both pool and group levels. User workloads and internal workloads are classified into separate resource pools and workload groups. User workload on the primary and readable secondary replicas into a designated pool and partitioned workload groups for various internal workloads. Other pools and workload groups may be reserved for various internal workloads.

In addition, job objects may be used for process level resource governance and File Server Resource Manager may be reserved for storage quota management.

Resource governance is hierarchical in nature. From top to bottom, limits can be enforced at various levels using their level appropriate mechanisms starting with the operating systems, then the resource pools and the workload groups. Data I/O governance limits both the read and the write physical I/O against data files of a database. IOPS limits are set for each service level to minimize the noisy neighbor effect. This approach allows customers to use dense elastic pools to achieve adequate performance and major cost savings. The only shortcoming with this approach is that dense competition gives rise to significant resource contention which can impact internal processes. One of the following three mitigation actions can be chosen by the customers. First, the query workload can be tuned to reduce resource consumption. Second, the pool density can be reduced by moving some databases to another pool and 3. the pool can be scaled up to get more resources.

The Sharding pattern enables to scale the workloads across multiple databases. Tools provided by the databases support the management of shard maps which track the tenants assigned to each shard. They also initiate and track queries and management operations on multiple shards by using elastic jobs.

These jobs can be periodically executed against one or many databases to run queries and perform maintenance tasks. The scripts must be defined, maintained, and persisted across a group of databases

Row-level security is useful for enforcing tenant level isolation in sharded tables. Group memberships or execution contexts are used to control access to the rows in a database table. It simplifies the design and coding of security in the application and implements restrictions on data row access. This access restriction logic is based out of the database tier rather than one spanning the application tier. This system is made more reliable and robust by reducing the surface area of the security system.

End-to-end encryption of data at rest and in transit is achieved through encryption keys and separation of databases for each tenant and always enabling the always encrypted feature.

Storage and data approaches for multitenancy must consider scale, performance predictability, data isolation, complexity of implementation, complexity of management and operations, costs, patterns and anti-patterns and best practices.