A Clustered Columnstore Index will provide the best performance for a Data Warehouse Fact Table.

Columnstore index is the preferred technology to run analytics queries in Azure SQL Databases. MS recently announced general availability if In-Memory technologies for all Premium databases. Similar to In-Memory OLTP, the columnstore index technology is available in premium databases.

The columnstore technology is available in two flavors:

• Clustered columnstore index (CCI) for DataMart analytics workloads

• Nonclustered columnstore index (NCCI) to run analytics queries on operational (i.e. OLTP) workload.

Refer to NCCI vs CCI for the differences between these two flavours of columnstore indexes.

The columnstore index can speed up the performance of analytics queries up to 100x while significantly reducing the storage footprint. The data compression achieved depends on the schema and the data, but we see around 10x data compression on average when compared to rowstore with no compression.

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

Typically, you look at these to verify the installation and the status of system databases and to check system objects in your database.

In the first result set, the system databases msdb, tempdb, and model are not listed. Only master and your user database are listed. This is because the master database for a database server for Azure SQL Database is not the same as the physical master database installed with SQL Server. In Azure SQL Managed Instance, you'll see the normal set of system databases, as with any SQL Server instance.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-deploy-configure/4-exercise-verify-database

Before starting the deployment in Azure, it's important to understand what the requirements are and how they map to offerings in Azure SQL. The following questions need answers:

• Deployment method: Azure Portal or command-line interface (CLI)?

• Deployment option: virtual machine (VM), database, elastic pool, managed instance, or instance pool?

• Purchasing model (Azure SQL Database only): DTU or vCore?

• Service tier (service-level objective): General Purpose, Business Critical, or Hyperscale?

• Hardware: Gen5, or something new?

• Sizing: number of vCores and Data max size?

https://docs.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview

Virtual machine families

When deploying to an Azure virtual machine, there are several series, or “families”, of virtual machine sizes that can be selected. Each series is a combination of memory, CPU, and storage that meets certain requirements. For example, the series that are compute optimized have a higher CPU to memory ratio. Having multiple options allows you to select an appropriate hardware configuration for the expected workload. The following six series each have various sizes available, the details of which are fully described in the Azure portal when you choose the option to select your VM size.

General purpose - These VMs provide a balanced ration of CPU to memory. This VM class is ideal for testing and development, small to medium-sized database servers, and web servers with a low to medium amount of traffic.

Compute optimized - Compute optimized VMs have a high CPU-to-memory ratio and are good for web servers with a medium amount of traffic, network appliances, batch processes, and application servers. These VMs can also support machine learning workloads that can't benefit from GPU-based VMs.

Memory optimized - These VMs provide high memory-to-CPU ratio. These VMs cover a broad range of CPU and memory options (all the way up to 4 TB of RAM) and are well suited for most database workloads.

Storage optimized - Storage optimized VMs provide fast, local, NVMe storage that is ephemeral. They are good candidates for scale-out data workloads such as Cassandra. It is possible to use them with SQL Server, however since the storage is ephemeral, you need to ensure you configure data protection using a feature like Always On Availability Groups or Log Shipping.

GPU - Azure VMs with GPUs are targeted at two main types of workloads—naturally graphics processing operations like video rendering and processing, but also massively parallel machine learning workloads that can take advantage of GPUs.

High performance compute - High Performance Compute workloads support applications that can scale horizontally to thousands of CPU cores. This support is provided by high-performance CPU and remote direct memory access (RDMA) networking that provides low latency communications between VMs.

The easiest way to see the sizing options within each series is through the Azure portal. From the blade for creating a VM, you can click the option to “Select Size” and see a list.

https://azure.microsoft.com/en-us/pricing/details/virtual-machines/series/

Database Configuration

Commonly, you configure an Azure SQL database with the T-SQL ALTER DATABASE and ALTER DATABASE SCOPED CONFIGURATION statements. Many of the configuration options for performance are available for Azure SQL. Consult the ALTER DATABASE and ALTER DATABASE SCOPED CONFIGURATION T-SQL reference for the differences between SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

An option that cannot be changed is the recovery model of the database. The default is full recovery. This ensures the database can meet Azure service-level agreements (SLAs). Therefore, minimal logging for bulk operations isn't supported. Minimal logging for bulk operations is supported for tempdb.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-performance/3-configure-maintain-performance

The estimated execution plan is stored in the plan cache.

When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Because of this, an estimated execution plan does not contain any runtime information, such as actual resource usage metrics or runtime warnings. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed, and displays the estimated rows flowing through the several operators in the plan.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-the-estimated-execution-plan?view=sql-server-ver15

Storage performance is a critical component of an I/O heavy application like a database engine. Azure offers a broad array of storage options and can even build your storage solution to meet your workload requirements.

Azure Storage is a highly scalable, secure storage platform that offers a range of solutions to meet the needs of many applications. The following types of storage support encryption at rest with either a Microsoft managed or a user-defined encryption key.

Blob Storage - Blob storage is what is known as object-based storage and includes cold, hot, and archive storage tiers. In a SQL Server environment, blob storage will typically be used for database backups, using SQL Server’s back up to URL functionality.

