Building a mixed cloud model for SharePoint – Part 5

Building a mixed cloud model for SharePoint” series is to show you how possible you can deploy SharePoint workload not only by using Azure Compute service but also Platform-as-a-service based such as Azure Active Directory Domain Services (AD DS), Azure SQL Database.

In this article, let’s again have a look the two deployment models for your SharePoint workload on Azure and decision considerations. Make sure you understand that SharePoint workload on Azure is still considered on-premises deployment as we still use Azure Compute to host SharePoint.

Keep track the series:

Deployment Model

Deploying SharePoint on Azure must require Azure Compute to run SharePoint platform. A complete deployment also requires Active Directory as an identity provider and SQL Server to host databases. That said, there are two models:

  • Full IaaS
  • Mixed IaaS & PaaS (mixed model)

Full IaaS is which you fully use Azure Compute to deploy Active Directory, SharePoint and SQL Server. There is not any PaaS based service involved in your SharePoint farm. Mixed model is which you only deploy SharePoint workload on Azure virtual machine while other roles including identity and database are used by Azure AD DS and Azure SQL Database. Below is what we have completed so far. In the illustration, Azure AD DS is the managed domain controller that our SharePoint farm is joined to. All the identities are provisioned and synchronized from Azure Active Directory. The App Workload tier contains virtual machines running SharePoint workload. In Database tier, there are two Azure services being used. One is Azure Compute which I ran SQL Server to host SharePoint configuration database and Search databases with the following reasons:

  • Integrated Windows authentication is not supported in Azure SQL Database (as of this article).
  • There is no way I can specify target databases in Azure SQL Database service when provisioning Search service application.

Azure SQL Database service is used to host SharePoint content databases and other service application’s databases. The setup covers almost common service applications available in SharePoint 2016, including User Profile, Managed Metadata, Secure Store Service and Business Connectivity Service. The other databases such as App Management, Word Automation Database, Machine Translation Service and Project Server should not have any problem. Although I haven’t tested these yet , I still believe Azure SQL Database service can host.

Deployment Advantages

SharePoint platform is considered a heavy workload and complex platform. The complexity can even be seen from the initial installation in which you have to prepare many things. Moreover, the platform has so many features and service applications bundled in its code base.

When using mixed cloud model, the very first advantage we take is that infrastructure and administration effort of SQL Server is reduced. There should not be a challenge to manage only configuration database and Search. If all databases are hosted on a SQL Server virtual machine, the administration effort needed is much more, especially when you have many content databases in your farm. More obvious on reducing administration effort, if you already have a SQL Server running, hosting configuration database and Search is a good utilization while the rest of main workload are moved to Azure SQL Database. In large SharePoint deployment, there is very often not only one content database but many when sizing. “200 GB” perception would be a good answer in this case. It means if the total size of database is huge then splitting into smaller ones (as long as less than 200 GB) is recommended. In terms of administration effort, using Azure SQL Database I don’t have to take care of software and security patching on OS and SQL Server level. This is Microsoft’s responsibility.

If large deployment of many content databases is what we are talking right here, Azure SQL Database with elastic pool is a competitive advantage. Elastic pool in Azure SQL Database allows you to group multiple databases in a pool for better performance and management. Consider in your corporation, SharePoint is provided as a service among divisions, departments of groups of audience. For example, an intranet is an information center to all corporation’s employees. A customer relationship management application on top of SharePoint is targeted to a sales team. Each application is served for different number of users. That said, the recommended design is to have one/more dedicated database for each application in your SharePoint farm.

Let’s say a peak usage varies from 100 to 500 connections depending on the number of users and applications they access to. This variation is not always bounded in specific time. How do you make sure not any of database receives high peak or becomes overloaded state? In this circumstance, monitoring and performance management becomes difficult. Using Elastic Pool in Azure SQL Database, all content databases are added to a pool. Elastic Pool uses a performance unit called eDTU (elastic Database Transaction Unit), allowing you to allocate that unit to multiple databases to control unpredictable periods of usage of SharePoint content databases. Elastic Pool provides you monitoring capability for each database, and give you recommendation based on the peak and average usage. For more information about Elastic Pool, read here.

DTU is not easy to understand and convert to IOPS when doing database sizing in Azure SQL Database. Read here to get more understanding.

Ideally there are two pools in your Azure SQL Database design. One is for content database. The latter is for service application database. You may have more pool to host content database depending how large your SharePoint farm is.

Followed by performance monitoring, by chance we realize that Azure SQL Database helps us in database sizing. When all databases are in a pool, you are given resource utilization, average usage or so on which are so helpful to know.  Sizing database server for SharePoint on-premises deployment is not often correct. Sizing factors based on application functionality, workload and queries are good to know but these are not usually used for sizing. In Azure SQL Database, once you realize the allocated eDTU is over estimated, you can reduce to save cost. What can be seen an advantage is that if you resize your Azure virtual machine or scale down your on-premises SQL Server virtual machine, you need downtime. Adjusting eDTU in Azure SQL Database in a pool can be completed with no database downtime. There is not any end-user communication plan, including downtime is needed.

For people accidentially wearing DBA hat, writing database query is a nightmare. When you receive end-user report related to performance, your job is to look to database performance. Perhaps you would have to write some T-SQL script to query performance. If you host your database on Azure SQL Database, the fear would be overcome. With Intelligent Insight feature, you can have an overall performance overview of your SharePoint databases. I don’t know how deep and supportability the Query Performance Insight queries to content database though.

