Administering Relational Databases on Microsoft Azure v1.0
Question 1
HOTSPOT -
You have an Azure subscription.
You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.
How should you complete the ARM template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Box 1: Microsoft.Sql/managedInstances
The Managed Instance depends on the Virtual Network.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/create-template-quickstart?tabs=azure-powershell
Question 2
HOTSPOT -
You have the following Azure Resource Manager template.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-arm-template-quickstart
Question 3
HOTSPOT -
You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.
You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service.
You need to create a backup of DB1 that is accessible to the Azure Database Migration Service.
What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Box 1: ..with CHECKSUM option -
Azure Database Migration Service does not initiate any backups, and instead uses existing backups, which you may already have as part of your disaster recovery plan, for the migration. Be sure that you take backups using the WITH CHECKSUM option.
Box 2: An SMB share -
For online migrations from SQL Server to SQL Managed Instance using Azure Database Migration Service, you must provide the full database backup and subsequent log backups in the SMB network share that the service can use to migrate your databases.
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online
Question 4
HOTSPOT -
You have an Azure subscription.
You plan to deploy an Azure SQL database by using an Azure Resource Manager template.
How should you complete the template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Box 1: "Microsoft.Sql/servers"
Example:
"resources": [
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2021-08-01-preview",
"name": "[parameters('serverName')]",
"location": "[parameters('location')]",
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]"
}
},
{
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2021-08-01-preview",
"name": "[format('{0}/{1}', parameters('serverName'), parameters('sqlDBName'))]",
"location": "[parameters('location')]",
"sku": {
"name": "Standard",
"tier": "Standard"
},
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
]
}
Box 2: "dependsOn": [
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-arm-template-quickstart
Question 5
You have an on-premises Microsoft SQL Server 2019 server that hosts a database named DB1.
You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a virtual network named VNET1. SQLMI1 resides on VNET1.
The on-premises network connects to VNET1 by using an ExpressRoute connection.
You plan to migrate DB1 to SQLMI1 by using Azure Database Migration Service.
You need to configure VNET1 to support the migration.
What should you do?
- A. Configure service endpoints.
- B. Configure virtual network peering.
- C. Deploy an Azure firewall.
- D. Configure network security groups (NSGs).
Answer : D
Question 6
You have an on-premises Microsoft SQL server that uses the FileTables and Filestream features.
You plan to migrate to Azure SQL.
Which service should you use?
- A. Azure SQL Database
- B. SQL Server on an Azure Virtual Machine
- C. Azure SQL Managed Instance
- D. Azure Database for MySQL
Answer : B
Question 7
You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime.
What should you do?
- A. Configure Transaction Log Shipping.
- B. Implement Always On availability groups.
- C. Configure transactional replication.
- D. Import a BACPAC.
Answer : C
Question 8
You have an Azure SQL database named DB1.
You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled.
During a database audit, you discover that none of the fields contain more than 150 characters.
You need to ensure that you can apply page compression to Table1.
What should you do?
- A. Configure the columns as sparse.
- B. Change the column type to NVARCHAR(MAX).
- C. Change the column type to VARCHAR(MAX).
- D. Change the column type to VARCHAR(200).
Answer : D
Question 9
You have an on-premises Microsoft SQL Server named SQL1 that hosts five databases.
You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss.
What should you use?
- A. Always On availability groups
- B. Backup and Restore
- C. log shipping
- D. Database Migration Assistant
Answer : B
Question 10
You have an Azure subscription that contains an Azure SQL database. The database contains a table named tablet that uses partitioned columnstores.
You need to configure table1 to meet the following requirements:
• Each partition must be compressed.
• The compression ratio must be maximized.
• You must be able to index the compressed data.
What should you use?
- A. page compression
- B. columnstore compression
- C. GZIP compression
- D. columnstore archival compression
Answer : B
Question 11
You have an Azure subscription linked to an Azure Active Directory (Azure AD) tenant. The subscription contains 10 virtual machines that run Windows Server 2019 and host Microsoft SQL Server 2019 instances.
You need to ensure that you can manage the SQL Server instances by using a single user account.
What should you do first?
- A. Enable a user-assigned managed identity on each virtual machine.
- B. Deploy an Azure Active Directory Domain Services (Azure AD DS) domain and join the virtual machines to the domain.
- C. Enable a system-assigned managed identity on each virtual machine.
- D. Join the virtual machines to the Azure AD tenant.
Answer : B
Question 12
DRAG DROP
-
You have an Azure subscription.
You plan to deploy a new Azure virtual machine that will host a Microsoft SQL Server instance.
You need to configure the disks on the virtual machine. The solution must meet the following requirements:
• Minimize latency for transaction logs.
• Minimize the impact on IO throughput of the virtual machine.
Which type of disk should you use for each workload? To answer, drag the appropriate disk types to the correct workloads. Each disk type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Answer : 
Question 13
You have an Azure SQL Database elastic pool that contains 10 databases.
You receive the following alert.
Msg 1132, Level 16, state 1, Line 1
The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (76800) MBs.
You need to resolve the alert. The solution must minimize administrative effort.
Which three actions can you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
- A. Increase the maximum storage of the elastic pool.
- B. Delete data from a database.
- C. Remove a database from the pool.
- D. Enable data compression.
- E. Shrink individual databases.
Answer : ADE
Question 14
You have an Azure subscription.
You need to deploy a new Azure SQL database by using Azure Command-Line Interface (CLI).
Which three parameters are required?
- A. --name, --edition, and --capacity
- B. --name, --tier, and --min-capacity
- C. --name, --resource-group, and --server
- D. --name, --licence-type, and --capacity
Answer : C
Question 15
HOTSPOT
-
You have an Azure subscription.
You plan to migrate 10 on-premises Microsoft SQL Server instances to Azure.
You need to ensure that the migrated environment can be managed by using multiserver administration and supports master/target (MSX/TSX) jobs.
The solution must minimize administrative effort.
Which SQL deployment options should you select as the master server (MSX) and the target server (TSX)? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer : 