dependsOn allows you to build a dependency structure into your template.

https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/overview

High Availability

One of the major benefits of cloud computing is that platform high availability is part of the architecture. Azure provides a high-level of built hardware, storage, and networking redundancy. High availability for a system is typically measured as a percentage of uptime per year. In the table below, you can see what those numbers translate into in terms of time.

A single Azure Virtual Machine provides three nines (99.9%) of high availability when used in conjunction with Azure managed storage. Three nines means that the service guarantees the availability of the virtual machine up to 99.9% of the time, which translates into a downtime of no more than 8.77 hours each year.

In addition to the default number of nines, there are additional features that you can include with your SQL Server in Azure virtual machine to ensure that you achieve the maximum amount of up time.

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

Azure SQL Database allows you to track and analyze the changes to your data using a feature called Temporal Tables. This feature requires that the tables themselves be converted to be temporal, which means the table will have special properties and will also have a corresponding history table. The Temporal Table feature allows you to use the history table to recover data that may have been deleted or updated. Recovering data from the history table is a manual process involving Transact-SQL, but could be helpful in certain scenarios such as if a user accidentally deletes important data that the business needs.

https://docs.microsoft.com/en-us/azure/azure-sql/temporal-tables

Backup to URL requires an Azure storage account and uses the Azure Blob Storage Service.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

The Role of Firewalls

Firewalls are used to prevent unauthorized users from accessing the protected resources. Each Azure SQL Database maps to a public IP address which is hosted by Microsoft. Each Azure region will have one or more public IP addresses where you can reach your database gateway, which will then take you to your database. In order to protect your database and your data, Azure provides built-in firewalls to limit access.

In Azure SQL Database there are two sets of firewall rules, Server Level Firewall Rules and Database Level Firewall Rules. Both server and database level firewalls use IP Address rules instead of SQL Server Logins. This allows all users at the same public IP Address to access the SQL Server. At most companies this will be the outbound IP address of the company.

Server level firewalls are configured to allow users to connect to the master database and all databases on the instance. Database level firewalls are used to grant or block specific IP Addresses from accessing specific databases.

Server level firewall rules can be configured using the Azure portal or using the sp_set_firewall_rule stored procedure from within the master database. Database level firewall rules are configured through T-SQL only using the sp_set_database_firewall_rule stored procedure from within the user database. Upon connection, Azure SQL Database will look first for a server level firewall rule in the master database and then a database level firewall rule, if the connection string specified a database name.

If either of these exist, the connection will be completed. If neither exist and the user is connecting through SQL Server Management Studio or Azure Data Studio, if the user authenticates to the database, they will be prompted to create a firewall rule as shown below.

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

Use SQL Audit and Data Classification in combination. In the audit log, you can track access to sensitive data specifically, and you can view information such as the data that was accessed and its sensitivity label.

Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

Auditing also:

• Helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

• Enables and facilitates adherence to compliance standards, although it doesn't guarantee compliance.

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

Data Discovery & Classification is built into Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It provides advanced capabilities for discovering, classifying, labelling, and reporting the sensitive data in your databases.

Your most sensitive data might include business, financial, healthcare, or personal information. Discovering and classifying this data can play a pivotal role in your organization's information-protection approach. It can serve as infrastructure for:

• Helping to meet standards for data privacy and requirements for regulatory compliance.

• Various security scenarios, such as monitoring (auditing) access to sensitive data.

• Controlling access to and hardening the security of databases that contain highly sensitive data.

https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

Azure SQL Database is the only deployment option with a possible SLA of 99.995 percent. The customer can obtain that SLA by using the Business Critical service tier and enabling availability zones.

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

SQL Server high availability and disaster recovery (HADR) architectures in Azure.

Disaster Recovery – Multi-Region or Hybrid Always On Availability Group

If you are using AGs, one option is to configure the AG across multiple Azure regions or potentially as a hybrid architecture. This means that all nodes which contain the replicas participate in the same Windows Server Failover Clustering cluster (WSFC). This assumes good network connectivity, especially if this is a hybrid configuration. One of the biggest considerations would be the witness resource for the WSFC. This architecture would require AD DS and DNS to be available in every region and potentially on premises as well if this is a hybrid solution. The image below shows what a single AG configured over two locations looks like using Windows Server.

Why is this architecture worth considering?

• This architecture is a proven solution; it is no different than having two data centres today in an AG topology.

• This architecture works with Standard and Enterprise editions of SQL Server.

• AGs naturally provide redundancy with additional copies of data.

• This architecture makes use of one feature that provides both HA and D/R

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

The path most DBAs take to troubleshoot query performance is to first identify the problematic query (typically the query consuming the highest amount of system resources), and then retrieve that query’s execution plan. There are two scenarios. One is that the query consistently performs poorly. Consistent poor performance can be caused by a few different problems, including hardware resource constraints (though this situation typically will not affect a single query running in isolation), a suboptimal query structure, database compatibility settings, missing indexes, or poor choice of plan by the query optimizer. The second scenario is that the query performs well for some executions, but not others. This problem can be caused by a few other factors, the most common being data skew in a parameterized query that has an efficient plan for some executions, and a poor one for other executions. The other common factors in inconsistent query performance are blocking, where a query is waiting on another query to complete in order to gain access to a table, or hardware contention.

Hardware Constraints

For the most part, hardware constraints will not manifest themselves with single query executions but will be evident when production load is applied and there is a limited number of CPU threads and a limited amount of memory to be shared among the queries. When you have CPU contention, it will usually be detectable by observing the performance monitor counter ‘% Processor Time’, which measures the CPU usage of the server. Looking deeper into SQL Server, you may see SOS_SCHEDULER_YIELD and CXPACKET wait types when the server is under CPU pressure. However, in some cases with poor storage system performance, even single executions of a query that is otherwise optimized can be slow. Storage system performance is best tracked at the operating system level using the performance monitor counters ‘Disk Seconds/Read’ and ‘Disk Seconds/Write’ which measure how long an I/O operation takes to complete. SQL Server will write to its error log if it detects poor storage performance (if an I/O takes longer than 15 seconds to complete). If you look at wait statistics and see a high percentage of PAGEIOLATCH_SH waits in your SQL Server, you might have a storage system performance issue. Typically, hardware performance is examined at a high level, early in the performance troubleshooting process, because it is relatively easy to evaluate.

Most database performance issues can be attributed to suboptimal query patterns, but in many cases running inefficient queries will put undue pressure on your hardware. For example, missing indexes could lead to CPU, storage, and memory pressure by retrieving more data than is required to process the query. It is recommended that you address suboptimal queries and tune them, before addressing hardware issue.

https://docs.microsoft.com/en-us/azure/virtual-machines/troubleshooting/allocation-failure

SQL Server has two types of databases: system and user. System databases are the ones used by SQL Server such as master and msdb. User databases are the ones created by users that store the data for applications. Both are important to account for when devising a backup and recovery plan. The nature of most system databases is that they are updated less frequently, although there are exceptions. As a general rule of thumb, system databases are not restored from one SQL Server instance to another. Your main concern should be backing up the user databases.

The most common types of backups generated for SQL Server installations are:

• Full

• Differential

• Transaction log

Depending on the deployment method, not all of these may be available as an option.

A full database backup is a backup of a single database. When the backup is made, all the pages from the database are copied to the backup device. The backup contains enough information so that you can restore the database to the point at which the backup was made. If you want to restore to a specific point-in-time to achieve your RPO, that can happen with the use of differential and/or transaction log backups. A full backup does not flush (or back up) the transaction log. Only a transaction log backup does that and is discussed below.

A differential backup contains all the database pages that have changed since the last time a full backup was made.

A transaction log backup is not only used to be able to achieve RPO and get to a more granular point in time but clears the transaction log and keeps its size manageable. Transaction log backups can be generated as frequently as every 30 seconds, although that is impractical.

There are other backup options such as copy-only, file, filegroup, partial, and more.

A differential or a log backup can be restored after a full database is restored, as long as the database RESTORE command uses either the WITH NORECOVERY or the WITH STANDBY option. If neither option is used, the database RESTORE will do a recovery of the database, after which no additional backups can be applied.

Every SQL Server database uses one of three recovery models: FULL, BULK_LOGGED, or SIMPLE. The recovery model is set as a database option, and governs the type of backups and restores that could be used with the database. Most databases are set to FULL or SIMPLE. FULL allows all types of backups to be generated while SIMPLE does not allow transaction log backups. This means that if you have a smaller RPO, SIMPLE may not meet your needs as you cannot restore to a granular point in time.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore