Version 2.0

25-Sep-18

Prepared by

<Partner> Services

1

Table of Contents

Executive Summary

1Solution Overview

1.1Multi-Tenancy Models

1.2Architecture Design Strategy

2Multi-Tenant SQL Server Infrastructure Design

2.1SQL Server Infrastructure

2.1.1Additional Hardware Considerations

2.1.2Hyper-V Replica Considerations

2.2SQL Server Virtual Machine Design

2.2.1SQL Virtual Machine CPU Considerations

2.2.2SQL Virtual Machine Memory Considerations

2.2.3SQL Virtual Machine Storage Considerations

2.2.4SQL Virtual Machine Networking Considerations

2.2.5SQL Virtual Machine Affinity Considerations

2.2.6Additional SQL Virtual Machine Considerations

3Multi-Tenant SQL Server Security Design

3.1.1Surface Area Reduction

3.1.2Policy-Based Management

3.1.3Service Account Selection and Management

3.1.4Patching and Automatic Windows Update

3.2Encryption

3.2.1Data and Database Encryption

3.2.2SSL Encryption

3.3Access Control

3.3.1Administrator Privileges

3.3.2User-Defined Server Roles

3.3.3Database Ownership and Trust

3.3.4Lockdown of System Stored Procedures

3.4Authentication

3.4.1Authentication Modes and Logins

3.4.2Contained Databases and Authentication

3.5Network Security

3.6Auditing

4Multi-Tenant SQL Server High Availability and Disaster Recovery Design

4.1Network Configuration

4.2Quorum Configuration

4.3SQL Server System Data and Availability Groups

4.3.1Jobs, Alerts, Email Operators

4.3.2Backups

5Shared Database Multi-Tenancy Model Design Considerations

5.1Resource Governor

5.1.1Resource Pools, Workload Groups, and Classification

5.2Database Storage

6Multi-Tenant SQL Server Service Management and Automation

6.1Operations Manager

6.1.1SQL Server 2014 Management Pack

6.2Orchestrator

6.3Windows Azure Pack

6.3.1Service Management Automation

71

Executive Summary

Microsoft SQL Server is an enterprise class relational database management platform and is an integral and indispensable component in most computing environment today with a significant application ecosystem. With the advent of hosted cloud computing and storage, the opportunity to offer a Microsoft SQL Server as an outsourced service is gaining momentum. This reference architecture is focused on providing sample design guidance and configuration considerations for helping service providers to build a multi-tenant Microsoft SQL Server 2014 platform using Microsoft’s virtualization and management platform.

This pattern and practiceprovides a consistent architecturefor Microsoft SQL Server 2014. This document was written to make the result a collection of both solution and product level best practices. Furthermore, this provides guidance for deploying Microsoft SQL Server 2014using Microsoft’s software defined datacenterpatterns and practices built on the Windows Server Hyper-V and System Center platform.

The primary goal is to familiarize decision-makers and IT professionals of service providers with an example of how to build amulti-tenant Microsoft SQL Server 2014 platformfor their organization. The design presented here could be used as is, or could serve as a starting point from which to drive deeper discussions about the business models, service descriptions, technology, and design decisions. This architecture builds on the guidance and configuration considerations of the Service Provider Reference Architecture Multi-Tenant – Foundation which provides the design of a Multi-Tenant infrastructure that includes Microsoft identity solutions as well as Microsoft’s virtualization and management platform.

1Solution Overview

Thissolution takes advantage of the full range of in-product features forMicrosoft SQL Server 2014. This example design is hosted on a Windows Server software-defined infrastructure, which aims to significantly reduce costs through the use of commodity storage and high-speed, low latency network connectivity.

Multi-Tenant SQL for Service Providers has been specifically designed with service providers in mind to help service providers to deliver database functionality as a service to their customers. Multi-Tenant SQL service support the following necessary capabilities:

  • Consumer-based provisioning and management of database instances using on-demand, self-service mechanisms;
  • Automated monitoring of and compliance with provider-defined service definitions, attributes and quality of service levels;
  • Fine-grained metering of database usage

