The address 10.0.0.2 will be returned if virtual network rules or private endpoints are enabled.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-connectivity/ba-p/1235573

Azure Key Vault is a tool used for storing and accessing secrets. Whether they be passwords, certificates or keys, Key Vault acts as a secure area for those secrets to be accessed in a secure fashion, typically programmatically. Key Vault data has its own RBAC policies, separate from the Azure subscription. This means someone who is in the role of subscription admin will not have access to the Key Vault unless explicitly granted.

SQL Server, either within an Azure Virtual Machine or on-premises, supports using Azure Key Vault to store certificates for features such as Transparent Data Encryption, Backup Encryption, or Always Encrypted. While this configuration is complex in an on-premises environment, is easy using the SQL VM Resource Provider as shown in the image below.

In order to configure the Azure Key Vault integration, you need to set the Key Vault URL, the Principal name, the Principal secret, and the name of the credential. This task can be done at VM creation or to an existing VM.

Configuring SQL Server to connect to Azure Key Vault first requires creating a normal SQL Server Login within the instance. Next a Credential needs to be created and mapped to the login. For the identity of the credential, the name of the key vault should be used. For the secret of the credential, use the application ID from Azure Key Vault. Once the credential is created, an asymmetric key can be created within the Azure Key Vault. An asymmetric key can then be created within the SQL Server database. The key in database can be mapped to the Azure Key Vault asymmetric key using the CREATE ASYMMETRIC KEY command with the FROM PROVIDER syntax. Once an asymmetric key is created within the database, that key can be used for TDE, or Backup Encryption or Always Encrypted.

https://docs.microsoft.com/en-us/azure/key-vault/general/basic-concepts

As data is inserted, updated, and deleted from indexes the logical ordering in the index will no longer match the physical ordering inside of the pages, and between the pages, making up the indexes. Also, over time the data modifications can cause the data to become scattered or fragmented in the database. This fragmentation can degrade query performance when the database engine needs to read additional pages in order to locate needed data.

The SQL Server and Azure SQL platforms offer DMVs that allow you to detect fragmentation in your objects using the following DMVs. The most commonly used DMVs for this purpose are: sys.dm_db_index_physical_stats, for b-tree indexes, and sys.dm_dm_colum_store_row_group_physical_stats, for columnstore indexes.

There are two options for index maintenance: reorganization, and rebuilding.

• Reorganization defragments an index by physically reordering the leaf-level index pages to match the logical sorted order of the leaf nodes and compacts the index pages based on the index’s fill factor setting.

• Rebuilding an index drops and recreates the pages of the index. The typical guidelines for deciding to rebuild versus reorganizing an index is that when fragmentation is greater than 30% that the index should be rebuilt.

However, the actual values vary from case to case. If an index is used heavily for scan operations, removing excess pages may help significantly, however there will be limited benefits for seek operations. Reorganization has the benefit of being an online activity, whereas rebuilding requires a special ONLINE option.

Indexes that have less than 5% fragmentation do not need to be defragmented, because the cost of defragmentation is greater than the performance benefits that could be gained. Whenever you are considering either rebuilding or reorganizing, you should verify that doing so does provide performance improvements.

SQL Server 2017 introduced resumable index maintenance operations. This option allows index maintenance operations to be paused, or take place in a time window, and be resumed later. A good example of where to use resumable index operations is to reduce the impact of index maintenance in a busy production environment, as you can perform rebuild operations during a specific maintenance window giving you more control over the process.

One other thing to note is that index rebuilds cause the statistics on the index to be updated, which can further help performance. Index reorganization does not update statistics.

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

Beyond proper indexing, index maintenance is in important part of performance, especially for queries which scan tables or indexes. The query optimizer utilizes statistical information from the indexes to attempt to build the most optimal execution plan. While this execution plan is usually “good enough” having healthy indexes and statistics will ensure that any given plan will perform at optimal efficiency. Index maintenance should be performed regularly as data in your databases changes over time. You could change your index maintenance strategy based on the frequency of modifications to your data.

Rebuild and reorganize

Index fragmentation occurs when logical ordering within index pages does not match the physical ordering. Pages can out of order during routine data modification statements such as UPDATE, DELETE, and INSERT. Fragmentation can introduce performance issues because of the additional I/O that is required to locate the data that is being referenced by the pointers within the index pages.

A reorganization of an index is an online operation that will defragment the leaf level of the index (both clustered and nonclustered). This defragmentation process will physically reorder the leaf-level pages to match the logical order of the nodes from left to right. During this process, the index pages are also compacted based on the configured fillfactor value.

A rebuild can be either online or offline depending on the command executed or the edition of SQL Server being utilized. An offline rebuild process will drop and re-create the index itself. If you can do so online, a new index will be built in parallel to the existing index. Once the new index has been built, the existing one will be dropped and then the new one will be renamed to match the old index name. Keep in mind that the online version will require additional space as the new index is built in parallel to the existing index.

The common guidance for index maintenance is:

• > 5% but < 30% Reorganize the index

• > 30% Rebuild the index

Use these numbers as general recommendations, but not as hard and fast rules. Depending on your workload and data, you may need to be more aggressive, or in some cases you may be able to defer index maintenance for databases that mostly perform queries that seek specific pages.

Starting with SQL Server 2017, Microsoft introduced the ability to have resumable rebuild operations. This option provides more flexibility in controlling how much of an impact a rebuild operation might impose on a given instance. With SQL Server 2019, the ability to control an associated maximum degree of parallelism was introduced further providing more granular control to database administrators.

SQL Server on an Azure Virtual Machine

With SQL Server being installed within an Azure virtual machine you have access to scheduling services such as the SQL Agent or the Windows Task Scheduler. These automation tools can assist in keeping the amount of fragmentation within indexes to a minimum. With larger databases, a balance between a rebuild and a reorganization of indexes must be found to ensure optimal performance. The flexibility provided by SQL Agent or Task Scheduler allows you to run custom jobs.

Azure SQL Database

Due to the nature of Azure SQL Database, you do not have access to SQL Server Agent nor Windows Task Scheduler. Without these services, index maintenance must be controlled from outside of the database.

There are three ways to manage maintenance operations:

• Azure Automation Runbooks

• SQL Agent Job from SQL Server in an Azure Virtual Machine

• Azure SQL Elastic Jobs

Azure SQL Managed Instance

As with SQL Server on an Azure Virtual Machine, you can schedule jobs on an Azure SQL Managed Instance via the SQL Server Agent. Using the SQL Server Agent provides flexibility to execute code specifically designed to reduce fragmentation within the indexes in the database.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787

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.

Serverless

The name “Serverless” can be a bit confusing as you still deploy your Azure SQL Database to a logical server, to which you connect. Azure SQL Database serverless is a compute tier that will automatically scale up or down the resources for a given database based on demand. If the workload no longer requires compute resources, the database will become “paused” and you will not be charged during the period when the database is in this state. When a connection attempt is made, the database will “resume” and become available. Resuming the database is not instantaneous.

Another difference between serverless and the normal vCore model of Azure SQL Database is that with serverless you can specify a minimum and maximum number of vCores. Memory and I/O limits are proportional to the range that is specified.

The image above shows the configuration screen for a serverless database in the Azure portal. You have the option to select a minimum as low as half of a vCore and a maximum as high as 16 vCores. You should note that databases that are not deployed as serverless are referred to as “provisioned”.

The setting to control pausing is referred to as the autopause delay and has a minimum value of 60 minutes and a maximum value of seven days. If the database has been idle for that period of time, it will then pause. Once the database has been inactive for the specified amount of time, it will be paused until a subsequent connection is attempted. Any applications using serverless should be configured to handle connection errors and include retry logic, as connecting to a paused database will generate a connection error.

Serverless is not fully compatible with all features in Azure SQL Database as some features are running background processes all the time. These features include:

• Geo-replication

• Long-term backup retention

• A job database in elastic jobs

• The sync database in SQL Data Sync (Data Sync is a service that replicates data between a group of databases)

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

Backing up and restoring Azure SQL Database is different than IaaS.

Backups are generated automatically for Azure SQL Database. A full backup is created once a week, a differential every 12 hours, and transaction log backups every 5 – 10 minutes. All backups are located in read-access, geo-redundant (RA-GRS) blobs replicated to a datacentre that is paired based on Azure rules. That means backups are safe from an outage in a single data centre.

Backup policies can be configured per database as shown in the image below. Azure SQL Database can assist you to be compliant with mandatory backups for regulatory purposes with retention policies.

If the server containing the database is deleted, all backups will be deleted at the same time and there is no way to recover them. If the server is not deleted but the database is, the database can be restored normally.

Both Azure SQL Database and Azure SQL Database Managed Instance have a feature called Accelerated Database Recovery (ADR). Its purpose is to decrease the time it takes to deal with long running transactions so they do not impact the recovery time. Although ADR was developed for Azure and was originally an Azure-based feature, ADR was implemented in SQL Server 2019 as well.

