Azure provides three main options to enhance availability for IaaS deployments:

• Availability Sets

• Availability Zones

• Azure Site Recovery

All three of these options are external to the virtual machine (VM) and do not know what kind of workload is running inside of it.

Availability Zones

Availability zones account for data centre-level failure in Azure. Each Azure region consists of many data centres with low latency network connections between them. When you deploy VM resources in a region that supports Availability Zones, you have the option to deploy those resources into Zone 1,2, or 3.A zone is a unique physical location, that is, a data centre, within an Azure region.

Zone numbers are logical representations. For example, if two Azure subscribers

both deploy a VM into Zone 1 in their own subscriptions, that does not mean those VMs exist in the same physical Azure data centre. Additionally, because of the distance there can be some additional latency introduced into zonal deployments. You should test the latency between your VMs to ensure that the latency meets performance targets. In most cases round-trip latency will be less than 1 millisecond, which supports synchronous data movement in features like Availability Groups. You can also deploy Azure SQL Database into Availability Zones.

https://docs.microsoft.com/en-us/azure/architecture/example-scenario/infrastructure/iaas-high-availability-disaster-recovery

SQL Server has several index types to support different types of workloads. At a high level, an index can be thought of as an on-disk structure that is associated with a table or a view, that enables SQL Server to more easily find the row or rows associated with the index key (which consists of one or more columns in the table or view), compared to scanning the entire table.

Clustered Indexes

A common DBA job interview question is to ask the candidate the difference between a clustered and nonclustered index, as indexes are a fundamental data storage technology in SQL Server. A Clustered Index is the underlying table, stored in sorted order based on the key value. There can only be one clustered index on a given table, because the rows can be stored in one order. A table without a clustered index is called a heap, and heaps are typically only used as staging tables. An important performance design principle is to keep your clustered index key as narrow as possible. When considering the key column(s) for your clustered index you should consider columns that are unique or that contain many distinct values. Another property of a good clustered index key is for records that are accessed sequentially, and are used frequently to sort the data retrieved from the table. Having the clustered index on the column used for sorting can prevent the cost of sorting every time that query executes, because the data will be already stored in the desired order.

When we say that the table is ‘stored’ in a particular order, we are referring to the logical order, not necessarily the physical, on-disk order. Indexes have pointers between pages, and the pointers help create the logical order. When scanning an index ‘in order’, SQL Server follows the pointers from page to page. Immediately after creating an index, it is most likely also stored in physical order on the disk, but after you start making modifications to the data, and new pages need to be added to the index, the pointers will still give us the correct logical order, but the new pages will most like not be in physical disk order.

https://azure.microsoft.com/en-us/blog/clustered-columnstore-index-in-azure-sql-database/

Proper storage configuration is important to the performance of your Azure Virtual Machines. SQL Server should run on premium disk storage or ultra disk for optimal performance. You can use the Resource Provider for SQL Server to automate the creation of your storage for you SQL Servers on Azure Virtual Machines. You can used Resource Governor to manage differing workloads in the same SQL Server.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices

A method to increase availability for Azure SQL Database is to use Active Geo-Replication. Active Geo-Replication creates a replica of the database in another region that is asynchronously kept up to date. That replica is also readable, similar to an AG in IaaS. Underneath the surface, Azure is using the same methods as an AG, which is why some of the terminology and functionality is similar(primary and secondary logical servers, read-only databases, etc.).

https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview

IQP is built into the query processor and can improve performance with no code changes.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-performance/9-intelligent-performance

Transparent Data Encryption (TDE) provides encryption for data at rest and is on by default for all new Azure SQL Database instances. It can be configured for all deployment options via a switch in the Azure portal, as shown here:

Advanced threat protection provides advanced security capabilities, such as vulnerability assessments, to help detect and remediate potential security problems with your databases. Threat protection also detects anomalous activities that indicate unusual and potentially harmful attempts to access or exploit your database.

It continuously monitors your database for suspicious activities, and provides immediate security alerts on potential vulnerabilities, SQL injection attacks, and anomalous database access patterns. Threat detection alerts provide details of the suspicious activity, and recommend action on how to investigate and mitigate the threat.

Auditing tracks database events and writes them to an audit log in your Azure storage account. Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that might indicate business concerns or suspected security violations.

SQL Database helps secure your data by providing encryption. For data in motion, it uses transport layer security. For data at rest, it uses transparent data encryption. For data in use, it uses always encrypted.

https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview

Transport Layer Security (TLS) is used to secure web transaction and other network communications.

https://www.cloudflare.com/learning/ssl/transport-layer-security-tls/

Transparent Data Encryption (TDE) is a technology employed by Microsoft, IBM and Oracle to encrypt database files. TDE offers encryption at file level. TDE solves the problem of protecting data at rest, encrypting databases both on the hard drive and consequently on backup media. It does not protect data in transit nor data in use.

https://en.wikipedia.org/wiki/Transparent_data_encryption

Azure Storage automatically encrypts your data when it is persisted to the cloud. Azure Storage encryption protects your data and to help you to meet your organizational security and compliance commitments.

https://docs.microsoft.com/en-us/azure/storage/common/storage-service-encryption

While some SQL Servers or Azure SQL managed instances only support one application’s databases (this configuration is commonly seen in mission critical applications), many servers support databases for multiple applications with differing performance requirements and different peak workload cycles. Balancing these differing requirements can be challenging to the administrator. One of the ways to balance server resources is to use Resource Governor, which was introduced to SQL Server 2008.

