Understanding the throttling in SharePoint Online

As mentioned many times in this blog, it is very easy to use SharePoint for document management and to maintain a small list without writing a custom application. But it is a nightmare when it comes to customizing SharePoint and interacting with it from other applications from APIs. One of the challenges is the throttling behavior. 

We should agree that it is a SaaS application and Microsoft cannot fulfill all our API requests. They should throttle for the below reasons

• In order to manage noisy neighbors give equal consideration to all the tenant
• Ensure the availability of the entire system

The problem is the throttling details are not clear from the documentation. None available if we ended up in a situation where we need to use user-based flow. Forget about using ROPC flow from a daemon application. 

Below is a flow chart that is trying to make sense of when our application will get throttled by Microsoft.

Click on it to open as SVG to navigate to the links.

Azure @ Enterprise - Should I keep tempdb files in d: drive or cluster storage in FCI ?

There are many importance to the tempdb¹ database in SQL Server. It is a system database that plays crucial role to keep the SQL Server performant. The best practices when we setup tempdb in on-premise SQL Server is different than when we setup tempdb on Azure SQL VM.

Problem

Azure SQL VMs has special d:\ drive that is ephemeral². It is expected to be used for temporary purpose. There are reasons why people place the tempdb files in that drive. Reasons are mainly performance related. It is fine if the machine is not part of the FCI³ (Failover Cluster Instance) but what about if the machine is part of FCI?

Will the d: drive is monitored by the fail over mechanism and trigger failover in case something happens to that drive? Likelihood of the scenario is very less but there is a possibility.

Solution

The FCI is not monitoring the d:\ drive and it will not trigger a fail over in case something goes wrong there. SQL Server will simply go down. Leading to reduction of high availability.

The recommended solution is to keep the tempdb file in the cluster storage. Docs⁴ says the same.

All good. Only in case someone missed to read the docs, they get into trouble.

Why this post if the official docs says it right?

I started writing this post in 2021 after 6 months of struggle. At that time this guidance was not there.

This section was committed⁵ to docs only on 2022-01-13

Reference

• ¹ https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16
• ² https://docs.microsoft.com/en-us/azure/virtual-machines/windows/faq#can-i-use-the-temporary-disk--the-d--drive-by-default--to-store-data-
• v https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server?view=sql-server-ver16
• ⁴ https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql
• ⁵ https://github.com/MicrosoftDocs/sql-docs/commit/1e920fbd09b099a5548fa55e061b004e1c2270ac

SQL Always On Availability Group - Mind map

 As solutions or data architects we often needs to work with high availability and disaster recovery designs. If the technology stack includes SQL Server the latest feature its is offering is SQL Server Always On Availability Groups. This post introducing a simple mind map of the same.

The diagram used PlantUML based coded diagramming technique and we can easily get started. It is available in GitHub. Feel free to modify and initiate pull requests.

SQL Server from container - Before jumping into the bandwagon

It is easy to get started on development and simple testing using SQL containers. It was discussed in the previous post. But before putting into production and start developing real applications we had to make sure the below things at least.

SQL Server running inside Linux container doesn't mean its free

People from the Linux world or in general people tend to think that everything in Linux is free. But that is not true in the case of SQL Server similar to other enterprise software running in Linux. In order to use the SQL Server container in production, we need a valid license even in the Linux containers.

Configuring and operating

Running and operating containers is an added skill to the operation teams. We need to make sure our client or in-house operational teams are capable of doing it.

Feature parity

There are many factors influencing feature differences. Microsoft always says its the same codebase but features don't reflect that. Let's see the factors that decide features

• Versions - Obviously there will be differences between SQL 2016, SQL 2017, and SQL 2019 features. The codebase is never the same though the latest may be built on top of the previous.
• Editions - First level feature difference inside the same version is based on the edition. This is regardless of wherever we run the SQL instance. This makes sense as we need to give more money to unlock features. This is there from the early days of SQL Server.
• Running platform - There are feature differences based on where the SQL instances run such as standalone inside Windows OS, Azure, and Linux containers.
• Azure PaaS platform - Inside Azure, there are Azure SQL and Azure SQL managed Instance models. They have many feature differences.

The containerized SQL server is more like the Azure SQL than compared with desktop SQL installation. Features such as FileStream, CLR support are not available in SQL container. The full list of features that are not supported in the SQL container can be found here.

Production readiness

As per Microsoft, SQL Server on Windows Container is not production-ready. But SQL Linux containers are production-ready. They have documentation on High availability when running SQL Linux containers and how to orchestrate using Kubernetes.  But not sure currently companies are investing to just move their SQL workload to a containerized production environment. 

Everyone agrees to start using it for development and testing. Eventually, when more workloads start using the SQL Server Big Data Cluster features, production will also start using SQL Server in containers. There are also documentation says SQL container as is don't support clustering hence the DR can be done only by log shipping. The Kubernetes based HA for SQL containers may not be instantaneous for an RDBMS database like Always On Availability Groups or FCI. It may take some time to detect the failure and start the next container instance.

As of now, my takeaway is not to use SQL containers for critical production workloads.