In addition to these required characteristics, Multi-Tenant SQL also supports granular service elasticity, secure multi-tenancy, automated resource management, and integrated capacity planning.

1.1Multi-Tenancy Models

Microsoft SQL Server supports several isolation models, such as dedicated SQL Server running on dedicated machines (physical or virtual), dedicated SQL Server instance, or dedicated SQL Server database depends on what features and level of flexibility service providers like to offer. Multi-Tenant SQL service will focus on virtualized scenarios to leverage the benefits of infrastructure as a service. The following three multi-tenancy models will be covered:

  • Virtual Machine Level Isolation: Each tenant database instance will be isolated with Virtual Machine for better feature flexibility and scalability. Virtual machine will be the container for controlling the resources allocation and scalability. Microsoft Hyper-V provides quality of service for various resources, such as CPU, Memory, Network and Storage IOPS.
  • SQL Server Instance Level Isolation: Each tenant database instance will be isolated with a dedicated SQL Server instance. Multiple SQL Server instances will be hosted on a given virtual machine. SQL Server instance will be the container for controlling the resources allocation, and SQL Server feature related configurations, such as SQL Collation, and patch level, etc. Even instance level isolation provides good security level isolation and feature level isolation, but it has limited options to isolate resources allocation, such as CPU allocation at instance level, and it is also limited to scale, running multiple SQL instances per virtual machine. Therefore, this multi-tenant model is not recommended and will not be focused.
  • Database Level Isolation: Each tenant database instance will be isolated as contained database running on a shared SQL instance. Resource governor will be leveraged for better resource guarantee and containment for workloads at resource pool level. It is possible to assign tenant database to its own resource pool to achieve database level resource containment. Microsoft SQL 2014 resource governor allows limiting CPU, Memory and IOPS at resource pool level.

The following table illustrates benefits and constraints of each model:

Multi-Tenant Model / Benefits / Constraints
Database / •Optimizes the overhead of resources (memory, CPU, disk space, connections, and more) that are required for each individual instance
•Maximizes value of licenses, since no additional license is required for multiple database instances
•Minimizes the labor and cost of database administration—the fewer individual instances, the easier the system is to monitor and manage / •A common security model (although distinct database and database objects within the model can be secured individually)
•A common service patch level
•The maintenance cycle and downtime
•Platform resources (CPU, Memory, Disk I/O, and more). Resource Governor could help to constraint the resources consumption.
Instance / •The data must be very carefully secured.
•The database needs to run at a different service patch level than the other databases.
•The application that depends on the database cannot share a maintenance schedule with other databases, either because the database will require the instance to be started and stopped repeatedly or because the application is too critical to be stopped by the requirements of another application. / •Each instance requires additional resources from the server, including CPU, memory, and disk I/O.
•Memory pools and connections cannot be shared between instances. More resources will be required on the server
•Separate instances require their own logins so additional management efforts will be required.
•Distinct instances require distinct monitoring and administration, which increase cost.
Virtual Machine / •Server maintenance affects only a single database application.
•The application may require direct access to the server through external stored procedures. This can open the door to certain security risks. By placing the server on its own server, the risk can be mitigated.
•The database can have a unique, isolated security model.
•The database can exercise full control of the resources available on the server. / •Each machine will require a Windows Server license, SQL Server license, and other licenses for antivirus software, backup, monitoring, and more.
•Each additional server requires more resources. If it is a physical machine, the hardware must be purchased, space must be allocated in the data center, and power consumption must be considered. In the case of virtual machines, there is additional load on the host hardware which eventually leads to the need of additional virtual machine host servers.
•Additional servers require additional labor to monitor and maintain the servers.

1.2Architecture Design Strategy

This Reference Architectureis designed to help service providersdeliver a multi-tenant SQL Server environment focusing on Virtual Machine and Database multi-tenancy models described above on a common Infrastructure as a Service platform.

Multi-Tenant SQL is designed to help meet the need for robust security, 24/7 reliability, and user productivity. Multi-Tenant SQL is designed for reliability, availability, and performance with a targeted Service Level Agreement (SLA) of 99.9% scheduled uptime.