Resource Governor is a feature in SQL Server and Azure SQL managed instance that allows you to granularly control how much CPU, physical IO, and memory resources can be used by an incoming request from an application. Resource Governor is enabled at the instance level and allows you to define how connections are treated by using a classifier function, which subdivides sessions into workload group. Each workload group is configured to use a specific pool of system resources.

Resource Governor use cases

Resource Governor is used primarily in multi-tenant scenarios where a group of databases share a single SQL Server instance, and performance needs to be kept consistent for all users of the server. You can also use Resource Governor to limit the resources used by maintenance operations like consistency checks and index rebuilds, to try to guarantee sufficient resources for user queries during your maintenance windows.

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-classifier-function?view=sql-server-ver15

Resource pools

A resource pool represents physical resources available on the server. SQL Server always has two pools, default and internal, even when Resource Governor is not enabled. The internal pool is used by critical SQL Server functions and cannot be restricted. The default pool, and any resource pools you explicitly define, can be configured with limits on the resources it can use. You can specify the following limits for each non-internal pool:

• Min/Max CPU percent

• Cap of CPU percent

• Min/Max memory percent

• NUMA node affinity

• Min/Max IOPs per volume

With the exception of min/max CPU percent, all of the other resource pool settings represent hard limits and cannot be exceeded. Min/Max CPU percentage will only apply when there is CPU contention. For example, if you have a maximum of 70%, if there is available CPU cycles the workload may use up to 100%. If there are other workloads running, the workload will be restricted to 70%.

https://docs.microsoft.com/en-us/system-center/scom/plan-resource-pool-design?view=sc-om-2019

Workload group

A workload group is a container for session requests based on their classification by the classifier function. Like resource pools there are two built-in groups, default and internal, and each workload group can only belong to one resource pool. However, a resource pool can host multiple workload groups. All connections go into the default workload group, unless they passed into another user-defined group by the classifier function. And by default, the default workload group uses the resources assigned to the default resource pool.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-isolation

Classifier function

The classifier function is run at the time a connection is established to the SQL Server instance and classifies each connection into a given workload group. If the function returns a NULL, default, or the name of the non-existent workload group the session is transferred into the default workload group. Since the classifier is run at every connection, it should be tested for efficiency.

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function?view=sql-server-ver15

Understanding the SQL Server Proportional fill algorithm

When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.

The SQL Server component that is responsible for managing the data storage within the database files, called the SQL Server Storage Engine. It uses a fill mechanism that writes data to the database files depending on the amount of free space in each data file rather than writing in each file until it is full then moving to the second one sequentially. This data filling algorithm is called Proportional Fill Algorithm. In other words, the SQL Server Storage Engine will write more frequently to the files with more free space. For example, if the first data file has 10 MB free space and the second one has 20 MB, the storage engine will fill one extent to the first file and two extents to the second one. If auto-growth is enabled to the database files and the database files become full, the SQL Server Database Engine will expand the database files one at a time and write to that file, once the expanded file becomes full, SQL Server Database Engine will expand the second database file and so on.

In the Proportional Fill Algorithm, each database data file will be assigned with a ranking integer number to specify how many times this file will be skipped from the writing process to the next file depending on the free space of that file, this number is called the Skip Target where the minimum value equal to 1 means that a write process will be performed on that file. The Skip Target can be measured by dividing the number of free extents in the file with the largest free space amount by the number of free extents in the current file, as integer value. The larger the free space in the database data file, the smaller Skip Target value. To have one file to write on each loop, there should be minimum one data file with Skip Target value equal to 1. Each time a new database data file is added or removed, or 8192 extents is filled in the database filegroup, the Skip Target value will be calculated again. In this way, all database data files will become full approximately at the same time. The Skip Target calculation can be monitored by enabling the Trace Flag 1165.

https://www.sqlshack.com/understanding-sql-server-proportional-fill-algorithm/

Point-in-Time restore (PITR)

In Azure SQL Database and Azure SQL Managed Instance, you can perform a self-service restore. You can choose the exact point in time to which you want to restore and start the process by using the Azure portal, the PowerShell/Azure CLI, or REST APIs. Point in time restore (PITR) will create a new database (with a different name) in the same logical server. If you need to replace the original database with the PITR database, you have to rename both the original and the new database to return to a working condition. You won't need to update connection strings.

Retention for PITR varies between 1 and 35 days. By default, the retention period (for all service tiers and deployment options) is 7 days. In most deployment options and service tiers, you can configure the policy to be between 1 and 35 days, depending on your scenario's requirements. For example, you might need only 1 day for a test database, but you might choose the maximum of 35 days for a mission-critical database.

Long-Term Retention (LTR)

If 35 days isn't long enough to meet your organization's needs or compliance requirements, you can choose long-term retention (LTR). This option enables you to automatically create full database backups that are stored in RA-GRS storage for up to 10 years. For Azure SQL Database, LTR is generally available. For Azure SQL Managed Instance, LTR is available in a limited public preview.

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore?tabs=azure-portal

Azure SQL Managed Instance

Behind the scenes, for Azure SQL Managed Instance, Azure deploys a Dedicated Ring (sometimes called a virtual cluster) for your service. This architecture helps in providing security and native virtual network support.

Because of this architecture, deployment and scaling operations can take longer. For example, when you scale up or down, Azure deploys a new virtual cluster for you and then seeds it with your data. You can think of every instance as running on a single virtual machine.

Azure SQL Instance pools were introduced to help with the long deployment time. You can pre-deploy a "pool" of dedicated resources. Deploying into a pool and scaling within a pool are faster than traditional deployments. (And you get a higher packing density, because you can deploy multiple instances within a single VM.)

https://docs.microsoft.com/en-us/azure/devops/migrate/phase-rollout-with-rings?view=azure-devops