Share via

Building Database-as-a-Service on SQL Server 2016

  • Article

Many service providers wish to offer new types of services to their customers. They want to move further from regular IaaS to services with more added value, like PaaS or SaaS. One of the options is Database-as-a-Service offering aka DBaaS. Such service includes IaaS, DBMS software licenses and database engine maintenance, provided by the service provider and packaged into the single offer. Such service is similar to Azure SQL Database, but hosted in service provider datacenter.

Some service providers deploy and support SQL Server installations, dedicated for every customer (single-tenant DBaaS). But such solutions require more compute resources, it's hard to scale them, and it's a nightmare to maintain when you have 1-2 database admins and 100+ customers, which use such service. That's why multi-tenant DBaaS (one environment, used by different customers) is a better choice for service providers, which want to provide DBaaS to different customers at scale. Microsoft has the set of tools that any service provider can use to build DBaaS in their datacenter:

  1. SQL Server 2016 Enterprise with features like Resource Governor and Constrained Databases as a platform to run customer databases.
  2. Windows Azure Pack (WAP) with SQL Server Resource Provider as a self-service panel for tenants.

Azure Pack DBaaS Azure Pack DBaaS

Multi-tenant DBaaS (MT DBaaS) is a better choice for service providers because it allows to:

  • Share the license cost among different customers. For example, MT DBaaS, build on top of 10 VMs with 16 cores each, requires 80 SQL Server Enterprise 2core SPLA licenses, but it can be used by 100-300 different customers. MT DBaaS makes SQL Server Enterprise features affordable even for SMB customers.
  • Share the compute resources and scale it based on the load. When MT DBaaS works on top of existing IaaS, service providers can easily scale out (add new VMs with SQL Server) and scale up (increase the size of the VMs) the solution.
  • Centralized maintenance. Your database admins can maintain MT DBaaS environment in a centralized manner - install patches, schedule backup and indexing tasks, monitor what's happening in the environment in a single dashboard etc. It is much easier to maintain a single MT DBaaS with 100 customers than 100 single-tenant DBaaS environments.
  • Customers don't need to manage the database instance, because it's covered by service provider's database gurus. Customer needs to manage the DB only. But even that can be outsourced by the service provider for an extra fee.

Let's see how Multi-tenant Database-as-a-Service solution can be deployed using SQL Server 2016 Enterprise and Windows Azure Pack.

Deployment and operations considerations

Here are several points that you need to know when you are deploying and operating multi-tenant DBaaS:

  • You are required to use an Enterprise edition of SQL Server 2016 because you'll need Resource Governor functionality to distribute resources among tenants, which is not available in Standard Edition.
  • SQL Server Reporting Services, SQL Server Integration Services and Linked Servers don't support multi-tenant mode.
  • You can deploy SQL Server instances directly on a physical hardware, or on top of existing IaaS. While SQL Server, deployed directly on the hardware, can provide slightly more performance (up to 10%), most of the service providers that I work with prefer to deploy it on top of IaaS, because it is much easier to scale.
  • You can an existing WAP installation, that you already use for IaaS. If you don't use WAP for IaaS, you can deploy WAP for specifically for DBaaS purposes.
  • Start with the minimal highly available installation - a pair of VMs with 4 cores each. Scale out (up to 16 servers in a single SQL Server AlwaysOn Availability Group) and scale up (up to 240 cores and 12Tb RAM for an instance, deployed on Hyper-V 2016) when you will better understand the resource demand.
  • Use several disks on every SQL Server instance:
    • OS Disk (HDD)
    • Database file disk (HDD or SSD)
    • Database log disk (HDD or SSD)
    • Disk for TempDB (SSD)
    • Storage for backups (HDD or Azure Storage).
  • Consider to use backup data compression to reduce the storage consumption:
    USE master;
    GO
    EXEC sp_configure 'backup compression default', '1';
    RECONFIGURE WITH OVERRIDE;
  • Azure Pack uses Contained Databases functionality to isolate databases of different tenants from each other. Contained Databases apply some limitations on Multi-tenant DBaaS, like:
    • ALTER command is not available. You can use impersonation trick as a workaround, it is described below.
    • SQL Server Agent is not accessible by Contained DB owners by default. As a workaround, create a separate SQL Server login for every tenant on every instance and assign msdb's SQLAgentUserRole user role to that login. Share the credentials of that login with a tenant.
    • iFTS need to be additionally configured on every SQL Server instance.
    • Change Data Capture (CDC) is not supported.
  • Some applications weren't designed to run on Multi-tenant SQL Server. They may require privileges on the SQL Server Instance level, which in not allowed in Multi-tenant scenario, when different tenants use the same instance. Check the application requirement for SQL Server backend before moving it to Multi-tenant DBaaS. Keep in mind that:
    • Only TCP-IP protocol is available.
    • Collation is set on the instance level, DB owner won't be able to change it.
    • xp_cmdshell can't be used.
    • Application can't change data in master, msdb and model databases.
    • Linked Server and Remote Server functionality is not supported.
    • Application can't create its own stored procedures. Triggering stored procedures, that require securityadmin or sysadmin priveleges is not allowed.
    • Application is now allowed to install any software (EXE apps, COM objects) into the OS, where SQL Server instance runs.
  • Tenant DB name should be unique on the instance. Only English letters and numbers are allowed for database name.
  • Avoid being caught into "SQL Server admin trap" by malicious tenants.

