Compute and storage

There are various options for compute and storage, including service tiers, purchasing models, and hardware generations. It is necessary to select the desired configuration during deployment. The number of vCores and Data max size must also be determined.

Generally, if migrating, use a size that's similar to what is used on-premises. There are tools, like the Data Migration Assistant SKU recommender, to estimate the number of vCores and Data max size based on the current workload.

Data max size is not necessarily the size of your data today. It's the maximum amount of data space that can be allocated for the database. It will also help you understand the allocation of log space, which scales with Data max size.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-deploy-configure/2-plan-deploy-verify

Role-Based Access Control (RBAC)

All Azure types of operations for Azure SQL are controlled through role-based access control (RBAC). RBAC is currently decoupled from SQL Security, but you can think of it as security rights outside of your database in SQL Database or SQL managed instance, with a scope that includes subscription, resource group, and resource. The rights apply to operations in the Azure portal, the Azure CLI, and Azure PowerShell. RBAC allows for separation of duties between deployment, management, and usage.

Built-in roles are available to reduce the need for higher-level RBAC roles such as Owner or Contributor. Effectively, you can use these roles to have certain individuals deploy Azure SQL resources (or manage security policies) but grant other users actual access to use or manage the instance or database. For example, a SQL Server contributor could deploy a server but assign an Azure SQL Database instance user to be the admin of the server and databases.

The built-in roles include:

• SQL DB Contributor: Can create and manage databases but can't access the database (for example, can't connect and read data)

• SQL Managed Instance Contributor: Can create and manage managed instances but can't access them

• SQL Security Manager: Can manage security policies for databases and instances (such as auditing) but can't access them

• SQL Server Contributor: Can manage servers and databases but can't access them.

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

Only SQL Server in an Azure virtual machine gives you access to the underlying OS. In the PaaS deployment options, the OS is abstracted away from you, meaning Azure manages it, but you can't access it.

https://cloudblogs.microsoft.com/sqlserver/2020/03/17/tips-to-select-the-best-azure-sql-deployment-option/

An auto-failover group is an availability feature that can be used with both Azure SQL Database and Azure SQL Database Managed Instance. Autofailover groups let you manage how databases on an Azure SQL Database server or databases in Azure SQL Database Managed Instance are replicated to another region, and let you manage how failover could happen. The name assigned to the autofailover group must be unique within the *.database.windows.net domain. Azure SQL Database Managed Instance only supports one autofailover group.

Autofailover groups provide AG-like functionality called a listener, which allows both read-write and read-only activity. This functionality can be seen in the image below which is slightly different than the one for active geo-replication. There are two different kinds of listeners: one for read-write and one for read-only traffic. Behind the scenes in a failover, DNS is updated so clients will be able to point to the abstracted listener name and not need to know anything else. The database server containing the read-write copies is the primary, and the server that is receiving the transactions from the primary is a secondary.

When it comes to fail over, autofailover groups have two different policies that can be configured.

• Automatic – By default, when a failure occurs and it is determined that a failover must happen, the autofailover group will switch regions. The ability to fail over automatically can be disabled.

• Read-Only – By default, if a failover occurs, the read-only listener is disabled to ensure performance of the new primary when the secondary is down. This behaviour can be changed so that both types of traffic are enabled after a failover.

Failovers can be performed manually even if automatic failover is allowed. Depending on the type of failover, there could be data loss. Unplanned failovers could result in data loss if forced and the secondary is not fully synchronized with the primary. Configuring GracePeriodWithDataLossHours controls how long Azure waits before failing over. The default is one hour. If you have a tight RPO and cannot afford much data loss, set the value higher so Azure will wait longer before failing over, hopefully resulting in less data loss.

One autofailover group can contain one or more databases. The database size and edition will be the same on both the primary and secondary. The database is created automatically on the secondary through a process called seeding. Depending on the size of the database, this may take some time. Ensure that you plan accordingly and that you take into account things like the speed of the network.

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

Backup frequency

Both SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes. The frequency of transaction log backups is based on the compute size and the amount of database activity.

When you restore a database, the service determines which full, differential, and transaction log backups need to be restored.

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

Languages and APIs

Because all Azure SQL services are based on the SQL Server engine, Azure SQL supports the Transact-SQL (T-SQL) language and the Tabular Data Stream (TDS) protocol. So all drivers that normally work with SQL Server work with Azure SQL.

Azure SQL also supports REST APIs for the management of SQL managed instances and SQL databases.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features

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

Azure Active Directory can be configured to sync with the on-premises Active Directory. This option allows users to have the same usernames and passwords to access on-premises resources as well as Azure resources. Azure Active directory adds on additional security measures by allowing the administrator to easily configure multi-factor authentication (MFA).

With MFA enabled on an account, after the correct username and password is supplied, a second level of authentication is required. By default, MFA can be configured to use the Windows Authenticator application, which will then send a push notification to the phone. Additional options for the default MFA action include sending the recipient a text message with an access code, or having the user enter an access code that was generated with the Microsoft Authenticator application. If a user has MFA enabled, they have to use the Universal Authentication with MFA option in Azure Data Studio and SQL Server Management Studio.

https://docs.microsoft.com/en-us/azure/active-directory/fundamentals/active-directory-whatis

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

In addition to supporting encryption at rest through transparent data encryption, SQL Server supports encrypting data within columns using Always Encrypted. Once data is encrypted, the application accessing the database must have the correct certificate in order to view the plain text values of the data.

Always Encrypted allows for the encryption of data within the client application. This encryption happens automatically based on the settings within the Microsoft SQL Server database, which tell the application what the encryption settings on the database column are. Always Encrypted is based on a master encryption key as well as a column encryption key. Having both keys allows each column to be encrypted using a different encryption key for maximum data protection. Always Encrypted has a variety of key stores that can store the certificate used by encryption.

Secure Enclaves

Starting with SQL Server 2019, Always Encrypted supports a feature called secure enclaves, which allows more robust querying of encrypted data. A secure enclave is a secured region of memory within the SQL Server process that acts as a trusted execution environment for processing encrypted data. This enclave appears as a black box to SQL Server, and it is not possible to view any data or code, even with a debugger. The image below shows the architecture of this process:

Secure enclaves allow pattern matching and range comparison on data that is stored in columns using Randomized encryption.

https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-azure-key-vault-configure?tabs=azure-powershell