You can use Azure Service Health to monitor the availability of a general region or service but not your specific database or instance availability.

https://azure.microsoft.com/en-us/features/service-health/

A baseline is a collection of data measurements that helps you understand the normal “steady state” of your application or server’s performance. Having the data collected over time allows you to identify changes from the normal state. Baselines can be as simple as a chart of CPU utilization over time, or complex aggregations of metrics to offer granular level performance data from specific application calls. The granularity of your baseline will depend on the criticality of performance of your database and application.

With any type of application workload, it is imperative to establish a working baseline. A baseline will help you identify if an ongoing issue should be considered within normal parameters or has exceeded given thresholds. Without a baseline, every issue encountered could be considered normal and therefore not require any additional intervention.

Correlating SQL Server and operating system performance

When deploying SQL Server on an Azure virtual machine, it’s critical to correlate the performance of SQL Server with the performance of the underlying operating system. If you are using Linux as the operating system, you will need to install InfluxDB, Collectd, and Grafana to capture data similar to Windows Performance Monitor. These services collect data from SQL Server and provide a graphical interface to review the data. Utilizing these tools on Linux or Performance Monitor on Windows can be used in conjunction looking at SQL Server-specific data such as SQL Server wait statistics. Using these tools together will allow you to identity bottlenecks in hardware or code. The following Performance Monitor counters are a sampling of useful Windows metrics, and can allow you to capture a good baseline for a SQL Server workload:

Processor(_Total)% Processor Time - This counter measures the CPU utilization of all of the processors on the server. It is a good indication of the overall workload, and when used in conjunction with other counters, this counter can identify problems with query performance.

Paging File(_Total)% Usage - In a properly configured SQL Server, memory should not page to the paging file on disk. However, in some configurations you may have other services running that consume system memory and lead to the operating system paging memory to disk resulting in performance degradation.

PhysicalDisk(_Total)\Avg. Disk sec/Read and Avg. Disk sec/Write - This counter provides a good metric for how the storage subsystem is working. Your latency values in most cases should not be above 20 ms, and with Premium Storage you should see values less than 10 ms.

System\Processor Queue Length - This number indicates the number of threads that are waiting for the time on the processor. If it is greater than zero, it indicates CPU pressure, indicating your workload could benefit from more CPUs.

SQLServer:Buffer Manager\Page life expectancy - Page life expectancy indicates how long SQL Server expects a page to live in memory. There is no proper value for this setting. Older documentation refers to 300 seconds as proper, but that was written in a 32-bit era when servers had far less RAM. You should monitor this value over time, and evaluate sudden drops. Such drops in the counter's value could indicate poor query patterns, external memory pressure (for example, the server running a large SSIS package) or could just be normal system processing like running a consistency check on a large database.

SQLServer:SQL Statistics\Batch Requests/sec - This counter is good for evaluating how consistently busy a SQL Server is over time. Once again there is no good or bad value, but you can use this counter in conjunction with % Processor time to better understand your workload and baselines.

SQLServer:SQL Statistics\SQL Compilations/sec and SQL Re-Compilations/sec - These counters will be updated when SQL Server has to compile or recompile an execution plan for a query because there is no existing plan in the plan cache, or because a plan was invalidated because of a change. Recompiles can indicate T-SQL with recompile query hints, or be indicative of memory pressure on the plan cache caused by either many ad-hoc queries or simple memory pressure.

These counters are just a sample of the available performance monitor counters that are available to you. While the above counters provide a good baseline of performance, you may need to examine more counters to identify specific performance problems.

Wait statistics

When a thread is being executed and is forced to wait on an unavailable resource, SQL Server keeps track of these metrics. This information is easily identifiable via the dynamic management view (DMV) sys.dm_os_wait_stats. This information is important to understanding the baseline performance of your database, and can help you identify specific performance issues both with query execution and hardware limitations. Identifying the appropriate wait type and corresponding resolution will be critical for resolving performance issues. Wait statistics are available across the Azure SQL platform.

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

Azure SQL Database

When you're securing your network for Azure SQL Database, you have four main choices:

• Allow access to Azure services

• Use firewall rules

• Use virtual network rules

• Use Azure Private Link

In addition to these main choices, you have the opportunity to block all public access (only with Private Link) and the option to force a minimum Transport Layer Security (TLS) version. The least secure method, but the easiest to configure, is to allow access to Azure services. The most secure method is to use Private Link.

Allow access to Azure services

During the deployment of Azure SQL Database, you have the option to set Allow Azure services and resources access to this server to Yes. If you choose this option, you're allowing any resource from any region or subscription the possibility to access your resource. This option makes it easy to get up and running and get Azure SQL Database connected to other services, such as Azure Virtual Machines, Azure App Service, or even Azure Cloud Shell, because you're allowing anything that comes through Azure to have the potential to connect.

Virtual network rules

If you want to use only firewall rules, setting this up can be complicated. It means that you'll have to specify a range of IP addresses for all your connections, which can sometimes have dynamic IP addresses. A much easier alternative is to use virtual network rules to establish and manage access from specific networks that contain VMs or other services that need to access the data.

If you configure access from a virtual network with a virtual network rule, any resources in that virtual network can access the Azure SQL Database logical server. This can simplify the challenge of configuring access to all static and dynamic IP addresses that need to access the data. By using virtual network rules, you can specify one or more virtual networks, encompassing all the resources within them. You can also start to apply virtual network technologies to connect networks across regions in both Azure and on-premises.

Private Link for an Azure SQL Database instance

You've seen how to configure the most secure network by using your database in Azure SQL Database with the public endpoint, which is similar to the way that your Azure SQL managed instance is deployed. This method of securing a database in SQL Database has been used for years. However, in 2019, Azure began moving toward a concept of a private link, which is more like the way that Azure SQL Managed Instance is deployed. With Private Link, you can connect to your database in SQL Database and several other platform as a service offerings by using a private endpoint. This means that it has a private IP address within a specific virtual network.

An Azure SQL managed instance

Although deploying an Azure SQL managed instance differs from deploying a database in SQL Database, it's easy to translate networking functionality at a high level from one to the other. For an Azure SQL managed instance, either before or during deployment, you must create a specific subnet, or logical grouping within a virtual network, with several requirements to host the managed instances. After they're deployed, they're already configured similar to a private endpoint in a database in SQL Database. By using standard networking practices, you must enable access to the virtual network where the managed instance lives. By default, you have a private endpoint and relatively private DNS hierarchy.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-secure-data/2-security-capabilities

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.

Nonclustered indexes

Nonclustered indexes are a separate structure from the data rows. A nonclustered index contains the key values defined for the index, and a pointer to the data row that contains the key value. You can add additional nonkey columns to the leaf level of the nonclustered index to cover more columns using the included columns feature in SQL Server. You can create multiple nonclustered indexes on a table.

An example of when you need to add an index or add columns to an existing nonclustered index is shown below:

The query plan indicates that for each row retrieved using the index seek, additional data will need to be retrieved from the clustered index (the table itself). There is a nonclustered index, but it only includes the product column. If you add the other columns in the query to a nonclustered index as shown below, you can see the execution plan change to eliminate the key lookup.

The index created above is an example of a covering index, where in addition to the key column you are including additional columns to cover the query and eliminate the need to access the table itself.

Both nonclustered and clustered indexes can be defined as unique, meaning there can be no duplication of the key values. Unique indexes are automatically created when you create a PRIMARY KEY or UNIQUE constraint on a table.

The focus of this section is on b-tree indexes in SQL Server—these are also known as row store indexes. The general structure of a b-tree is shown below:

Each page in an index b-tree is a called an index node, and the top node of b-tree is called the root node. The bottom nodes in an index are called leaf nodes and the collection of leaf nodes is the leaf level.

Index design is a little bit art and a little bit science. A narrow index with few columns in its key requires less time to update and has lower maintenance overhead; however it may not be useful for as many queries as a wider index that includes more columns. You may need to experiment with several indexing approaches based on the columns selected by your application’s queries. The query optimizer will generally choose what it considers to be the best existing index for a query; however, that does not mean that there is not a better index that could be built.

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?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.