For high availability, we would not have to worry thanks to Microsoft’s commitment. Of course, some factors such as natural disaster, government action or so on documented in SLA are not guaranteed. With that in mind, recovery plan needs to be taken. Geo-replication in Azure SQL Database allows you to quickly select the region, logical server and even the destination elastic pool you want to replicate your SharePoint content databases to.

If database level replication does not satisfy you, consider the case you want to perform failover of the logical server. In Azure SQL Database, the Failover group feature allows you to create a group and add a secondary logical server to this group. The mechanism is pretty much similar to AlwaysOn feature in SQL Server. Read/Write policy with 24-hour timeframe is supported in the feature.

Azure SQL logical server is not a virtual machine running Microsoft SQL Server. By Microsoft definition, the term “logical server” is like a control panel which provides administration capabilities to help you manage your SQL databases

Added a little more thanks to Emmanual’s comment down the article, Geo-replication is used for disaster recovery scenario, not high availability. When the farm gets off due to datacenter outage, I’d force the failover to the secondary logical server where content databases are replicated and trigger passive SharePoint farm. There are more things to do than what are being discussed here when it comes to SharePoint farm disaster recovery. I just wanted to give out a conceptual idea based on the model.

In terms of security, Azure SQL Database allows you to control inbound traffic to logical server and all databases inside. In other words, you can add an existing virtual network (refer to part 2) to let all resources (e.g SharePoint virtual machine) connect to databases. Without this feature, the attacker has chance to grab your connection string and perform some brute-force attacks to guess SQL account admin credential. Using this feature, all traffics out of the virtual network are refused. Azure SQL Database also gives you the ability to use Azure AD account which is more controlled from identity management perspective. Transparent Database Encryption (TDE) and Dynamic Data Masking features are shifted to Azure SQL Database. TDE is supported in SharePoint while DDM is not. Azure SQL Database also provide Threat detection on database but I don’t think it is applicable to SharePoint since a provisioned content database contains pre-defined table, schema and so on.

Deployment Disadvantage 

If there is a disadvantage, I would say that is technical limitation and possibility. There is not any official information directly from Microsoft  or Microsoft engineers relating to deploying SharePoint database workload on Azure SQL Database. My series only include academic research and experimental deployment . I have no real-world deployment so far honestly. From what we have seen, it is likely possible.

How about performance? It’s hard to say performance in virtual machine deployment is better or worst than Azure SQL Database. In virtual machine deployment we have IOPS, memory and CPU along while DTU is a performance sizing factor in Azure SQL Database. The thing to know is if sizing needs to be adjusted, Azure SQL Database is more flexible.

SharePoint Search supportability is perhaps a big disadvantage. Search service application has four databases. Except administration database, the others are read/write-intensive databases. Moreover, when data size is huge, Search also needs to be scaled out. This is a burden to SharePoint or database system administrator to host Search databases in a SQL Server virtual machine.

While Azure SQL Database offers several security features, the current mixed model still doesn’t support Azure AD DS to authenticate against Azure SQL Database. It’s because Azure SQL Database does not belong to any Azure AD or Azure AD DS domain controller. The only way to make a connection is to set an account in Azure AD to become SQL logical server admin. This does not make much sense when provisioning a SharePoint database. Note that even the Azure AD username you set as an admin is the same with Azure AD DS, the real identity including password (perhaps kind of SAM structure) is different.

Conclusion

In this article, I’ve tried to point out some advantages when you host SharePoint databases on Azure SQL Database. Note that this is my personal opinion and academic research without any real-world deployment so far.

This article is not the last one. I’m planning to have more articles in the series covering financial analysis for the two models,  including lift-and-shift strategy from on-premises to the mixed cloud model. I hope this series gives you more decision considerations.

Comments

  1. February 2, 2018 — 7:25 pm

    Thanks Thuan, Academically fascinating 🙂

    This architecture would indeed make sense if you have a very large content to host, as you have the same administration cost for everything else (for example, you’d have to cluster the SQL VM to avoid it being a SPOF). The geo clustering would be partial, as streched farms are not supported, so you are bound to have numerous round trips to your “central” site, afaik.

    A lot of this could be alleviated if sharepoint could natively use SAML instead of WIA, as you stated. For example, the hybrid search relies on an “OnPremisesSecurityIdentifier” in the AAD user account to security trim. That is still … a SiD, which btw, you can replace on the fly in the crawl component (not supported – but it works!)

    i suppose it’ll be implemented in sharepoint 2022 🙂

  2. Thuan Soldier
    February 3, 2018 — 2:02 pm

    Thank you very much for your valuable comment. During the past 3 years, I’ve involved in several larger farms in which the total size of database is over 2 TB. And the largest number of content databases in one of those farms due to the designated blueprint farm design is 200 (for security, performance reason and variety of service providers) which brings huge challenge to manage. The idea of using geo-replication is for disaster recovery. Instead of stretched farm, ideally there would be another SharePoint farm in the same region of the secondary SQL logical server. If the datacenter outage happens, I would force the failover to the passive farm. Of course, if this is the DR design, it would be more complicated than what are being discussed here. I’d love to spend time researching more on this.

    Btw, I really like to have your advice and insights over the topic, and even if you have something to contribute, let me add it to the article.

Leave a Reply

© 2018 The Soldier of Fortune.