A foundational set of design principles has been employed to make sure that the Multi-Tenant SQLdeployment is optimized to deliver a robust set of capabilities and functionalities. The following design principles are the basis for the Multi-Tenant SQL:

  • Efficient multi-tenancy: The goal is to minimize the size of the infrastructure footprint required, while meeting application-level query performance goals.
  • Elastic scalability: Multi-Tenant SQL supports database and workloads of different sizes. Tenant will be able to provision and deprovision necessary resources to support their database and workloads.
  • High availability and Business continuity: Multi-Tenant SQL is designed to deliver 99.9% scheduled uptime. Redundant network architecture is part of the geographically dispersed design to handle unscheduled service outages. Data centers act as backups for each other: If one fails, the affected users are transferred to another data center with limited interruption of service.
  • On-Demand and Self-Service Administration: With a management portal, tenants and administrators can perform tasks associated with SQL. For example, tasks such as creating databases, creating database users, resetting passwords, and backup/restore databases, etc.

Although two multi-tenancy models: virtual machine based and database based have different characteristics, but they do share a common set of SQL Server design principals and considerations. This document will describe those common architectural elements for supporting those two different multi-tenancy models, and also provide special considerations focusing on the differences among those two multi-tenancy models.

2Multi-Tenant SQL Server Infrastructure Design

Multi-Tenant SQL Server Infrastructure will follow the common Infrastructure as a Service framework detailed in the Service Provider Reference Architecture Multi-Tenant – Foundation document. This multi-tenant SQL Server reference architecture will only focus on virtualized SQL Server workload residing on the common Infrastructure provided by the Infrastructure as a Service (IaaS) platform. The following diagram illustrates a typical physical design of such an Infrastructure.

2.1SQL Server Infrastructure

This section looks at the considerations for hosting of SQL Server on virtual machines that are providing Infrastructure as a Service (IaaS).

2.1.1Additional Hardware Considerations

In addition to the common Infrastructure as a Service framework described in the previously referenced document, service providers should also consider use processors that support Second Level Address Translation (SLAT) technologies (that is, SLAT-based processors). SLAT technologies add a second level of paging functionality under the paging tables of x86/x64 processors. They provide an indirection layer that maps virtual machine memory addresses to physical memory addresses, which reduces load on the hypervisor for address translation.

SLAT technologies also help to reduce CPU and memory overhead, thereby allowing more virtual machines to be run concurrently on a single Hyper-V machine. The Intel SLAT technology is known as Extended Page Tables (EPT); the AMD SLAT technology is known as Rapid Virtualization Indexing (RVI), formerly Nested Paging Tables (NPT).

For optimal performance of demanding workloads like SQL Server, run Windows Server 2012 R2 Hyper-V on SLAT-capable processors/hardware. This offers the additional benefits of improved performance, more virtual machine density per host machine, and reduced overhead.

2.1.2Hyper-V Replica Considerations

For maintaining high availability of mission-critical applications, organizations can replicate their applications on different server workloads in different locations. Hyper-V with Windows Server 2012 R2 provides Hyper-V Replica for replicating virtual machines from one location to another location.

Key SQL Server considerations on Hyper-V Replica include:

  • SQL Server on Hyper-V Replica is supported as long as the flag for EnableWriteOrderPreservationAcrossDisks is set.
  • If multiple SQL Server virtual machines are tightly coupled with one another, individual virtual machines can fail over to the disaster recovery site, but SQL Server high availability features need to be removed and reconfigured. For this reason, the following SQL Server features are not supported on Hyper-V Replica: availability groups, database mirroring, failover cluster instances, log shipping, and replication.

2.2SQL Server Virtual Machine Design

In addition to configuring and establishing the host server as a virtualization server with Hyper-V, it is important to design detailed architecture and system specifications for building virtual machines for expected workloads. It is also necessary to plan for needed resources for the virtual machines. The number of virtual machines you can run on any individual server depends on the server’s hardware configuration and the anticipated workloads.