Backups

One of the most important features of the Platform as a Service offering is backups. In this case, backups are performed automatically without any intervention from you. Backups are stored in Azure blob geo-redundant storage and by default are retained for between 7 and 35 days, based on the service tier of the database.

Basic and vCore databases default to seven days of retention, and on the vCore databases this value can be adjusted by the administrator. The retention time can be extended by configuring long-term retention (LTR), which would allow you to retain backups for up to 10 years. In order to provide redundancy, you are also able to use read-accessible geo-redundant blob storage.

This storage would replicate your database backups to a secondary region. It would also allow you to read from that secondary region if needed. It is worth stating that manual backups of databases are not permitted and the platform will deny any request to do so.

Database Backups are taken on a given schedule:

• Full – Once a week

• Differential – Every 12 hours

• Log – Every 5-10 minutes depending on transaction log activity

This backup schedule should meet the needs of most recovery point/time objectives (RPO/RTO) however each customer should evaluate whether they meet your business requirements.

If the need to restore a database arises, there are several options available. Due to the nature of Platform as a Service, you cannot manually restore a database using conventional methods, such as issuing the T-SQL command RESTORE DATABASE.

Regardless of which restore method is implemented, it is not possible to restore over an existing database. If a database needs to be restored, the existing database must be dropped or renamed prior to initiating the restore. Furthermore, keep in mind that depending on the platform service tier, restore times could fluctuate. It is recommended that you test the restore process to obtain baseline metrics on how long a restore could potentially take.

The available restore options are:

Restore using the Azure portal – Using the Azure portal you have the option of restoring a database to the same Azure SQL Database server, or you can use the restore to create a new database on a new server in any Azure region.

Restore using scripting Languages – Both PowerShell and Azure CLI can be utilized in order to restore a database.

https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database

In General Purpose, tempdb is on direct-attached SSD, but the data and log files are in Azure Premium Storage.

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

Page Life Expectancy (PLE) is how long the engine expects a given page to live in memory.

https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-performance-baseline

Performance counters in Windows and Linux provide insight into the performance of hardware components, operating systems, and applications. Azure Monitor can collect performance counters from Log Analytics agents at frequent intervals for Near Real Time (NRT) analysis in addition to aggregating performance data for longer term analysis and reporting.

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-performance-counters

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.

Azure SQL Database is available in three different deployment options:

• A single database – A single database that is billed and managed on a per database level

• Elastic Pools – A group of databases that are managed together and share a common set of resources

• Hyperscale – a single database offering that allows databases to scale much beyond the 4-TB limit of an Azure SQL Database

Even in the cloud all services are backed by physical hardware. The Azure SQL

Database allows you to choose from two different purchasing models:

• Database Transaction Unit (DTU) – DTUs are calculated based on a formula combining compute, storage, and I/O resources.

• vCore – The vCore model allows you to purchase a specified number of vCores based on your given workloads. vCore is the default purchasing model when purchasing Azure SQL Database resources. vCore databases have a specific relationship between the number of cores and the amount of memory and storage provided to the database.

Azure SQL Database also includes:

• Automatic backups

• Automatic patching

• Built-in high availability

• SQL Server feature enhancements without the need to upgrade software

https://www.pass.org/PASS-Blog/choose-the-right-azure-sql-database-deployment-options-for-your-solution

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 failover clustering feature

Before a WSFC can be configured, the underlying Windows feature must be enabled on every node that will participate in the WSFC.

This can be done in one of two ways:

• Server Manager

• PowerShell.

In Server Manager, Failover Clustering must be enabled in the Add Roles and Features Wizard.

To enable the feature via PowerShell, use the following command, which will also install the utilities, which will be used to administer a WSFC:

1. PowerShell
2. Install-WindowsFeature Failover-Clustering -IncludeManagementTools

Once the feature is installed on the servers targeted as WSFC nodes, you must then validate the configuration.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview