Virtual Network Endpoints

Virtual network endpoints allow traffic from a specific Azure Virtual Network. These rules apply at the server level, not just the database level. Additionally, the service endpoint applies to only one region, which is the underlying endpoint’s region. One additional concern is that the virtual network that is connecting to the Azure SQL Database must have outbound access to the public IP address for Azure SQL Database, which can be configured using service tags for Azure SQL Database.

Private Link

The Private Link feature allows you connect to Azure SQL Database (and other PaaS offerings) using a private endpoint. A private endpoint allows for a connection to your Azure SQL Database to go entirely over the Azure backbone network and not over the public internet. This feature provides a private IP address on your Virtual Network. Another feature of private link is that it allows for Azure Express Route connections through that circuit. Private link offers several additional benefits include cross-region private connectivity and protection against data leakage by only allowing connections to specific resources.

https://docs.microsoft.com/en-us/azure/private-link/private-link-overview

Because the Daily Bugle is using CLR, which is an instance-scoped feature, Azure SQL Managed Instance will allow them to move to a PaaS offering without having to rewrite the CLR applications.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-case-studies/2-scenario-1

Denormalization

A normalized database does not always give you the best performance. Normalized data frequently requires multiple join operations to get all the necessary data returned in a single query. There is a tradeoff between normalizing data when the number of joins required to return query results has high CPU utilization, and denormalized data that has fewer joins and less CPU required, but opens up the possibility of update anomalies.

Denormalized data is not the same as unnormalized. For denormalization, we start by designing tables that are normalized. Then we can add additional columns to some tables to reduce the number of joins required, but as we do so, we are aware of the possible update anomalies. We then make sure we have triggers or other kinds of processing that will make sure that when we perform an update, all the duplicate data is also updated.

Denormalized data can be more efficient to query, especially for read heavy workloads like a data warehouse. In those cases, having additional columns may offer better query patterns and/or more simplistic queries.

https://www.xenonstack.com/insights/data-denormalization/

Azure SQL SLA

Azure SQL maintains a service-level agreement (SLA) that provides financial backing to the commitment to achieve and maintain service levels. If your service level isn't achieved and maintained as described in the SLA, you might be eligible for a credit toward a portion of your monthly service fees.

Currently, you can achieve the highest availability (99.995%) from an Azure SQL Database Business Critical deployment that has Availability Zones configured. The Business Critical tier is the only option in the industry that provides RPO and RTO SLAs of 5 seconds and 30 seconds, respectively. RPO stands for recovery point object. It represents the amount of data you're potentially willing to lose in a worst case scenario. RTO stands for recovery time objective. It represents how long it takes to be back up and running again if a disaster occurs.

For General Purpose or single-zone Business Critical deployments of Azure SQL Database or Azure SQL Managed Instance, the SLA is 99.99%.

The Hyperscale tier's SLA depends on the number of replicas. Remember that you choose how many replicas you have in Hyperscale. If you don't have any, the failover behaviour is more like that of General Purpose. If you have replicas, the failover behaviour is more like that of Business Critical.

Here are the SLAs, based on the number of replicas:

• 0 replicas: 99.5%

• 1 replica: 99.9%

• 2 or more replicas: 99.99%

https://azure.microsoft.com/en-us/support/legal/sla/sql-database/v1_5/

Role assignment is a combination of the role definition, security principal, and scope.

https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles

The msdb database acts as the data store for the SQL Server Agent.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-ver15

The service tier you select will determine the underlying architecture of the database or managed instance that you deploy.

General Purpose

Databases and managed instances in the General Purpose service tier have the same availability architecture. Using the following figure as a guide, first consider the application and control ring.

The application connects to the server name, which then connects to a gateway (GW) that points the application to the server to connect to, running on a VM. With General Purpose, the primary replica uses locally attached SSD for the tempdb. The data and log files are stored in Azure Premium Storage, which is locally redundant storage (multiple copies in one region). The backup files are then stored in Azure Standard Storage, which is RA-GRS by default. In other words, it's globally redundant storage (with copies in multiple regions).

All of Azure SQL is built on Azure Service Fabric, which serves as the Azure backbone. If Azure Service Fabric determines that a failover needs to occur, the failover will be similar to that of a failover cluster instance (FCI). The service fabric will look for a node with spare capacity and spin up a new SQL Server instance. The database files will then be attached, recovery will be run, and gateways will be updated to point applications to the new node. No virtual network or listener or updates are required. This capability is built in.

Business Critical

Business Critical can generally achieve the highest performance and availability of all Azure SQL service tiers (General Purpose, Hyperscale, Business Critical). Business Critical is meant for mission-critical applications that need low latency and minimal downtime.

Using Business Critical is like deploying an Always On Availability Group (AG) behind the scenes. Unlike in the General Purpose tier, in Business Critical, the data and log files are all running on direct-attached SSD, which significantly reduces network latency. (General Purpose uses remote storage.) In this AG, there are three secondary replicas. One of them can be used as a read-only endpoint (at no additional charge). A transaction can complete a commit when at least one of the secondary replicas has hardened the change for its transaction log.

Read scale-out with one of the secondary replicas supports session-level consistency. So if the read-only session reconnects after a connection error caused by replica unavailability, it might be redirected to a replica that's not 100% up to date with the read-write replica. Likewise, if an application writes data by using a read-write session and immediately reads it by using a read-only session, the latest updates might not immediately be visible on the replica. The latency is caused by an asynchronous transaction log redo operation.

If any type of failure occurs and the service fabric determines a failover needs to occur, failing over to a secondary replica is fast because the replica already exists and has the data attached to it. In a failover, you don't need a listener. The gateway will redirect your connection to the primary even after a failover. This switch happens quickly, and then the service fabric takes care of spinning up another secondary replica.

Hyperscale

The Hyperscale service tier is available only in Azure SQL Database. This service tier has a unique architecture.

Because this architecture uses paired page servers, you can scale horizontally to put all the data in caching layers. This new architecture also allows Hyperscale to support databases as large as 100 TB. Because it uses snapshots, nearly instantaneous database backups can occur regardless of size. Database restores take minutes rather than hours or days. You can also scale up or down in constant time to accommodate your workloads.

It's interesting to note how the log service was pulled out in this architecture. The log service is used to feed the replicas and the page servers. Transactions can commit when the log service hardens to the landing zone. So the consumption of the changes by a secondary compute replica isn't required for a commit. Unlike in other service tiers, you can determine whether you want secondary replicas. You can configure zero to four secondary replicas, which can all be used for read-scale.

As in the other service tiers, an automatic failover will happen if service fabric determines it needs to. But the recovery time will depend on the existence of secondary replicas. For example, if you don't have replicas and a failover occurs, the scenario will be similar to that of the General Purpose service tier: the service fabric first needs to find spare capacity. If you have one or more replicas, recovery is faster and more closely aligns to that of the Business Critical service tier.

Business Critical maintains the highest performance and availability for workloads with small log writes that need low latency. But the Hyperscale service tier allows you to get a higher log throughput in terms of MB/second. So you'll need to consider your workload when you choose between the two.

https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

Performance overhead of the Query Store

Collecting data in any system has an execution cost of CPU cycles, memory, and disk utilization that is known as observer overhead. The Query Store is designed to minimize the impact of its data collection. Data for the Query Store is written to memory for each new query, and each execution of an existing query. If this information was written to disk for each execution, the performance overhead would be significant, so SQL Server uses a setting called DATA_FLUSH_INTERVAL_SECONDS to control the frequency of flushing the Query Store data to disk. By default, the data is flushed every 15 minutes, but this value is a user configurable setting per database. This process of collecting the query and runtime information and writing it to disk is shown in the image below:

The other important setting to note in the Query Store options is Max Size (MB), which sets the amount of storage for the data collected. The default value is 100 MB and is commonly increased to around 1-2 GB depending on the volume of queries executed against a database. Some workloads that have a high number of unique ad-hoc queries, which is characteristic of applications written in Entity Framework, may see high data volume. If the size of the data stored on disk exceeds the Max Size (MB) the Query Store will go into read-only mode until more space is added, or cleanup happens. The default value for time-based cleanup is 30 days, but the size-based cleanup mode option will remove older queries as the Query Store approaches its max size. You will want to strike a balance between the amount of data you keep and the amount of space being consumed on disk.

The other setting you should note is Query_Capture_Mode which defaults to Auto. This value means queries with insignificant compilation time and duration are ignored, along with infrequent queries. This default was changed in SQL Server 2019, and in Azure SQL. The older default was All, which captures all queries executed. There are also options of none (collect no queries) and Custom, which was also introduced in SQL Server 2019, which allows you to use metrics such as execution count, compile CPU time, and execution time to limit which queries are captured. This setting is useful for a database where most of the queries are unique, as these unique queries can cause the Query Store to grow rapidly in size.

Plan forcing in the Query Store

Another benefit of the Query Store is the ability to force a given execution plan for a query. Plan forcing in the Query Store also drives the automatic tuning feature that was introduced in SQL Server 2017, which uses the last known good execution plan for a given query after a performance regression occurs. Since the query can store multiple execution plans for a given query, you can have the database engine force a known good plan for a given query. Plan forcing should be used for queries that have suddenly changed execution plans and have experienced significant regression in execution time. Plan forcing offers a quick mitigation for a performance problem, but you should always investigate what caused the performance regression and look to resolve the cause of the execution plan variability. Those fixes could be adding an index or looking to rewrite part of a query.

https://docs.microsoft.com/en-us/azure/mysql/concepts-query-store

Columnstore Indexes