2.2.1SQL Virtual Machine CPU Considerations

In Windows Server 2012 R2, NUMA is now extended to the virtual environment (virtual NUMA) by making virtual NUMA topology available to the guest operating systems. High-performance applications such as SQL Server support NUMA and use the computer’s NUMA topology to increase performance by considering NUMA when scheduling threads or allocating memory. Therefore, reflecting the underlying NUMA topology into virtual machines running SQL Server 2014 reduces the need for remote memory access, which is critical to improving SQL Server 2014 performance.

Identify and categorize virtual machines based on the intensity of the loads they bear (high-intensity loads and low-intensity loads). Then set weights and reserves on the virtual processors accordingly. In this way, you can ensure that a large amount of the CPU cycle is available for virtual machines/virtual processors having high-intensity loads.

Whenever possible, avoid the use of emulated devices for SQL Server deployments as this can lead to significant CPU overhead. Install the latest virtual machine Integration Services in each supported guest virtual machine. Virtual machine Integration Services helps to improve I/O throughput and decrease overall CPU usage of guests. This is because it includes enlightened drivers for Hyper-V-specific I/O devices that reduce CPU overhead for I/O.

2.2.2SQL Virtual Machine Memory Considerations

For memory in a virtualized environment, better performance and enhanced support are essential considerations. You must be able to both quickly allocate memory to virtual machines depending on their requirements (peak and off-peak loads) and ensure that the memory is not wasted.

Allocate a reasonable amount of memory to the virtual machines running SQL Server workloads so that they can handle the expected loads (at peak and off-peak times). If the memory is not sufficient, it can increase response time or I/O usage for highly intensive workloads. Allocate at least 512 MB of memory for the virtual machine running Windows Server 2012 R2.

Be sure to check the minimum memory requirement of the SQL Server workload that will be hosted on a Windows Server 2012 R2 guest machine and, based on that, allocate the total minimum memory (which is likely to be much higher than 512 MB). Dynamic Memory can be enabled to manage the memory requirements dynamically.

There is a limit to the amount of physical memory with which a physical server can be equipped. Therefore, prioritize memory usage based on the requirements of the running virtual machines or the importance of SQL Server workloads, such as OLTP, DW, and BI. Assign memory weights to high-priority virtual machines/workloads so that in the event of a physical memory crunch, memory is first allocated to the higher priority virtual machines.

In general, it is better to leave max server memory at its default setting if using dynamic memory. This allows SQL Server to manage memory dynamically.

To provide better stability to a virtual machine workload, grant Lock Pages in Memory user rights to the SQL Server service account. This helps when Hyper-V Dynamic Memory is trying to reduce the virtual machine’s memory. In such cases, it prevents Windows from paging out a large amount of buffer pool memory from the process, thereby providing a positive performance impact.

If memory pre-allocation is used with SQL Server, it is better to assign only the amount of memory that is required so sufficient memory remains for other processes (thereby avoiding paging). This also ensures that enough memory is available for Analysis Services. (Use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).

Do not set the memory pre-allocation value too high. Otherwise, other processes may not get sufficient memory at the time when they require it, and this can result in memory paging. If the SQL Server relational engine and Analysis Services are running on the same server, also limit the maximum memory that can be used by the SQL Server relational engine. It is generally sufficient to leave 6-8 GB of memory for the operating system if no other services are running on the same server. However, this can vary depending on workload requirements. When setting the maximum memory for SQL server, consider using the following formula:

Total_Mem - (1% of mem x #_of_NUMA_node) – ( 3% of mem ) – ( 1GB )

By default, a virtual machine gets its preferred NUMA node every time it runs. In due course, an imbalance in the assignment of NUMA nodes to the virtual machines may occur. This may happen because of the ad hoc memory requirements of each virtual machine and because the virtual machines can be started in any order. Therefore, we recommend that you use Perfmon to check the NUMA node preference settings for each running virtual machine. This can be checked through the \Hyper-V VM Vid Partition (*)\ NumaNodeIndex counter.