New features of SQL Server 2016 in Multi-tenant mode

You are probably interested - will the great new features of SQL Server 2016 work in multi-tenant mode or not? Let's dig into the details.

  • InMemory OLTP - user needs to run ALTER command to create filegroups and containers, required for InMemory OLTP. As I've mentioned earlier, user can't run ALTER commands in Contained Databases. To enable InMemory OLTP for the database, you can use the impersonation trick.
  • Always Encrypted - this functionality requires to run a configuration commands as a user with a SQL Server login, registered in master database. Contained Database owners are not added to master DB as SQL Server Logins, so you will need to use an impersonation trick to enable Always Encrypted for the tenants DB.
    BTW, it is a good approach to protect frontend VMs (that contains Always Encrypted private keys) with Shielded VMs functionality. Otherwise fabric admin will be able to steal private keys from the frontend and encrypt the databases on the backend, even if they are protected by Always Encrypted.
  • Stretch Database - to enable stretching the DB to Azure SQL Database, you need to do several tasks:
    1. Allow network traffic from SQL Server instances to Azure
    2. Enable Stretch DB functionality on the SQL Server instance level by running sp_configure command as serveradmin or sysadmin on every instance.
    3. Enable Stretch DB functionality on the database using ALTER DATABASE and COMMAND DATABASE commands. Yes, the same impersonation trick will be required.
  • Row-level Security (RLS) - works as usual, Contained Databases don't require any extra steps to enable RLS by the tenant in his database.
  • TemporalDB, PolyBase and new Reporting Services are not supported in multi-tenant mode.

Impersonation trick

As I've mentioned earlier, some commands, that DB owner may need to use in real life, are not allowed in Multi-tenant mode with Contained Databases. Such command list includes ALTER, COMMAND, KILL and other commands, that may need to run on the database level, but require more permissions to execute.

As a workaround, you can create stored procedures, that will be triggered by the tenant towards his databases, but commands in fact will be executed under the user with enough permissions on the instance level. Here is an example of the script, that will execute KILL command in the tenant's DB under the privileged user on the instance:

 USE msdb
GO
CREATE procedure [dbo].[sp_kill2] 
 @SPID int
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON; 
DECLARE @DBNAME nvarchar(128) 
DECLARE @SQLEXEC nvarchar(1024) = '' 
DECLARE @SQLLoginEffectivePermoissions table (Permission nvarchar(128)) 
DECLARE @SQLEXECKILL nvarchar(128) = '' 
DECLARE @ErrorMessage nvarchar(1024) = '' 
 --get tenant’s DB name: 
SELECT @DBNAME= ISNULL(DB_NAME([dbid]),'') FROM sys.sysprocesses where spid = @SPID
IF @DBNAME<>'' 
BEGIN
 -- check if the user really have owner rights on the database, where he wants to execute the command: 
EXECUTE AS LOGIN = ORIGINAL_LOGIN(); 
SET @SQLEXEC = ' USE ['+@DBNAME+'] 
SELECT permission_name AS [Permission] 
FROM fn_my_permissions(null, N''DATABASE'') 
WHERE permission_name=''CONTROL'' 
ORDER BY permission_name;' 
 --executing the request: 
INSERT INTO @SQLLoginEffectivePermoissions
EXEC sp_executesql @SQLEXEC
REVERT
IF EXISTS (SELECT * FROM @SQLLoginEffectivePermoissions WHERE [Permission] IN ('CONTROL')) 
BEGIN
 --if user has correspoing permissions, execute KILL command: 
SET @SQLEXECKILL='USE [master]; kill ' + CAST(@SPID as varchar(4)) 
EXEC (@SQLEXECKILL) 
PRINT N'EXECUTED: '+ @SQLEXECKILL
END
ELSE
BEGIN
SET @ErrorMessage='Current SQL LOGIN does not have appropriate permissions to execute KILL command for SPID '++CAST(@SPID AS varchar(4)) + ' ON DBNAME '+ @DBNAME
RAISERROR (@ErrorMessage, -- Message text. 
16, -- Severity, 
1 -- State, 
 ); 
END
END
ELSE
BEGIN
SET @ErrorMessage= 'Process ID '+CAST(@SPID AS varchar(4))+' is not an active process ID.' 
RAISERROR (@ErrorMessage, -- Message text. 
16, -- Severity, 
1 -- State, 
 ); 
END
GO
GRANT EXECUTE ON OBJECT::[dbo].[sp_kill2] 
TO Public; 
GO
USE [master] 
GO
CREATE PROCEDURE [dbo].[sp_kill2] 
 @SPID int
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON; 
EXEC [msdb]. [dbo].[sp_kill2] @SPID
GO
GRANT EXECUTE ON OBJECT::[dbo].[sp_kill2] 
TO Public;

Configure Resource Governor

By default, all application, that run on SQL Server instance, have the same priority for CPU, RAM and Storage read/writes. Resource Governor is a feature of SQL Server Enterprise, that allows you to distribute the compute resources in a managed way.

You can configure Resource Pools Templates in Azure Pack admin portal, or you can create Resource Pools directly on every instance.

Resource Governor manages all the connection requests for the DB and uses its classifier to determine into which workgroup the session must be put in. One or several workgroups are connected to resource pools, which have their own assigned compute resources.

Here is an example script, that creates 4 resource pools:

  1. AdminPool - for administration tasks, executed by DBAs. No resource restrictions.
  2. PlatinumPool - high priority for the CPU and RAM resources
  3. GoldPool - medium priority
  4. Default (aka "SilverPool") - low resource priority, default option.

Download the configuration script here:  ConfigureResourceGovernor.sql

You can extend the script with MAX_IOPS_PER_ VOLUME parameters to specify the priorities for storage read/write operations. You can modify the parameters of Resource Governor in SQL Server Management Studio.

resourcegovernor

Audit recommendations

It is recommended to enable these audits on every instance of SQL Server 2016 in the multi-tenant environment:

  1. Record all create, remove and modify user actions
  2. Audit all DBs, that are being created and removed
  3. Record all ALTER DATABASE triggers
  4. Record all backup actions
  5. Audit all scheduled tasks
  6. Record all impersonated calls.

Here is the script that configures such audit policy on the selected SQL Server instance: ConfigureAudit.sql

Configure Policy-based Management

Policy-based management allows DBAs to prohibit and control some changes on the instance level and also on the DB level. These are the recommended policies that you should apply on the multi-tenant DBaaS:

  1. Don't allow to enable AutoShrink of the DB.
  2. Find all DBs that haven't been backup up for some time
  3. Allow DB and transaction log growth for all DBs
  4. Disable "Max Degree of Parallelism" option
  5. Detect all DBs with Recovery Model different from "Full".

The configuration script is available here: ConfigurePolicyBasedManagement.sql

To manage Policy violations in SQL Server Management Studio, go to Policy Management -> Policies -> Choose policy -> View Policies.

policyviolations

Typical deployment workflow

Typical multi-tenant Database-as-a-Service deployment workflow on top of SQL Server 2016 Enterprise and Windows Azure Pack will look like this:

  1. Deploy at least 2 instances of SQL Server 2016 Enterprise on top of Windows Server 2016 on the existing IaaS with AlwaysOn Availability Groups. This step is well described in the official documentation. Remember about deployment considerations, described above.
  2. Install all updates on Windows Server 2016 and SQL Server 2016. Update all passive AlwaysOn AG nodes first, and only then update active node.
  3. Configure Resource Governor with a provided script.
  4. Apply Audit rules with a provided script.
  5. Configure Policy-based management with a provided script.
  6. Deploy Azure Pack (in you don't use it for IaaS already). I recommend to use Minimal distributed deployment architecture with Windows Server 2016 Software Load Balancer.
  7. Install SQL Server Resource Provider for Azure Pack and configure it.
  8. Connect SQL Server AlwaysOn Availability Group to Azure Pack.
  9. Create Azure Pack plans for DBaaS like Silver, Gold and Platinum.
  10. Implement the impersonation trick, described above.
  11. Activate new SQL Server 2016 features (InMemory OLTP, AlwaysEncrypted, Stretch DB).
  12. Configure backups for the DBaaS environment and tenant databases. You can use DPM or Azure Backup Server for that.
  13. Configure DBaaS monitoring using Azure Log Analytics or System Center Operations Manager.
  14. Make money!

That's all for today. I hope it will be valuable for production deployments. If you are interested in new tricks and best practices for Service Providers, don't forget to subscribe to this blog using the button to the right.

Comments

  • Anonymous
    February 06, 2017
    Woah! I'm reaply loving the template/theme oof thos website.It's simple, yeet effective. A llot oof times it's tough tto gget that "perfect balance" between uability annd appearance.I muist sayy thast you've done a awesome job witrh this.In addition, thhe blog loads verey quick ffor me on Opera.Supesrb Blog!