Columnstore indexes were introduced in SQL Server 2012 and offer improved performance for queries that run large aggregation workloads. This type of index was originally targeted at data warehouses, but over time columnstore indexes have been used in a number of other workloads in order to help solve query performance issues on large tables. As of SQL Server 2014, there are both nonclustered and clustered columnstore indexes.

Like b-tree indexes, a clustered columnstore index is the table itself stored in a special way, and nonclustered columnstore indexes are stored independently of the table. Clustered columnstore indexes inherently include all the columns in a given table. However, unlike rowstore clustered indexes, clustered columnstore indexes are NOT sorted.

Nonclustered columnstore indexes are typically used in two scenarios, the first is when a column in the table has a data type that is not supported in a columnstore index. Most data types are supported but XML, CLR, sql_variant, ntext, text, and image are not supported in a columnstore index. Since a clustered columnstore always contains all the columns of the table (because it IS the table), a nonclustered is the only option. The second scenario is a filtered index—this scenario is used in an architecture called hybrid transactional analytic processing (HTAP), where data is being loaded into the underlying table, and at the same time reports are being run on the table. By filtering the index (typically on a date field), this design allows for both good insert and reporting performance.

Columnstore indexes are unique in their storage mechanism, in that each column in the index is stored independently. This offers a two-fold benefit — a query using a columnstore index only needs to scan the columns needed to satisfy the query, reducing the total IO performed, and it allows for greater compression, since data in the same column is likely to be similar in nature.

Columnstore indexes perform best on analytic queries that scan large amounts of data, like fact tables in a data warehouse. Starting with SQL Server 2016 you can augment a columnstore index with an additional b-tree nonclustered index, which can be helpful if some of your queries do lookups against singleton values.

Columnstore indexes also benefit from batch execution mode, which refers to processing a set of rows (typically around 900) at a time versus the database engine processing those rows one at time. Instead of loading each record independently and processing them, the query engine computes the calculation in that group of 900 records. This processing model reduces the number of CPU instructions dramatically. This works best for a query like the following:

1. Transact-SQL
2. SELECT SUM(Sales) FROM SalesAmount;

Batch mode can provide up to a 10x performance increase over traditional row processing. SQL Server 2019 also includes batch mode for rowstore data. While batch mode for rowstore does not have the same level of read performance as a columnstore index, analytical queries may see up to a 5x performance improvement.

The other benefit columnstore indexes offer to data warehouse workloads is an optimized load path for bulk insert operations of 102,400 rows or more. While 102,400 is the minimum value to load directly into the columnstore, each collection of rows, called a rowgroup, can be up to approximately 1,024,000 rows. Having fewer, but fuller, rowgroups makes your SELECT queries more efficient, because fewer row groups need to be scanned to retrieve the requested records. These loads take place in memory and are directly loaded to the index. For smaller volumes, data is written to a b-tree structure called a delta store, and asynchronously loaded into the index.

In this example, the same data is being loaded into two tables, FactResellerSales_CCI_Demo and FactResellerSales_Page_Demo. The FactResellerSales_CCI_Demo has a clustered columnstore index, and the FactResellerSales_Page_Demo has a clustered b-tree index with two columns and is page compressed. As you can see each table is loading 1,024,000 rows from the FaceResellerSalesXL_CCI table. By using the SET STATISTICS TIME ON option, SQL Server keeps track of the elapsed time of the query execution.

Loading the data into the columnstore table took roughly 8 seconds, where loading into the page compressed table took nearly 20 seconds. In this example, all the rows going into the columnstore index are loaded into a single rowgroup.

If you load less than 102,400 rows of data into a columnstore index in a single operation, it is loaded in b-tree structure known as a delta store. The database engine moves this data into the columnstore index using an asynchronous process called the tuple mover. Having open delta stores can impact the performance of your queries, because reading those records is less efficient than reading from the columnstore. You can also reorganize the index with the COMPRESS_ALL_ROW_GROUPS option in order to force the delta stores to be added and compressed into the columnstore indexes.

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

Platform as a Service (PaaS) provides a complete development and deployment environment in the cloud, which can be used for simple cloud-based applications as well as for advanced enterprise applications.

As shown in the image above, PaaS includes cloud provided operating system and management of your solution. For example, in Azure SQL Database and Azure SQL Managed Instance, the high availability, OS and SQL Server features, and backups are all provided by the Azure platform.

Hyperscale

Azure SQL Database has been limited to 4 TB of storage per database for many years. This restriction is due to a physical limitation of the Azure infrastructure. Azure SQL Database Hyperscale changes the paradigm and allows for databases to be 100 TB or more. Hyperscale introduces new horizontal scaling techniques to add compute nodes as the data sizes grow. The cost of Hyperscale is the same as the cost of Azure SQL Database; however, there is a per terabyte cost for storage. You should note that once an Azure SQL Database is converted to Hyperscale, you cannot convert it back to a “regular” Azure SQL Database.

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale