Resource limits

There are resource limits that are affected by the choice of Azure SQL Managed Instance, Azure SQL Database, or options within these choices:

• Memory

• Max log size

• Size of tempdb

• Max concurrent workers

• Backup retention

https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison

The Azure ecosystem offers three different open source database platforms. Each platform has different attributes that facilitate moving to the cloud quickly and painlessly. Each of these services comes with native high availability, automatic patching, automatic backups and the highest level of security protection. These offerings are fully supported by Microsoft from the service all the way through the database engine.

Azure Database for MySQL is a full managed enterprise grade database service that is ready to easily lift and shift customer environments to the Azure cloud. Customers can use their existing frameworks and languages to ensure that a migration doesn’t disrupt any business activity. In addition, the service has built-in high availability and dynamic scaling, which helps to meet any fluctuation in performance demands.

Azure Database for MariaDB is similar to the MySQL offering. It also allows for continued use of frameworks and languages of your choice. High availability and dynamic scaling are also provided by the service to ensure that customer demands are met within a moment’s notice.

Azure Database for PostgreSQL helps customers to build large scalable applications. The service allows for horizontal scaling and is available with Hyperscale which allows for unparalleled performance. It also integrates with several native features such as geospatial support, rich indexing, and JSONB along with other extensions. Azure Database for PostgreSQL Hyperscale is ideal for multi-tenant applications, with minor code changes to allow for data sharding.

Service tiers

There are three service tiers for each offering. Each tier has an ideal workload for which it is designed and allows you to choose from a variety of performance options.

• Basic – This tier is best for light workloads that need minimal compute and I/O performance.

• General Purpose – This tier is great for most workloads requiring scalable I/O throughput along with a healthy balance of compute and memory.

• Memory Optimized – This tier is suitable for workloads that demand high performance and require in-memory speed for quick processing of transactions along with higher concurrency.

The service tiers offered by the open source platforms provide a wide range of performance options and allow you to choose the best one for your given workload. The following versions shown in the table are available in the service for each database.

While the Azure service will perform minor upgrades, in order to execute a major upgrade you will need to restore from a backup into a new version.

https://azure.microsoft.com/en-us/product-categories/databases/

The data migration assistant provides both tooling to assess and execute a migration.

Data Migration Assistant

The MAP toolkit and Database Experimentation assistant can help you identify your databases and highlight any incompatibilities or potential performance issues in your database, but the Data Migration Assistant (DMA) is a comprehensive toolkit that assesses, identifies new features you can use to benefit your application, and ultimately performs the migration. This tool can be used to migrate between versions of SQL Server, from on-premises to an Azure VM or Azure SQL Database or Azure SQL Managed Instance.

One of the main benefits of the DMA is the ability to assess queries both from Extended Event trace files and SQL queries from an external application, for example T-SQL queries in the C# application code for your application. You can generate a full report using a C# source and upload the migration assessment to the DMA. The DMA mitigates the risk of moving to a newer version of SQL Server or to Azure SQL Database.

https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

SQL Server uses what is known as cost-based query optimizer. The query optimizer calculates a cost for multiple possible plans based on the statistics it has on the columns being utilized, and the possible indexes that can be used for each operation in each query plan. Based on this information, it comes up with a total cost for each plan. Some complex queries can have thousands of possible execution plans. The optimizer does not evaluate every possible plan, but uses heuristics to determine plans that are likely to have good performance. The optimizer will then choose the lowest cost plan of all the plans evaluated for a given query.

Because the query optimizer is cost-based, it is important that it has good inputs for decision making. The statistics SQL Server uses to track the distribution of data in columns and indexes need be kept up to date, or it can cause suboptimal execution plans to be generated. SQL Server automatically updates its statistics as data changes in a table; however, more frequent updates may be needed for rapidly changing data. The engine uses a number of factors when building a plan including compatibility level of the database, row estimates based on statistics and available indexes.

When a user submits a query to the database engine, the following process happens:

1. The query is parsed for proper syntax and a parse tree of database objects is generated if the syntax is correct.

2. The parse tree from Step 1 is taken as input to a database engine component called the Algebrizer for binding. This step validates that columns and objects in the query exist and identifies the data types that are being processed for a given query. This step outputs a query processor tree which is in the input for step 3.

3. Because query optimization is a relatively expensive process in terms of CPU consumption, the database engine caches execution plans in a special area of memory called the plan cache. If a plan for a given query already exists, that plan is retrieved from the cache. The queries whose plans are stored in cache will each have a hash value generated based on the T-SQL in the query. This value is referred to as the query_hash. When looking for a plan in cache, the engine will generate a query_hash for the current query and then look to see if it matches any existing queries in the plan case.

4. If the plan does not exist, the Query Optimizer then uses its cost-based optimizer to generate several execution plan options based on the statistics about the columns, tables, and indexes that are used in the query, as described above. The output of this step is a query execution plan.

5. The query is then executed using an execution plan that is pulled from the plan cache, or a new plan generated in step 4. The output of this step is the results of your query.

Most DBAs prefer to look at plans graphically, because a graphical plan allows you to see the plan as a whole, including what’s called the ‘shape’ of the plan, much more easily. There are several ways you can view and save graphical query plans. The most common tool used for this purpose is SQL Server Management Studio, but estimated plans can also be viewed in Azure Data Studio. There are also third-party tools that support viewing graphical execution plans.

There are three different types of execution plans that can be viewed.

Estimated Execution Plan: - This type is the execution plan as generated by the query optimizer. The metadata and size of query memory grant are based on estimates from the statistics as they exist in the database at the time of query compilation. To see a text-based estimated plan run the command SET SHOWPLAN_ALL ON before running the query. When you run the query, you will see the steps of the execution plan, but the query will NOT be executed, and you will not see any results. The SET option will stay in effect until you set it OFF.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-the-estimated-execution-plan?view=sql-server-ver15

Actual Execution Plan: - This type is same plan as the estimated plan; however this plan also contains the execution context for the query, which includes the estimated and actual row counts, any execution warnings, the actual degree of parallelism (number of processors used) and elapsed and CPU times used during the execution. To see a text-based actual plan run the command SET STATISTICS PROFILE ON before running the query. The query will execute, and you get the plan and the results.

Live Query Statistics: - This plan viewing option combines the estimated and actual plans into an animated plan that displays execution progress through the operators in the plan. It refreshes every second and shows the actual number of rows flowing through the operators. The other benefit to Live Query Statistics is that it shows the handoff from operator to operator, which may be helpful in troubleshooting some performance issues. Because the type of plan is animated, it is only available as a graphical plan.

https://www.mssqltips.com/sqlservertip/6328/execution-plan-in-azure-data-studio/

Database Normalization is a technique of designing the database in structured way to reduce redundancy and improve data integrity.

Database Normalization is used for following purpose:

1. To Eliminate the redundant or useless data

2. To Reduce the complexity of the data

3. To Ensure the relationship between tables as well as data in the tables

4. To Ensure data dependencies and data is logically stored.

http://www.complexsql.com/database-normalization/

Data encryption

Encrypted connections are forced by Azure SQL Database, with the option to additionally specify the inbound Transport Layer Security (TLS) required minimum version (>1.0, >1.1, or >1.2). For an Azure SQL managed instance, a TLS version of >1.2 is forced for outbound connections, and you have the same options for inbound TLS as for an Azure SQL Database instance. The recommended task is to force encryption on the client to avoid server negotiation, as well as to not trust the server certificate as a best practice.

Transparent Data Encryption (TDE) provides encryption for data at rest and is on by default for all new Azure SQL Database instances. It can be configured for all deployment options via a switch in the Azure portal, as shown here:

At the server or instance level, you can also choose to use a service-managed key or use Bring Your Own Key (BYOK). The default is to let the Azure service manage your key. Azure will automatically generate a key to encrypt your databases, and it will manage the key rotations. You've seen how to do this with the Azure portal, but you can also use Azure PowerShell, the Azure CLI, Transact-SQL (T-SQL), or REST APIs.

You can, alternately, use BYOK and take advantage of an Azure key vault. In this scenario, you are responsible for and in full control of a key lifecycle management (key creation, rotation, and deletion), key usage permissions, and auditing of operations on keys.

You can also take advantage of column-level encryption, which is supported in Azure SQL just as it is in SQL Server. Similarly, the Always Encrypted feature is supported just as it is in SQL Server. This process involves using client-side encryption of sensitive data, which uses keys that are never given to the database system. Additionally, the client driver transparently encrypts query parameters and decrypts the encrypted results. There is currently support on encrypted data for equality comparison, including JOIN, GROUP BY, and DISTINCT operators by deterministic encryption.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-secure-data/6-data-protection

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

Disaster Recovery – Distributed Availability Group

A distributed AG is an Enterprise Edition only feature introduced in SQL Server 2016. It is different than a traditional AG. Instead of having one underlying WSFC where all of nodes contain replicas participating in one AG as described in the previous example, a distributed AG is made up of multiple AGs. The primary replica containing the read write database is known as the global primary. The primary of the second AG is known as a forwarder and keeps the secondary replica(s) of that AG in sync. In essence, this is an AG of AGs.

This architecture makes it easier to deal with things like quorum since each cluster would maintain its own quorum, meaning it also has its own witness. A distributed AG would work whether you are using Azure for all resources, or if you are using a hybrid architecture.

The image below shows an example distributed AG configuration. There are two WSFCs. Imagine each is in a different Azure region or one is on premises and the other is in Azure. Each WSFC has an AG with two replicas. The global primary in AG 1 is keeping the secondary of replica of AG 1 synchronized as well as the forwarder, which also is the primary of AG 2. That replica keeps the secondary replica of AG 2 synchronized.

Why is this architecture worth considering?

• This architecture separates out the WSFC as a single point of failure if all nodes lose communication

• In this architecture, one primary is not synchronizing all secondary replicas.

• This architecture can provide failing back from one location to another.

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

Automated backup is a feature of the service.

Azure Backup and Azure Database Services have come together to build an enterprise-class backup solution for Azure Database for PostgreSQL servers that retains backups for up to 10 years.

Besides long-term retention, the solution also has many other capabilities as listed below:

• Azure role-based access control (Azure RBAC) to the database using Azure Active Directory and Managed Service Identity (MSI) authentication.

• Customer controlled scheduled and on-demand backup at the individual database level.

• Database-level restores to any Postgres server or directly to blob storage.

• Long-term retention.

• Central monitoring of all operations and jobs.

• Backups are stored in separate security and fault domains. So even if the source server were to be compromised or even killed, the backups would remain safe in the Backup vault.

• Use of pg_dump allows for greater flexibility in restores such that you can restore across database versions or even restore only a part of the backup.

You may use this solution independently or in addition to the native backup solution offered by Azure PostgreSQL that offers retention up to 35 days. The native solution is suited for operational recoveries, such as when you want to recover from the latest backups. The Azure Backup solution helps you with your compliance needs and more granular and flexible backup and restore.

https://docs.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql

You can use Shared Access Signatures (SAS) to grant limited rights to resources in an Azure storage account for a specified time period. This feature enables applications to access resources such as blobs and files, without requiring that they're authenticated first. You should only use SAS for data that you intend to make public.

A SAS is a token that an application can use to connect to the resource. The application appends the token to the URL of the resource. The application can then send requests to read or write data using this URL and token.

You can create a token that grants temporary access to the entire service, containers in the service, or individual objects such as blobs and files.

Use the Shared Access Signature (SAS) page in the Azure portal to generate SAS tokens. You specify the permissions (you could provide read-only access to a blob, for example), the period for which the SAS token is valid, and the IP address range of computers allowed to use the SAS token. The SAS token is encrypted using one of the access keys; you specify which key to use (key1 or key2).

https://docs.microsoft.com/en-us/azure/storage/common/storage-sas-overview

The Recovery Time Objective (RTO) is the amount of time that a business believes it will take to restore a function in the event of a disruption.

https://www.techopedia.com/definition/24250/recovery-time-objective--rto

Understanding recovery time and recovery point objectives are crucial to your high availability and disaster recovery (HADR) plan as they are the foundation for any availability solution.

Recovery Time Objective

Recovery Time Objective (RTO) is the maximum amount of time available to bring resources online after an outage or problem. If that process takes longer than the RTO, there could be consequences such as financial penalties, work not able to be done, and so on. RTO can be specified for the whole solution, which includes all resources, as well as for individual components such as SQL Server instances and databases.

Recovery Point Objective

Recovery Point Objective (RPO) is the point in time to which a database should be recovered and equates to the maximum amount of data loss that the business is willing to accept. For example, suppose an IaaS VM containing SQL Server experiences an outage at 10:00 AM and the databases within the SQL Server instance have an RPO of 15 minutes. No matter what feature or technology is used to bring back that instance and its databases, the expectation is that there will be at most 15 minutes worth of data lost. That means the database can be restored to 9:45 AM or later to ensure minimal to no data loss meeting that stated RPO. There may be factors that determine if that RPO is achievable.

Defining Recovery Time and Recovery Point Objectives

RTOs and RPOs are driven by business requirements but are also based on various technological and other factors, such as the skills and abilities of the administrators (not just DBAs). While the business may want no downtime or zero data loss, that may not be realistic or possible for a variety of reasons. Determining your solution’s RTO and RPO should be an open and honest discussion between all parties involved.

One of the aspects crucial for both RTO and RPO is knowing the cost of downtime. If you define that number and the overall effect being down or unavailable has to the business, it is easier to define solutions. For example, if the business can lose $10,000 per hour or could be fined by a government agency if something could not be processed, that is a measurable way to help define RTO and RPO. Spending on the solution should be proportional to the amount, or the cost, of downtime. If your HADR solution costs $X, but you wind up only being affected for a few seconds instead of hours or days when a problem occurs, it has paid for itself.

From a non-business standpoint, RTO should be defined at a component level (for example, SQL Server) as well as for the entire application architecture. The ability to recover from an outage is only as good as its weakest link. For example, if SQL Server and its databases can be brought online in five minutes but it takes application servers 20 minutes to do the same, the overall RTO would be 20 minutes, not five. The SQL Server environment could still have an RTO of five minutes; it still will not change the overall time to recover.

RPO deals specifically with data and directly influences the design of any HADR solution as well as administrative policies and procedures. The features used must support both the RTO and RPOs that are defined. For example, if transaction log backups are scheduled every 30 minutes but there is a 15-minute RPO, a database could only be recovered to the last transaction log backup available which in a best case would be 30 minutes ago. This timing assumes no other issues and the backups have been tested and are known to be good. While it is hard to test every backup generated for each database in your environment, backups are just files on a file system. Without doing at least periodic restores, there is no guarantee they are good. Running checks during the backup process can give you some degree of confidence.

The specific features used, such as an Always On Availability Group (AG) or an Always On Failover Cluster Instance (FCI) will also affect your RTOs and RPOs. Depending on how the features are configured, IaaS or PaaS solutions may or may not automatically failover to another location, which could result in longer downtime. By defining RTO and RPO, the technical solution that supports that requirement can be designed knowing the allowances for time and data loss. If those wind-up being unrealistic, RTOs and RPOs must be adjusted accordingly. For example, if there is a desired RTO of two hours but a backup will take three hours to copy to the destination server for restoring, the RTO is already missed. These types of factors must be accounted for when determining your RTOs and RPOs.

There should be RTOs and RPOs defined for both HA and DR. HA is considered a more localized event that can be recovered from more easily. One example of high availability would be an AG automatically failing over from one replica to another within an Azure region. That may take seconds, and at that point, you would need to ensure that the application can connect after the failover. SQL Server’s downtime would be minimal. A local RTO or RPO may potentially be measured in minutes depending on the critical nature of the solution or system.

DR would be akin to bringing up a whole new data centre. There are lots of pieces to the puzzle; SQL Server is just one component. Getting everything online may take hours or longer. This is why the RTOs and RPOs are separate. Even if many the technologies and features used for HA and DR are the same, the level of effort and time involved may not be.

All RTOs and RPOs should be formally documented and revised periodically or as needed. Once they are documented, you can then consider what technologies and features you may use for the architecture.

https://docs.microsoft.com/en-us/azure/architecture/framework/resiliency/backup-and-recovery