Administering Relational Databases on Microsoft Azure

Step 1: Create an Azure AD service principal and grant the service principal permissions for Vault1.
Set up SQL Server TDE Extensible Key Management by using Azure Key Vault

Set up an Azure AD service principal
To grant your SQL Server instance access permissions to your Azure key vault, you need a service principal account in Azure AD.

Step 2: On SQL1, create an asymmetric key.
Be sure to use an asymmetric RSA 2048 or 3072 key so that it's supported by SQL Server.

Step 3: On SQL1, crate a cryptographic provider and Microsoft SQL Server credential.
Configure SQL Server
Create a cryptographic provider by using the SQL Server Connector, which is an EKM provider for the Azure key vault.
Set up a SQL Server credential for a SQL Server login to use the key vault.

Step 4: On SQL1, create a login from the asymmetric key.
Create a new login from the asymmetric key in SQL Server.

References:
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault

Example:
Free space for the file in the below query result set will be returned by the FreeSpaceMB column.

SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM
sys.database_files
WHERE type IN (0,1);

References:
https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

Box 1: wait_type
Determine if a RESOURCE_SEMAHPORE wait is a top wait
Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait

SELECT wait_type, SUM(wait_time) AS total_wait_time_ms FROM sys.dm_exec_requests AS req JOIN sys.dm_exec_sessions AS sess ON req.session_id = sess.session_id WHERE is_user_process = 1 GROUP BY wait_type ORDER BY SUM(wait_time) DESC;

Box 2: sys.dm_exec_requests
Use the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time.

Azure SQL RESOURCE_SEMAPHORE wait "wait_time" is_user_process

C: Advanced Data Security (ADS) is a unified package for advanced SQL security capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive data

D: You can apply sensitivity-classification labels persistently to columns by using new metadata attributes that have been added to the SQL Server database engine. This metadata can then be used for advanced, sensitivity-based auditing and protection scenarios.
A: An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. This field logs the sensitivity classifications (labels) of the data that was returned by a query. Here's an example:

References:
https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

Step 1: Set up the Active Directory Admin for AzSQL1.

Step 2: Connect to DB1 by using the server administrator.
Sign into your managed instance with an Azure AD login granted with the sysadmin role.

Step 3: Create a user by using the FROM EXTERNAL PROVIDER clause.
FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. To create an Azure AD user from an Azure AD login use the following syntax:

CREATE USER [AAD_principal] FROM LOGIN [Azure AD login]

References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql