Describe 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

Beyond its built-in high availability, the Azure platform offers two options for providing higher levels of availability for VM and some PaaS workloads. Availability Zones and Availability Sets protect your workloads from planned maintenance activity and potential hardware failures.

Availability Zones

Availability Zones are unique physical locations within a region. Each zone is made up of one or more datacentres equipped with independent power, cooling, and networking. Within Azure regions that support Availability Zones, you can specify in which zone you want the virtual machine to reside when you choose to use Available Zones during VM creation. There are three Availability Zones within each supported Azure region. Availability Zones provide high availability against datacentre failures when you deploy multiple VMs into different zones. In addition, they also provide a means for Microsoft to perform maintenance (using a grouping called an update domain) within each region by only updating one zone at any given time. You can spread out your virtual machine ecosystem across three zones in the region. Utilizing Availability Zones in conjunction with your Azure virtual machines raises your uptime to four nines (99.99%) which equates to a maximum of 52.60 minutes of downtime per year. You can identify which Azure regions support Availability Zones at docs.microsoft.com. If Availability Zones are available in your region, and your application can support the minimal cross-zone latency, Availability Zones will provide the highest level of availability for your application.

In the image above, you can see the availability zone configuration. When you deploy a VM into a region with an availability zone you will be presented with the option to deploy in Zone 1, 2, and 3. These zones are logical representations of physical datacentres, which means a deployment to Zone 1 in one subscription, does not mean that Zone 1 represents the same datacentre in another subscription.

Availability Sets

Availability sets are similar to Availability Zones, except instead of spreading workloads across datacentres in a region, they spread workloads across servers and racks in a datacentre. Since nearly all workloads in Azure are virtual, you can use availability sets in order to guarantee that the two VMs containing your Always On Availability Group members are not running on the same physical host. Availability sets can provide up to 99.95% availability, and should be used when Availability Zones are unavailable in a region, or an application cannot tolerate intra-zone latency.

https://docs.microsoft.com/en-us/azure/availability-zones/az-overview

sys.dm_db.index_usage_stats shows the read and write operations against each index.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?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.

While this behaviour occurs in any database, with the write-intensive nature of tempdb, an uneven write pattern could cause a bottleneck on the largest file, as more of the writes would happen there.

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

Azure provides multiple methods to monitor the performance of your resources and create a baseline. Each method can be tailored for a specific metric. The metrics that you can monitor will vary depending on the type of Azure resource you are monitoring. For example, Azure SQL Database and SQL Server on an Azure Virtual Machine will have different metrics available in the Azure Portal.

The following examples are focused on an Azure Virtual Machine. When you deploy an Azure Virtual Machine from the Azure Marketplace, an agent is installed in the virtual machine that provides a basic set of operating system metrics that are presented to you in the Azure portal. This agent supplies metrics to a service called Azure Monitor, which is a comprehensive platform monitoring solution that collects and displays a standard set of metrics from Azure resources. In the case of a virtual machine, the default metrics captured are CPU, network utilization, and disk read and write operations. You can capture additional metrics beyond what is captured in Azure monitor by enabling Monitoring Insights for your virtual machine as shown in the following image.

These metrics pertain to the operating system, not SQL Server. You’ll notice that the namespace for each metric is the virtual machine host, not SQL Server.

You are unable to view SQL Server-specific metrics from within the portal. For detailed SQL Server-specific metrics you will need to gather them from the virtual machine itself.

Azure Monitoring Insights allows you to collect additional data points like storage latency, available memory, and disk capacity. These Azure Monitor Insights can be one way of viewing a baseline of performance for your Azure Virtual Machine including I/O performance, memory, and CPU utilization. This data is stored in an Azure Log Analytics workspace. Azure Log Analytics is the primary tool in Azure for storing and querying log files of all kinds. Log Analytics is queried by a SQL-like language called Kusto Query Language (KQL).

