Versionless SQL is a significant difference between IaaS and PaaS. Unlike IaaS, which is tied to a specific SQL Server version, like SQL Server 2019, SQL Database and SQL Managed Instance are versionless. The main "branch" of the SQL Server engine codebase powers SQL Server 2019, SQL Database, and SQL Managed Instance.

Although SQL Server versions come out every few years, PaaS services allow Microsoft to continually update SQL databases/instances. Microsoft rolls out fixes and features as appropriate. As a consumer of the service, you don't have control over these updates, and the result of @@VERSION won't line up to a specific SQL Server version. But versionless SQL allows for worry-free patching for both the underlying OS and SQL Server and for Microsoft to give you the latest bits.

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

Isolation Levels

SQL Server offers several isolation levels to allow you to define the level of consistency and correctness you need guaranteed for your data. Isolation levels let you find a balance between concurrency and consistency. The isolation level does not affect the locks taken to prevent data modification, a transaction will always get an exclusive lock on the data that is modifying. However, your isolation level can affect the length of time that your locks are held. Lower isolation levels increase the ability of multiple user process to access data at the same time, but increase the data consistency risks that can occur. The isolation levels in SQL Server are as follows:

Read Uncommitted - This is the lowest isolation level available. Dirty reads are allowed, which means one transaction may see changes made by another transaction that have not yet been committed.

Read Committed - This level allows a transaction to read data previously read, but not modified by another transaction with without waiting for the first transaction to finish. This level also releases read locks as soon as the select operation is performed. This is the default SQL Server level.

Repeatable Read - This level keeps read and write locks that are acquired on selected data until the end of the transaction.

Serializable - This is the highest level of isolation where transactions are completely isolated. Read and write locks are acquired on selected data and not released until the end of the transaction.

SQL Server also includes two isolation levels that include row-versioning.

Read Committed Snapshot - In this level read operations take no row or page logs, and the engine presents each operation with a transactionally consistent snapshot of the data as it existed at the start of the query. This level is typically used when users are running frequent reporting queries against an OLTP database, in order to prevent the read operations from blocking the write operations.

Snapshot - This level provides transaction level read consistency through row versioning. This level is vulnerable to update conflicts. If a transaction running under this level reads data modified by another transaction, an update by the snapshot transaction will be terminated and roll back. This is not an issue with read committed snapshot isolation.

Isolation levels are set for each session with the T-SQL SET command.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

Hybrid solutions span both on premises and Azure, or possibly Azure to another public cloud.

https://docs.microsoft.com/en-us/azure/azure-sql/azure-hybrid-benefit?tabs=azure-powershell

This Dynamic Management View (DMV) shows the recommendations and the status.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql?view=sql-server-ver15

All Relational Database Management platforms have 4 basic permissions which control data manipulation language (DML) operations. These permissions are SELECT, INSERT, UPDATE, and DELETE. These permissions apply to all SQL Server platforms as well as Azure SQL Database for MySQL and Azure SQL Database for PostgreSQL.

https://www.educba.com/sql-dml-commands/

All of these permissions can be granted, revoked or denied on tables and views. If a permission is granted using the GRANT statement, then the permission is given to the user or role referenced in the GRANT statement. Users can also be denied permissions using the DENY command. If a user is granted a permission and denied the same permission, the DENY will always supersede the grant, and the user will be denied access to the specific object.

Table and view permissions

Tables and views represent the objects on which permissions can be granted within a database. Within those tables and views, you can additionally restrict the columns that are accessible to a given security principal (user or login). SQL Server and Azure SQL Database also include row-level security which can be used to further restrict access.

The SELECT permission, when granted, will allow the user to view the data within the object (table or view). When denied, the user will be prevented from viewing the data within the object.

The INSERT permission, when granted, will allow the user to insert data into the object. When denied, the user will be prevented from inserting data into the object.

The UPDATE permission, when granted, will allow the user the update data within the object. When denied, the user will be prevented from updating data in the object.

The DELETE permission, when granted, will allow the user to delete data within the object. When denied, the user will be prevented from deleting data from the object.

Azure SQL Database and Microsoft SQL Server have additional permissions which can be granted, revoked or denied as needed.

The CONTROL permission grants all rights to the objects. This allows the user who has this permission to perform any action they wish against the object, including deleting the object.

The REFERENCES permission grants the user the ability to view the foreign keys on the object. This permission is also available in Azure SQL Database for MySQL and Azure SQL Database for PostgreSQL.

The TAKE OWNERSHIP permission allows the user the ability to take ownership of the object.

The VIEW CHANGE TRACKING permission allows the user to view the change tracking setting for the object.

The VIEW DEFINITION permission allows the user to view the definition of the object.

Function and stored procedure permissions

Like tables and views, functions and stored procedures have several permissions which can be granted or denied.

The ALTER permission grants the user the ability to change the definition of the object.

The CONTROL permission grants the user all rights to the object.

The EXECUTE permission grants the user the ability to execute the object. This permission can be granted to Azure SQL Database for MySQL and Azure SQL Database for PostgreSQL.

The VIEW CHANGE TRACKING permission allows the user to view the change tracking setting for the object.

The VIEW DEFINITION permission allows the user to view the definition of the object.

https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/

Always On Availability Groups

Availability Groups were introduced in SQL Server 2012 Enterprise Edition and as of SQL Server 2016, are also in Standard Edition. In Standard Edition, an AG can contain one database whereas in Enterprise Edition, an AG can have more than one database. While AGs share some similarities with Failover clustered instances (FCIs), in most ways they are different.

The biggest difference between an FCI and an AG is that AGs provide database-level protection. The primary replica is the instance participating in an AG that contains the read/write databases. A secondary replica is where the primary sends transactions over the log transport to keep it synchronized. Data movement between a primary replica can be synchronous or asynchronous. The databases on any secondary replica are in a loading state, which means they can receive transactions but cannot be a fully writeable copy until that replica becomes the primary. An AG in Standard Edition can have at most two replicas (one primary, one secondary) whereas Enterprise Edition supports up to nine (one primary, eight secondary). A secondary replica is initialized either from a backup of the database, or as of SQL Server 2016, you can use a feature called ‘automatic seeding’. Automatic seeding uses the log stream transport to stream the backup to the secondary replica for each database of the Availability Group using the configured endpoints.

An AG provides abstraction with the listener. The listener functions like the unique name assigned to an FCI and has its own name and IP address that is different from anything else (WSFC, node, etc.). The listener also requires an ILB and goes through a stop and start. Applications and end users can use the listener to connect, but unlike an FCI, if desired, the listener does not have to be used. Connections directly to the instance can occur. With Enterprise Edition, secondary replicas in Enterprise Edition can also be configured for read-only access if desired and can be used for other functionality such as database consistency checks

(DBCCs) and backups.

AGs can have a quicker failover time compared to an FCI, which is one reason they are attractive. While AGs do not require shared storage, each replica has a copy of the data, which increases the total number of copies of the database and overall storage costs. The storage is local to each replica. For example, if the data footprint of the databases on the primary replica is 1 TB, each replica will also have the same. If there are five replicas, that means you need 5 TB of space.

Remember that any object that exists outside of the database or is not captured in the database’s transaction log must manually be created and accounted for on any other SQL Server instance should that instance need to become the new primary replica. Examples of objects you would be responsible for include SQL Server Agent jobs, instance-level logins, and linked servers. If you can use Windows authentication or use contained databases with AGs, it will simplify access.

Many organizations may face challenges implementing highly available architectures, and may only need the high availability provided by the Azure platform, or using a PaaS solution like Azure SQL Managed Instance. Before we look at Azure platform solutions, there is one other SQL Server feature that you should know about: log shipping.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

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.

Backups

One of the most important features of the Platform as a Service offering is backups. In this case, backups are performed automatically without any intervention from you. Backups are stored in Azure blob geo-redundant storage and by default are retained for between 7 and 35 days, based on the service tier of the database.

Basic and vCore databases default to seven days of retention, and on the vCore databases this value can be adjusted by the administrator. The retention time can be extended by configuring long-term retention (LTR), which would allow you to retain backups for up to 10 years. In order to provide redundancy, you are also able to use read-accessible geo-redundant blob storage.

This storage would replicate your database backups to a secondary region. It would also allow you to read from that secondary region if needed. It is worth stating that manual backups of databases are not permitted and the platform will deny any request to do so.

Database Backups are taken on a given schedule:

• Full – Once a week

• Differential – Every 12 hours

• Log – Every 5-10 minutes depending on transaction log activity

This backup schedule should meet the needs of most recovery point/time objectives (RPO/RTO) however each customer should evaluate whether they meet your business requirements.

If the need to restore a database arises, there are several options available. Due to the nature of Platform as a Service, you cannot manually restore a database using conventional methods, such as issuing the T-SQL command RESTORE DATABASE.

Regardless of which restore method is implemented, it is not possible to restore over an existing database. If a database needs to be restored, the existing database must be dropped or renamed prior to initiating the restore. Furthermore, keep in mind that depending on the platform service tier, restore times could fluctuate. It is recommended that you test the restore process to obtain baseline metrics on how long a restore could potentially take.

The available restore options are:

Restore using the Azure portal – Using the Azure portal you have the option of restoring a database to the same Azure SQL Database server, or you can use the restore to create a new database on a new server in any Azure region.

Restore using scripting Languages – Both PowerShell and Azure CLI can be utilized in order to restore a database.

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

Database Roles

Database security can get complicated for applications with many users. In order to make it easier for both administrators and auditors, most database applications use role-based security.

Roles are effectively security groups that share a common set of permissions. Combining permissions into a role allows a set of roles to be created for a given application.

Some examples of roles would be administrators who had full access to all of the databases and servers, reporting users who only read the database, and an application account that had access to write data into the database.

The roles can be defined when the application is designed, and then users can be assigned to those roles as they need access to the database. Role-based access control or RBAC is a common architecture across computer systems and is how authorization is managed in Azure Resource Manager.

SQL Server and Azure SQL Database both include built-in roles that are defined by Microsoft, and also provide the option to create custom roles.

Custom roles can be created at the server or database level. However, server roles cannot be granted access to objects within a database directly. Server roles are only available in SQL Server and Azure SQL Managed Instance, not in Azure SQL Database.

Within a database, permissions can be granted to the users that exist within the database. If multiple users all need the same permissions, you can create a database role within the database and grant the needed permissions to this role. Users can be added as members of the database role. The members of the database role will inherit the permissions of the database role.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

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 (max 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

The Maximum Tolerable Downtime (MTD) is the amount of time that a business may be without a service before irreparable harm occurs. The measure is sometimes also called the Maximum Tolerable Outage (MTO).

https://www.strategiccompanies.com/maximum-tolerable-downtime

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