DP-300: Administering Microsoft Azure SQL SolutionsDemo

You have an Azure subscription.

You need to deploy an Azure SQL resource that supports cross-database queries by using an Azure Resource Manager (ARM) template. How should you complete the ARM template?

"resources": [
  ...
  "type": "Select…",
  "name": "[parameters('targetName')]",
  "location": "[parameters('location')]",
  "sku": {
    "name": "[parameters('skuName')]"
  },
  ...
  "dependsOn": [
    "Select…"
  ],
  "properties": {
    "administratorLogin": "[parameters('administratorLogin')]",
    "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
    "subnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworkName'),
      parameters('virtualNetworkName'), parameters('subnetName'))]",
    "storageSizeInGB": "[parameters('storageSizeInGB')]", "vCores": "[parameters('vCores')]",
    "licenseType": "[parameters('licenseType')]"
  }
  ...
]

You have an Azure subscription that includes an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.

You need to collect performance metrics for SQLMI1 and stream those metrics to Workspace.

What should you do first?

You have an Azure subscription that contains an Azure Data Factory version 2 (V2) data factory named df1. DF1 includes a linked service.

You have an Azure Key Vault named vault1 that contains an encryption key named key1.

You need to encrypt df1 by using key1.

What should you do first?

You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that was deployed using an Azure Marketplace SQL Server 2019 Enterprise image.

You need to change the Microsoft SQL Server instance on SQLVM1 to the Standard edition. The solution must maintain licensing compliance.

What should you do first?

You have an Azure subscription.

You plan to deploy a new Azure virtual machine that will host a Microsoft SQL Server instance.

You must configure the virtual machine disks to meet these requirements:

• Minimize latency for transaction logs.
• Minimize the impact on the virtual machine's IO throughput.

Which disk type should be used for each workload?

Each disk type may be used once, more than once, or not at all.

You have an Azure SQL database that includes an Employees table with a Salary column.

You need to encrypt the Salary column. The solution must prevent database administrators from reading the Salary data and provide the most secure encryption.

Which three actions should you perform in sequence?

You have 50 Azure SQL databases.

You need to notify the database owner when Azure modifies database settings, such as database size and pricing tier.

What should you do?

You have an on-premises Microsoft SQL Server database named DB1 and an Azure subscription.

You need to migrate DB1 to an Azure SQL service that meets these requirements:

• Protect the confidentiality of sensitive data from malware and unauthorized database administrators with high privileges.
• Support pattern matching for server-side database operations.
• Use hardware-based encryption technology.

Which Azure SQL service and attestation service should be included in the solution?

You have a SQL Server instance running on Azure Virtual Machines that contains a database named DB1.

You plan to implement Azure SQL Data Sync for DB1.

Which isolation level should you configure?

You have an Azure virtual machine named Server1 running Windows Server 2022. Server1 hosts a Microsoft SQL Server 2022 instance named SQL1 and a database named DB1.

You create a master key in the master database on SQL1.

You need to create an encrypted backup of DB1.

What should you do?

You have an on-premises Microsoft SQL Server instance named SQL1 that hosts five databases.

You need to migrate the databases to an Azure SQL managed instance. The solution must:

• Minimize downtime
• Prevent data loss

What should you use?

You have an Azure subscription containing a SQL Server on Azure Virtual Machines instance named SQLVM1 and a Windows Server virtual machine named Server1. SQLVM1 and Server1 are joined to an Active Directory Domain Services (AD DS) domain. Server1 hosts a file share named Share1.

Ensure that a SQL Server Agent job step on SQLVM1 can access the files in Share1 while following the principle of least privilege.

Which three actions should you perform, in order?

You have an Azure subscription containing a storage account named databasebackups.

You have an Azure SQL Managed Instance named DB1.

You need to back up DB1 to databasebackups.

How should you complete the commands?

CREATE CREDENTIAL
[https://databasebackups.blob.core.windows.net/Backups]
WITH IDENTITY = Select… ,
SECRET = 'sp=r&st=2023-02-02T19:23:08Z&se=2033-02-
02T19:30:08Z&spr=https&sv=2021-06-
08&sr=b&sig=B%2FxEYQiOC%4BqyYCeqwHSz2QpRI%2FKcg3ZABz78J2kix3JZjk%3D'

BACKUP DATABASE DB1

TO URL =

'https://databasebackups.blob.core.windows.net/Backups/db1.bak'

WITH Select…

You have an Azure SQL database named DB1 that includes a nonclustered index named index1.

End users report slow queries when using index1.

You need to identify the operations being performed on the index.

Which dynamic management view should you use?

You have SQL Server 2019 on an Azure virtual machine running Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

You scale up the virtual machine to 16 vCPUs and 64 GB of memory.

You need to deliver the lowest latency for tempdb.

What total number of data files should tempdb contain?

You manage an enterprise data warehouse in Azure Synapse Analytics.

Users report slow performance when running commonly used queries. They do not report performance changes for infrequently used queries.

You need to monitor resource utilization to identify the source of the performance issues.

Which metric should you monitor?

You have an Azure SQL Database managed instance that begins to experience performance problems.

You need to identify the query causing the problem and obtain that query’s execution plan. The solution must minimize administrative effort.

What should you use?

You have an Azure SQL Database elastic pool that includes 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 while minimizing administrative effort.

Which three actions can you take? Each correct answer provides a complete solution.

NOTE: Each correct selection is worth one point.

You plan to migrate two 100-GB databases to Azure.

You need to dynamically scale resource consumption according to workloads. The solution must minimize downtime during scaling operations.

What should you use?

You have an Azure SQL database named DB1. You need to determine how much unused space, in megabytes, has been allocated to DB1. How should you complete the Transact-SQL query?

SELECT SUM(size/Select… - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)
AS DatabaseDataSpaceAllocatedUnusedInMB
FROM Select…
GROUP BY type_desc
HAVING type_desc = 'ROWS';

You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance hosts a 4-TB database named DB1.

You plan to migrate DB1 to an Azure SQL Database managed instance.

What should you use to minimize downtime and data loss during the migration?

You have an Azure subscription.

You need to deploy two SQL Server instances on Azure virtual machines in a highly available configuration that uses an Always On availability group. The solution must meet these requirements:

• Minimize the time required for failover.
• Preserve existing connections to the primary replica during failover.

What should you do?

You have an Azure subscription.

You need to deploy an Azure SQL Managed Instance that meets these requirements:

• Optimize latency.
• Maximize the memory-to-vCore ratio.

Which service tier and hardware generation should you use?

You have an Azure SQL database named DB1.

You have a table named Table1 that contains 20 columns of type CHAR(400). Row compression is enabled for Table1.

During a database audit, you find that none of the fields contains more than 150 characters.

You need to ensure that page compression can be applied to Table1.

What should you do?