If you create a virtual machine with one of the pre-configured SQL Server images in the Azure Marketplace, you will also get the SQL virtual machine resource provider as shown in the following image. You can launch this screen in the Azure portal by clicking on the SQL Server configuration option in the settings section of the main blade for an Azure Virtual Machine.

This dashboard allows you to see how much space your database files and transaction log file are consuming, and allows you to manage the features provided by the resource provider like automated patching and storage configuration. You can manually install the SQL Resource Provider for other installations of SQL Server on Azure Virtual Machine that were not defined as part of the virtual machine.

https://azure.microsoft.com/en-us/services/monitor/

Service tier

When it is time to choose a service tier for performance and availability, Microsoft recommends you start with the General Purpose tier and adjust as needed.

Three tiers are available in the vCore model:

• General Purpose: Suitable for most business workloads. Offers budget-oriented, balanced, and scalable compute and storage options.

• Business Critical: Suitable for business applications with low-latency response requirements. Offers the highest resilience to failures by using several isolated replicas. This tier is the only one that can use In-Memory OLTP to improve performance.

• Hyperscale: Suitable for business workloads with highly scalable storage (100 TB+) and read-scale requirements. From a performance and cost perspective, this tier falls between General Purpose and Business Critical. Hyperscale is currently available only for single databases in Azure SQL Database.

If you choose General Purpose and the vCore-based model, you have an additional decision to make regarding the compute that you pay for:

• Provisioned compute is meant for more regular usage patterns with higher average compute utilization over time, or for multiple databases that use elastic pools.

• Serverless compute is meant for intermittent, unpredictable usage with lower average compute utilization over time. Serverless has autopause and resume capabilities with a time delay that you set. When your database is paused, you pay only for storage.

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-general-purpose-business-critical

Although geo-replication and auto-failover groups are both options here, auto-failover groups will enable the customer to fail over if it's required, without changing any connection strings. This setup can potentially help in reducing downtime to update applications, because it won't be needed. You can also configure monitoring queries to check the status. If something goes wrong, you can even force a failover.

https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell

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.

Suboptimal query constructs

Relational databases perform best when executing set-based operations. Set-based operations perform data manipulation (INSERT, UPDATE, DELETE, and SELECT) in sets, where work is done on a set of values and produces either a single value or a result set. The alternative to set-based operations is to perform row-based work, using a cursor or a while loop. This type of processing is known as row-based processing, and its cost increases linearly with the number of rows impacted. That linear scale is problematic as data volumes grow for an application.

While detecting suboptimal use of row-based operations with cursors or WHILE loops is important, there are other SQL Server anti-patterns that you should be able to recognize. Table-valued functions (TVF), particularly multi-statement table-valued functions, caused problematic execution plan patterns prior to SQL Server 2017. Many developers like to use multi-statement table valued functions because they can execute multiple queries within a single function and aggregate the results into a single table. However, anyone writing T-SQL code needs to be aware of the possible performance penalties for using TVFs.

SQL Server has two types of table-valued functions, inline and multi-statement. If you use an inline TVF, the database engine treats it just like a view. Multi-statement TVFs are treated just like another table when processing a query. Because TVFs are dynamic and as such, SQL Server does not have statistics on them, it used a fixed row count when estimating the query plan cost. A fixed count can be fine, if the number of rows is small, however if the TVF returns thousands or millions of rows, the execution plan could be very inefficient.

Another anti-pattern has been the use of scalar functions, which have similar estimation and execution problems. Microsoft has made a lot of headway on improving the performance of the aforementioned patterns with the introduction of Intelligent Query Processing in SQL Server 2017 and Azure SQL Database, under compatibility levels 140 and 150.

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

Database-level protection means that anything that is captured in the transaction log for a user or application database is accounted for as part of the availability feature. Anything that exists outside of the database or that is not captured as part of the transaction log such as SQL Server Agent jobs and linked servers must be manually dealt with to ensure the destination server can function like the primary in the event of a planned or unplanned failover event.

https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell

Automatic Plan Correction does look at query plan regressions from the Query Store, and can recommend or fix problems by reverting to a previous good plan.

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