Backups generated for Azure SQL Database cannot be used with Azure SQL Database Managed Instance or any version of SQL Server.

Database backup and restore for Azure SQL Database Managed Instance

By default, Azure creates backups for databases in Azure SQL Database Managed Instance and functions like Azure SQL Database.

You can also manually back up and restore databases with Azure SQL Database Managed Instance using the same backup to URL/restore from URL functionality found in SQL Server covered earlier. That requires the use of credentials to access Azure Blob Storage.

There is one major difference: for full backups, you can only generate a COPY_ONLY backup since Azure SQL Database Managed Instance is maintaining the log chain.

Backups generated by Azure SQL Database Managed Instance cannot be used to restore a database on SQL Server or Azure SQL Database.

Database backup and restore for Azure Database for MySQL

Backups of the data files and transaction log are automatically created for Azure Database for MySQL in either locally- or geo-redundant storage. The backups are encrypted with a default retention of seven days, and a maximum of 35. One nice thing about the backups is that there is no cost; if a server is provisioned with 100 GB of storage, you get the equivalent amount of backup space included with what you are already paying. If backup storage exceeds the 100 GB, there will be a charge.

Restores can be done either to a point in time or as a geo-restore, which recovers the database in another region if geo-redundant storage was configured.

Similar to Azure SQL Database and Azure SQL Database Managed Instance, these backups can only be used by Azure Database for MySQL and not a standard installation of MySQL.

Database backup and restore for Azure Database for PostgreSQL

The backup and restore situation for Azure Database for PostgreSQL is similar to that of MySQL. Backups occur automatically and both data files and the transaction log are backed up. The retention and encryption are the same as are the options for local- or geo-redundancy, cost, and restore options. The only difference is that depending on the supported maximum storage size, Azure will create full and differential backups (maximum of 4 TB) or snapshot backups (up to 16 TB).

Similar to all other Azure PaaS offerings, these backups can only be used by Azure Database for PostgreSQL and not a standard installation of PostgreSQL.

https://docs.microsoft.com/en-us/sql/azure-data-studio/tutorial-backup-restore-sql-server?view=sql-server-ver15

Azure Site Recovery is a low-cost solution that will perform block level replication of your Azure virtual machine.

As an organization you need to adopt a business continuity and disaster recovery (BCDR) strategy that keeps your data safe, and your apps and workloads online, when planned and unplanned outages occur.

Azure Recovery Services contributes to your BCDR strategy:

• Site Recovery Service: Site Recovery helps ensure business continuity by keeping business apps and workloads running during outages. Site Recovery replicates workloads running on physical and virtual machines (VMs) from a primary site to a secondary location. When an outage occurs at your primary site, you fail over to secondary location, and access apps from there. After the primary location is running again, you can fail back to it.

• Backup Service: The Azure Backup service keeps your data safe and recoverable.

Site Recovery can manage replication for:

• Azure VMs replicating between Azure regions.

• On-premises VMs, Azure Stack VMs, and physical servers.

https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-overview

The Query Store can be thought of as a flight data recorder for SQL Server. It collects and permanently stores and aggregates performance information In older versions of SQL Server, this information was either transient or not collected at all. The Query Store captures run time information such as duration, logical I/O, CPU usage among other metrics for query executions in a specific user database. It also captures the estimated execution plan for each execution, which can allow you to quickly detect an execution plan that has regressed in performance. The catalogue views that make up the Query Store are stored in the user database, and are shown in this image:

There are two types of data being captured. One is the data about the query itself (number of executions, the plan it used in execution, the query text) and the other is the performance information, which collects runtime statistics for each execution of the query. Each query execution has a large number of runtime metrics captured for each execution of the query.

Query Store Reports

SQL Server Management Studio offers a number of out of the box reports that allow you to quickly gather information from the Query Store. The current list of built-in reports is shown in the image below. You can reach this menu by using SQL Server Management Studio, clicking on a user database in Object Explorer, and clicking on the Query Store folder to expand it.

Most of these reports can be filtered by the following execution metrics:

• CPU Milliseconds

• Duration

• Logical Reads

• Logical Writes

• Physical Reads

• CLR Time

• Degree of Parallelism

• Memory Consumption

• Row Count

• Log Memory Used

• TempDB Memory Use

• Wait Time

In addition to these metrics, you can choose a statistic to further refine your data.

• Average

• Maximum

• Minimum

• Standard Deviation

• Total

You can further filter based on a time interval, which allows you to retroactively troubleshoot a performance problem that may have happened several days ago. The built-in reports include:

Regressed Queries - This report shows any queries where execution metrics have degraded in the period of time of interest (last hour, day, and week). This report is useful for evaluating the impact of minor or major changes in server configuration or database schema.

Overall Resource Consumption - This report allows you to quickly observe the most impactful queries in your database. This report allows click through to the “Top Resource Consuming Queries” report, which allows you to gather execution plan information.

Top Resource Consuming Queries - This report shows the query and query plan for the most impactful queries in a database for a time period. This data allows you to observe if a query has multiple execution plans, and whether or not those plans have high variability in performance.

Queries with Forced Plans — This report contains information about plan forcing, and any plan forcing failures (a situation where a forced execution plan was not honoured).

Queries with High Variation - This report showcases queries that have a high degree of variance between executions and can be sorted by any of the above execution metrics.

Query Wait Statistics - This report allows you to see waits statistics aggregated, and drill-through to get further information on queries that spent the most time waiting. Note that this wait information is aggregated and not as detailed as what you might observe in the sys.dm_os_wait_stats DMV.

Tracked Queries - This report is filtered by query_Id and allows you to view the performance of a specific query and its execution plans. You can manually enter a query_id or you can add queries from the regressed or top resource consuming query reports. The query_id can be captured from the catalogue view sys.query_store_query.

https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15

Always On Failover Cluster Instances

An FCI is configured when SQL Server is installed. A standalone instance of SQL Server cannot be converted to an FCI. The FCI is assigned a unique name as well as an IP address that is different from the underlying servers, or nodes, participating in the cluster. The name and IP address must also be different from the underlying cluster mechanism.

Applications and end users would use the unique name of the FCI for access. This abstraction enables applications to not have to know where the instance is running. One major difference between Azure-based FCIs versus on-premises FCIs, is that for Azure, an internal load balancer (ILB) is required. The ILB is used to help ensure applications and end users can connect to the FCI’s unique name.

When an FCI fails over to another node of a cluster, whether it is initiated manually or happens due to a problem, the entire instance restarts on another node. That means the failover process is a full stop and start of SQL Server. Any applications or end users connected to the FCI will be disconnected during failover and only applications that can handle and recover from this interruption can reconnect automatically.

Upon starting up on the other node, the instance goes through the recovery process. The FCI will be consistent to the point of failure, so technically there will be no data loss but any transactions that need to be rolled back will do so as part of recovery. As noted above, because this is instance-level protection, everything necessary (logins, SQL Server Agent jobs, etc.) is already there so business can continue as usual once the databases are ready.

FCIs require one copy of a database, but that is also its single point of failure. To ensure another node can access the database, FCIs require some form of shared storage. For Windows Server-based architectures, this can be achieved via an Azure Premium File Share, iSCSI, Azure Shared Disk, Storage Spaces Direct (S2D), or a supported third-party solution like SIOS DataKeeper. FCIs using Standard Edition of SQL Server can have up to two nodes. FCIs also require the use of Active Directory Domain Services (AD DS) and Domain Name Services (DNS), so that means AD DS and DNS must be implemented somewhere in Azure for an FCI to work.

Using Windows Server 2016 or later, FCIs can use Storage Replica to create a native disaster recovery solution for FCIs without having to use another feature such as log shipping or Availability Groups (AGs).

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

The Auto Update Statistics Asynchronously option allows for statistics to be updated during query execution.

Automatic statistics update – synchronous vs. asynchronous

By default, the SQL Server database engine updates statistics automatically as needed to reflect changes in the underlying data, and help the query optimizer generate more optimal plans. When a query plan is compiled, if existing statistics are considered out-of-date (stale), new statistics are collected and written to the database metadata. By default, this happens synchronously with query execution, therefore the time to collect and write new statistics is added to the execution time of the query being compiled.

While up-to-date statistics often improve query plan quality, the extra time added to some query executions due to statistics update may be undesirable, particularly in transactional workloads with short queries, where updating statistics may take longer than query execution itself. For this reason, the SQL Server database engine also supports an option to update statistics asynchronously. When the database option AUTO_UPDATE_STATISTICS_ASYNC is set to ON, query optimizer proceeds to compile and execute the query even if statistics are considered stale; however, stale statistics are then updated on a background thread asynchronously, so that future query executions starting after this asynchronous process has completed can benefit from up-to-date statistics.

https://techcommunity.microsoft.com/t5/azure-sql/improving-concurrency-of-asynchronous-statistics-update/ba-p/1441687