File Storage - File storage is effectively a file share that can be mounted inside a virtual machine, without the need to set up any hardware. SQL Server can use File storage as a storage target for a failover cluster instance.

Disk Storage - Azure managed disks offer block storage that is presented to a virtual machine. These disks are managed just like a physical disk in an on-premises server, except that they are virtualized. There are several performance tiers within managed disks depending on your workload. This type of storage is the most commonly used type for SQL Server data and transaction log files.

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

The goal of business continually planning exercises is to reduce the amount of time required to restore operations. This is done by minimizing the Recovery Time Objective (RTO).

https://www.techopedia.com/definition/24250/recovery-time-objective--rto

Understanding recovery time and recovery point objectives are crucial to your high availability and disaster recovery (HADR) plan as they are the foundation for any availability solution.

Recovery Time Objective

Recovery Time Objective (RTO) is the maximum amount of time available to bring resources online after an outage or problem. If that process takes longer than the RTO, there could be consequences such as financial penalties, work not able to be done, and so on. RTO can be specified for the whole solution, which includes all resources, as well as for individual components such as SQL Server instances and databases.

Recovery Point Objective

Recovery Point Objective (RPO) is the point in time to which a database should be recovered and equates to the maximum amount of data loss that the business is willing to accept. For example, suppose an IaaS VM containing SQL Server experiences an outage at 10:00 AM and the databases within the SQL Server instance have an RPO of 15 minutes. No matter what feature or technology is used to bring back that instance and its databases, the expectation is that there will be at most 15 minutes worth of data lost. That means the database can be restored to 9:45 AM or later to ensure minimal to no data loss meeting that stated RPO. There may be factors that determine if that RPO is achievable.

Defining Recovery Time and Recovery Point Objectives

RTOs and RPOs are driven by business requirements but are also based on various technological and other factors, such as the skills and abilities of the administrators (not just DBAs). While the business may want no downtime or zero data loss, that may not be realistic or possible for a variety of reasons. Determining your solution’s RTO and RPO should be an open and honest discussion between all parties involved.

One of the aspects crucial for both RTO and RPO is knowing the cost of downtime. If you define that number and the overall effect being down or unavailable has to the business, it is easier to define solutions. For example, if the business can lose $10,000 per hour or could be fined by a government agency if something could not be processed, that is a measurable way to help define RTO and RPO. Spending on the solution should be proportional to the amount, or the cost, of downtime. If your HADR solution costs $X, but you wind up only being affected for a few seconds instead of hours or days when a problem occurs, it has paid for itself.

From a non-business standpoint, RTO should be defined at a component level (for example, SQL Server) as well as for the entire application architecture. The ability to recover from an outage is only as good as its weakest link. For example, if SQL Server and its databases can be brought online in five minutes but it takes application servers 20 minutes to do the same, the overall RTO would be 20 minutes, not five. The SQL Server environment could still have an RTO of five minutes; it still will not change the overall time to recover.

RPO deals specifically with data and directly influences the design of any HADR solution as well as administrative policies and procedures. The features used must support both the RTO and RPOs that are defined. For example, if transaction log backups are scheduled every 30 minutes but there is a 15-minute RPO, a database could only be recovered to the last transaction log backup available which in a best case would be 30 minutes ago. This timing assumes no other issues and the backups have been tested and are known to be good. While it is hard to test every backup generated for each database in your environment, backups are just files on a file system. Without doing at least periodic restores, there is no guarantee they are good. Running checks during the backup process can give you some degree of confidence.

The specific features used, such as an Always On Availability Group (AG) or an Always On Failover Cluster Instance (FCI) will also affect your RTOs and RPOs. Depending on how the features are configured, IaaS or PaaS solutions may or may not automatically failover to another location, which could result in longer downtime. By defining RTO and RPO, the technical solution that supports that requirement can be designed knowing the allowances for time and data loss. If those wind-up being unrealistic, RTOs and RPOs must be adjusted accordingly. For example, if there is a desired RTO of two hours but a backup will take three hours to copy to the destination server for restoring, the RTO is already missed. These types of factors must be accounted for when determining your RTOs and RPOs.

There should be RTOs and RPOs defined for both HA and DR. HA is considered a more localized event that can be recovered from more easily. One example of high availability would be an AG automatically failing over from one replica to another within an Azure region. That may take seconds, and at that point, you would need to ensure that the application can connect after the failover. SQL Server’s downtime would be minimal. A local RTO or RPO may potentially be measured in minutes depending on the critical nature of the solution or system.

DR would be akin to bringing up a whole new data centre. There are lots of pieces to the puzzle; SQL Server is just one component. Getting everything online may take hours or longer. This is why the RTOs and RPOs are separate. Even if many the technologies and features used for HA and DR are the same, the level of effort and time involved may not be.

All RTOs and RPOs should be formally documented and revised periodically or as needed. Once they are documented, you can then consider what technologies and features you may use for the architecture.

https://docs.microsoft.com/en-us/azure/architecture/framework/resiliency/backup-and-recovery

Connection resiliency should be built into your application to handle transient failures, like failover or database resizing.

https://docs.microsoft.com/en-us/azure/mysql/app-development-best-practices

Database resources are reduced to a lower service tier to reduce costs.

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