Enabling Database as a Service with the Microsoft Private Cloud stack
A Building Clouds Blog eBook | Enterprise Cloud Group CAT Team
Author:Bruno Saille (Microsoft)
Published: May 2015, Version 1.1
Abstract
This paper is for IT Professionals and Database Administrators who would like to enable “Database as a Service” (DBaaS) for their end users. Providing DBaaS to applications owners or developersis a topic that comes up quite often these days in our interactions with customers. The Microsoft Private Cloud stack offers several ways to provide DBaaS. This document summarizes the options, and which requirements may lead you to prefer one option compared to the others. While this will mainly be in the context of Microsoft SQLServer, we will also cover options to achieve DBaaS for other types of database software.
This paper applies to WindowsServer2012R2, Microsoft SystemCenter2012R2, and Microsoft WindowsAzurePack for WindowsServer.
© 2015 Microsoft Corporation. All rights reserved. This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.
Some examples are for illustration only and are fictitious. No real association is intended or inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes.
Some information relates to pre-released product which may be substantially modified before it’s commercially released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Table of Contents
Introduction: First, let’s define “Database as a Service”
Delivering DBaaS scenarios through System Center and the Windows Azure Pack
Preparing the VM Clouds fabric
Templates for virtual machines with SQL Server
Preparing the SQL Servers fabric
Enabling DBaaS for tenants, through plans and subscriptions
Looking at the tenant experience
Plan subscription
Creating a virtual machine with SQL Server installed
Creating a database
Some requirements that make Windows Azure Pack a good fit for DBaaS
Frequently Asked Questions about Database as a Service in the context of Windows Azure Pack
Do I need to use both the VM Clouds and the SQL Server resource providers?
What if I like the shared approach, but want to provide more dedicated servers to my tenants?
How is the location of new databases determined in the shared model?
How can I can provide additional value-added services for my tenants?
Can I pre-provision resources for my tenants when they subscribe to my plans?
Using Windows Azure Pack, can I also enable Database as a Service for other types of database software?
My processes are largely based on ITIL today. How can I combine ITIL with Database as a Service?
Can I use the APIs to interact with the databases, as an administrator or a tenant?
How can I ensure that my tenants get their fair share of performance when using the shared model with the SQL Server resource provider?
When using IaaS to deploy SQLServer virtual machines, could my tenants also deploy highly-available virtual machines?
Can I get data for potential chargeback with my tenants?
Other options with System Center
Looking beyond Database as a Service: Why leverage Microsoft technologies to virtualize and manage SQL Server
References
Introduction: First, let’s define “Database as a Service”
Everyone has a different definition based on their requirements, but ultimately “Database as a Service” (DBaaS) means giving end users — application owners, developers, and others — the ability to request and provision database engine components in a self-service manner. For some enterprises and service providers, responding to requests for database services is an activity that takes a lot of time from DBAs on a daily basis, and automating it frees up valuable time for other value-added tasks, reduces human error, and increases consistency and confidence in operations.
Depending on the requirements, the scope of a DBaaSimplementation may include coverage for all or part of the following considerations:
-What components should be deployed?Deployment of a database, an instance, a machine with MicrosoftSQLServer installer, or a set of machines including SQLServer?
-Should or will deployments occur on a shared fabric only, or will we want to enablededicated scenarios where database components are dedicated to a specific tenant?
-What about the service level and high availability aspects? Are we talking about deploying commodity databases without any high availability capabilities, or surfacing highly available database components?
-How much of the lifecycle of the database component should be covered? Is it just about the deployment and delegation of the database components, or should it cover the full lifecycle (resize, monitoring, database user management, decommissioning, etc.)?
There is no good or bad answer to these questions, it just depends on what IT and business issues you are trying to solve with your DBaaS implementation.
Whatever the end solution chosen, it will likely consist of multiple building blocks or layers like these:
-The fabricis the foundation layer providing compute, network, and storage resources for hosting the SQLServer database components. This fabric can benefit from the performance and scale capabilities from WindowsServer and SQLServer. For example, increased Hyper-V guest VM scale and performance (virtual processors, memory, disk IOPS), storage flexibility and choices for both virtual machines and databases (SMB support, Storage Spaces, etc.).Resource consolidation and pooling are often the first phase of a SQLServer private cloud project leading to DBaaS, meaning that optimizing the footprint and performance of the fabric is generally an important topic.
-Theself-service interface (portal and APIs)sits as the top layer to receive requests and let users know about the status and progress of those requests, and possibly interact with their delegated resources
-Amanagement and automation layer to integrate the self-service interface with the fabric.The management layer may also provide added benefits like in-depth application-level monitoring, inventory, patching, and backup/restore capabilities.
This document will focus on how the Microsoft stack addresses these different layers in the context of DBaaS, leveraging MicrosoftWindowsAzurePack (WAP) as the main portal, and it will also mention other options as well.
Finally, while DBaaS is a key benefit of the Microsoft Private Cloud stack in SQLServer environments, there are other reasons why WindowsServer and MicrosoftSystemCenter are a great platform to virtualize and manage SQLServer. Some of these reasons are detailed in the last section of this document.
Delivering DBaaS scenarios through System Center and the Windows Azure Pack
In conjunction with WindowsServer and SystemCenter, Windows Azure Pack[1] (WAP) enables enterprises and service providers to deliver a subset of MicrosoftAzure services in their datacenters. WAP provides consistency with Azure through the user experience in the portal, and also aims at providing consistency through the APIs. Both the portal and the APIs ship as part of WAP.
WAP provides several services out of the box, including web sites, VM Clouds, and SQLServer, among others.These services are surfaced by WAP through the corresponding resource providers.
WAP admin portal
WAP tenant portal
More specifically in the context of this discussion:
- The VM Cloudsresourceprovider enablestenants todeploy and manage virtual machines, which could host SQL Server, on top of a Hyper-V fabric managed by the Virtual Machine Manager (VMM) component of System Center. This is typically what most people would call Infrastructure as a Service (IaaS), and it maps to the “dedicated” scenarios.
- The SQL Serverresource provider (described here[2]and in this session from Ignite 2015[3]) enables tenants to deploy and manage databases on top of a SQLServer fabric. This is sometimes referred to as a Platform as a Service (PaaS) approach, and it mapsto the “shared” scenarios.
With these two resources providers and in the context of WAP, the three layers mentioned in the introduction look like the figure below. The figure is simplified, because VM Clouds are also managed by System Center.
Microsoft Private Cloud stack
Whether the VMs or databases are highly available or not depends on how the underlying fabric was designed. For example, the SQLServer resource provider can work with SQLServer hosts using AlwaysOn configurations.
In the next section of this document, we will look more closely at each provider to understand their capabilities out of the box, and what additional content is available to help you realize the DBaaS potential.
Note:Both resource providers can be used in plans surfaced to tenants. Whether you would use one or both of them dependson your requirements, and the resources at your disposal. It is also possible to mix and match, that is,to have some plans offering virtual machines and databases, and other plans only offering one of these. For more details on this topic, see the section titled “Do I need to use both the VM Clouds and the SQL Server resource providers?” in the FAQ section of this document.
Preparing the VM Clouds fabric
The VM Cloud fabric consists of clouds leveraging compute, network and storage on Hyper-V hosts, on which tenants will be given the ability to deploy virtual machines hosting SQLServer.
The fabric for virtual machines is made up of clouds, as defined and managed in System Center Virtual Machine Manager (VMM). Once the clouds are defined in VMM, and VMM is connected to WAP through Service Provider Foundation (SPF), the clouds show up in the WAP admin portal:
Notice how the actual clouds are not detailed on the previous screenshot;they are defined and configured in VMM, and just reused by WAP. The actual architecture used under the hood (clustering or not, using SAN arrays, Storage Spaces, etc.) is up to the administrator when defining each cloud. In an actual implementation, we could expect the “Gold” cloud to have more features, higher availability or performance. The way that you craft your VM Clouds is really up to you and your requirements.
The clouds will then be used when creating plans, as explained later on in this document.
Templates for virtual machines with SQL Server
An important aspect of the VM Clouds resource provider is the Gallery tab, where you can view and import currently installed VM role gallery items:
These are templates which include deployment of the operating system and some additional applications.
You can find sample VM Role gallery items using Web Platform Installer (WebPI) as explained in Shawn Gibbs’ post here[4], and a more general reference for resources related to VM Role Gallery Items has been posted here[5] by Charles Joy.
/ We have released VM Role Gallery Items for SQL Server, as detailed in these blogs posts here[6], and here[7].More specifically:
-A SQL Server 2012 SP1 “advanced” template (leveraging an unattended installation)
-A SQL Server 2014 “advanced” template (leveraging an unattended installation)
-A SQL Server 2012 SP1 “sysprepped” template (this is an update to the VM Role gallery item initially published by the VMM team on Web Platform Installer)
You can use these as foundation, to help you get started. You can also leverage the VM Role Example kit[8] to start your own.
Preparing the SQL Servers fabric
The SQLServer fabric consists of “groups” of physical or virtual SQL Servers, on which tenants will be given the ability to deploy databases. Those servers can be physical or virtual, and require[9]SQLServer2008SP3, SQLServer2008R2SP2, SQLServer2012SP1, or SQLServer2014.
Here is an example with a small environment with five SQLServer servers, and you can see how they were assigned to groups:
The groups can be organized as you like, for example per site, by usage (“production”, “development”), or even by business units – this can be used to ensure that tenants only use specific hosts when creating databases, since the plan they subscribe to will be attached to specific SQLServer groups (more on that later).
Just like with VM Clouds, the way SQLServer is configured on these servers and groups is up to the administrator. This means some of the servers and groups may be using AlwaysOn[10] capabilities or different storage architectures(and costs). This also highlights the capability to “tier” your SQLServer groups, just like you can do it for VM Clouds. You can view this in a similar manner to what Microsoft Azure provides forSQL Azure Database Basic, Standard and Premium offerings[11].More details on performance-related questions can also be found in the FAQ section, including support for Resource Governor on SQLServer2014 introduced in Update Rollup 5. For more information, see “When using the shared model with the SQL Server Resource Provider, how can I ensure my tenants get their fair share of performance?” and “Do I need to use both the VM Clouds and the SQL Server resource providers?”in the FAQ section of this document.
The “SQL Servers” view in the WAP administrator portal with Update Rollup 5 installed. Notice the new tab for Resource Pool Templates and the new Resource Pools column, both related to SQLServer Resource Governor support.
In the WAP administrator portal, adding a SQLServer instance to the SQLServer fabric can be achieved like this:
Note that the SQLServer has to be preprovisioned (although you could automate that too, as we will be discussing later). Another prerequisite is that the SQLServer instance must have SQLServer authentication enabled, because it is required to register the server with the SQLServer resource provider. While this requirement may sound surprising for some of our enterprise readers, remember that WAP is also often used in service providers’ scenarios, where the SQLServer fabric servers are likely to be in a hoster domain, fully decoupled from the tenants’ domains.
Update:Update Rollup (UR) 6[12]also adds the capability in the user interface to edit the properties of a hosting server. Only the capacity can be edited once databases have been created.This capability was already available via API in Update Rolup (UR) 5.
Enabling DBaaS for tenants, through plans and subscriptions
Afterwe have the fabric setup, we need to create plans that tenants can subscribe to. At this stage, we only need to provide a name for the plan and choose the services it will contain:
By default, the plan is created as private so that nobody can subscribe to it until it is made public, and its services are not configured yet:
We can then configure how each service is going to be used in our DBaaS plan:
- VM Clouds:
We need to pick which cloud is going to be leveraged by this plan
It is possible to set optional quotas
We define to which networks the VM can be connected – these can be directly mapped to the actual underlying network or be virtualized (fully isolated or routed through a gateway).
An important step is to define the gallery items that can be used by this plan. In this scenario, the gallery items chosen are related to SQLServer.
Finally, this is where you can define which actions will be available to tenants for their virtual machines.
- SQLServers
We need to add SQLServer groups to our plan, also with limits about what can be used in these groups. Note: We will get back to the Max Additional Size Per Database If Add-Ons Acquired (MB) option shortly.
Also note that this screen is a bit different after installing Update Rollup (UR) 3 and 5, as UR3 adds a Database Windows Authentication option, and UR5 adds the Resource Pool Template option if the select group supports SQLServer Resource Governor. Both these additions are discussed later in this document.
In this example, only one group was added to this plan, but adding multiple groups is possible
Finally, we can optionally configure add-ons that tenants can subscribe to, to augment the services they have in a plan. Add-on subscriptions are tracked and can be charged for.
In this scenario, I created an add-on called “SQL Server Quota Upgrade” and configured it for the “Production” group, with these settings:
This add-on can be linked to the plan, using the Link Add-On option from the plan’s dashboard page:
Once all of this is done, we just have to make our plan public, so that tenants can see it and subscribe:
Looking at the tenant experience
Plan subscription
Let’s take the example of a tenant who has no subscription yet:
This tenant requests a new subscription, and selects the “Database as a Service” plan: