In an Azure VM, it's just SQL Server, so it is supported. In Azure SQL Managed Instance, native restore from a URL is supported.

https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups

Continuous integration

Continuous integration is a development methodology that focused on making small changes to code and frequent code check-ins to the version control system. Continuous integration provides an automated way to build, package, and test applications.

Having the framework in place facilitates more frequent check-ins, and allows better collaboration between developers, with the goal of improving code quality. Continuous delivery builds on the continuous integration and automates the delivery of code changes to the underlying infrastructure.

https://docs.microsoft.com/en-us/azure/devops/learn/what-is-continuous-integration

Networking configuration

Choices for networking for Azure SQL Database and Azure SQL Managed Instance are different. When you deploy a SQL database, currently the default is No access.

You can then choose to select a public endpoint or private endpoint. With Azure SQL Managed Instance, you deploy it inside an Azure virtual network and a subnet that's dedicated to managed instances. This enables you to have a secure, private IP address.

Azure SQL Managed Instance provides the ability to connect an on-premises network to a managed instance, connect a managed instance to a linked server or other on-premises data store, and connect a managed instance to other resources.

You can also enable a public endpoint so you can connect to a managed instance from the internet without a virtual private network (VPN). This access is disabled by default.

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

Geo-restore

If there's a catastrophic event, your organization needs to be able to recover. Your backups are automatically stored in RA-GRS (unless you opt for ZRS or LRS) meaning your backups will be stored in the paired region. So if an entire region goes down and your databases or managed instances are in that region, you're protected. You can do a geo-restore to any other region from the most recent geo-replicated backup. This backup can be a bit behind the primary because it takes time to replicate the Azure blob to another region. You can easily perform a geo-restore by using the Azure portal, the PowerShell/Azure CLI, or REST APIs.

https://azure.microsoft.com/en-us/blog/azure-sql-database-geo-restore/

sys.dm_os_wait_stats shows the wait stats across the server that have completed. This dynamic management view does not show current waits.

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

The Recovery Time Object (RTO) is the amount of time expected to return an IT service or component to operation after a failure.

https://www.ibm.com/services/business-continuity/rto

The Maximum Tolerable Downtime (MTD) is the longest amount of time that an IT service or component may be unavailable without causing serious damage to the organization.

https://www.sciencedirect.com/topics/computer-science/maximum-tolerable-downtime

The Recovery Point Objective (RPO) identifies the maximum amount of days data, measured in time, that may be lost during a recovery effort.

https://www.ibm.com/services/business-continuity/rpo

Service-Level Agreements (SLAs) are written contracts that document service expectations.

https://www.cio.com/article/2438284/outsourcing-sla-definitions-and-solutions.html

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.

Application roles

Application roles can be created within a SQL Server database or Azure SQL Database. Unlike database roles, users are not made members of an application role. An application role is activated by the user, by supplying the pre-configured password for the application role. Once the role is activated the permissions that are applied to the application role are applied to the user until that role is deactivated.

Built-in database roles

Microsoft SQL Server contains several fixed database roles within each database for which the permissions are predefined. Users can be added as members of one or more roles. These roles give their members a pre-defined set of permissions. These roles work the same within Azure SQL Database and SQL Server.

Users that need to create other users within the database can be granted membership in the role db_accessadmin. This role does not grant access to the schema of any of the tables, nor does it grant access to the data within the database.

Users that need to back up a database in a SQL Server or Managed Instance can be made members of the role db_backupoperator. The role db_backupoperator does not confer any permissions in an Azure SQL Database.

Users that need the ability to read from every table and view within the database can be made members of the role db_datareader.

Users that need the ability to INSERT, UPDATE, and DELETE data from every table and view within the database can be made members of the role db_datawriter.

Users who need the ability to create or modify objects within the database can be made members of the role db_ddladmin. Members of this role can change the definition of any object, of any type, but members of this role are not granted access to read or write any data within the databases.

The role db_denydatareader can be used for users who need to be prevented from reading data from any object in the database, when those users have been granted rights through other roles or directly.

The role db_denydatawriter can be used for users who need to be prevented from writing data to any object in the database, when those users have been granted rights through other roles or directly.

Users who need administrative access to the database can be made members of the role db_owner. Members of the db_owner role can perform any action within the database by default. However, unlike the actual database owner, who has the user name dbo, users in the db_owner role can be blocked from accessing data by placing them in other database roles, such as db_denydatareader, or by denying them access to objects. Membership in this database role should be limited to only trusted users.

Users who need to be able to grant access to other users within the database can be made members of the role db_securityadmin. Members of this role are not specifically granted access to the data within the database; however members of this role can grant themselves access to the tables within the database. Membership in this database role should be limited to only trusted users.

All users within a database are automatically members of the public role. By default, this role has no permissions granted to it. Permissions can be granted to the public role, but you should consider carefully whether that is really something you want to do. Granting permissions to the public role would grant these permissions to any user, including the guest account, if the guest account was enabled.

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

Purchasing model

The Azure SQL purchasing model provides two options:

• Based on virtual cores (vCore-based)

• Based on database transaction units (DTU- based)

The DTU model isn't available in Azure SQL Managed Instance.

Microsoft recommends the vCore-based model because it allows you to independently select compute and storage resources. The DTU-based model is a bundled measure of compute, storage, and I/O resources.

In the vCore model, you pay for:

• Compute resources. (The service tier + the number of vCores and the amount of memory + the generation of hardware.)

• The type and amount of data and log storage.

• Backup storage location. (Read-access geo-redundant storage (RA-GRS), Zone-redundant storage (ZRS), or locally-redundant storage (LRS)).

The vCore model also allows you to use Azure Hybrid Benefit for SQL Server and/or reserved capacity (pay in advance) to save money. Neither of these options is available in the DTU model.

https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models

Authentication

For both Azure SQL Database instances and Azure SQL managed instances, SQL authentication is used for deployment. This authentication role is referred to as the server admin. For databases in Azure SQL Database, the server admin is a server-level principal for the Azure SQL Database logical server but, for Azure SQL managed instances, the role is a member of the sysadmin server role. In addition, Mixed Mode authentication is forced for both deployment options.

Depending on how your organization has configured the Azure AD instance, you can connect to it by using any of the following three methods (for example, in SSMS):

• Azure Active Directory - Integrated: A non-interactive method that you can use if you're signed in to Windows with your Azure AD credentials from a federated domain.

• Azure Active Directory - Password: A non-interactive method that allows you to connect with an Azure AD principal name by using the Azure AD-managed domain. From the documentation:

This can apply to native or federated Azure AD users. A native user is one explicitly created in Azure AD and being authenticated using user name and password, while a federated user is a Windows user whose domain is federated with Azure AD. The latter method (using user & password) can be used when a user wants to use their windows credential, but their local machine is not joined with the domain (for example, using a remote access). In this case, a Windows user can indicate their domain account and password and can authenticate to SQL Database/Azure Synapse Analytics (formerly SQL DW) by using federated credentials.

• Azure Active Directory - Universal with Multi-Factor Authentication: An interactive method that safeguards access to data while meeting an organization's demand for a single sign-in process with Azure AD Multi-Factor Authentication.

For an Azure SQL managed instance, the method is similar to that for SQL Server: you can have SQL or Azure AD sign-ins, database users, and contained database users.

For Azure SQL Database instances, there are a few nuances. You can have SQL log-ins, database users, and even contained database users for Azure AD (recommended). Although the server admin for Azure SQL Database essentially has sysadmin rights, you can create more limited admins by using database level roles in the master of the Azure SQL Database logical server. Two roles are available:

• loginmanager: A database-level role that allows members to create logins for the database server.

• dbmanager: A database-level role that allows members to create and delete databases for the database server.

Finally, when you set up and configure authentication and authorization, you have four guidelines to follow:

• Deploy with a server admin

• Create other admins as necessary

• Admins can create users

• Grant access just like you would in SQL Server

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

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