Administering Relational Databases on Microsoft Azure v1.0

Page:    1 / 15   
Exam contains 220 questions

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Does this meet the goal?

  • A. Yes
  • B. No


Answer : A

Explanation:
You can use the Get Metadata activity to retrieve the metadata of any data in Azure Data Factory or a Synapse pipeline. You can use the output from the Get
Metadata activity in conditional expressions to perform validation, or consume the metadata in subsequent activities.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: You use an Azure Synapse Analytics serverless SQL pool to create an external table that has an additional DateTime column.
Does this meet the goal?

  • A. Yes
  • B. No


Answer : B

Explanation:
This is not about an external table.
Instead, in an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Note: You can use the Get Metadata activity to retrieve the metadata of any data in Azure Data Factory or a Synapse pipeline. You can use the output from the
Get Metadata activity in conditional expressions to perform validation, or consume the metadata in subsequent activities.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: You use a dedicated SQL pool to create an external table that has an additional DateTime column.
Does this meet the goal?

  • A. Yes
  • B. No


Answer : B

Explanation:
Instead, in an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Note: You can use the Get Metadata activity to retrieve the metadata of any data in Azure Data Factory or a Synapse pipeline. You can use the output from the
Get Metadata activity in conditional expressions to perform validation, or consume the metadata in subsequent activities.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

HOTSPOT -
You are provisioning an Azure SQL database in the Azure portal as shown in the following exhibit.


Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:



Answer :

Explanation:

Box 1: no extra time -
Auto Pause is not checked in the exhibit.
Note: If Auto Pause is checked the correct answer is: up to one minute
Box 2: intermittent and unpredictable
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview

You plan to deploy an app that includes an Azure SQL database and an Azure web app. The app has the following requirements:
✑ The web app must be hosted on an Azure virtual network.
✑ The Azure SQL database must be assigned a private IP address.
✑ The Azure SQL database must allow connections only from a specific virtual network.
You need to recommend a solution that meets the requirements.
What should you include in the recommendation?

  • A. Azure Private Link
  • B. a network security group (NSG)
  • C. a database-level firewall
  • D. a server-level firewall


Answer : A

Explanation:
Private Link allows you to connect to various PaaS services in Azure via a private endpoint.
A private endpoint is a private IP address within a specific VNet and subnet.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview

You are planning a solution that will use Azure SQL Database. Usage of the solution will peak from October 1 to January 1 each year.
During peak usage, the database will require the following:
✑ 24 cores
✑ 500 GB of storage
✑ 124 GB of memory
✑ More than 50,000 IOPS
During periods of off-peak usage, the service tier of Azure SQL Database will be set to Standard.
Which service tier should you use during peak usage?

  • A. Business Critical
  • B. Premium
  • C. Hyperscale


Answer : A

Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-single-databases#business-critical---provisioned-compute---gen4

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 :

Explanation:
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

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

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 :

Explanation:

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

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 :

Explanation:
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

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 : A

Explanation:
During virtual network setup, if you use ExpressRoute with network peering to Microsoft, add the following service endpoints to the subnet in which the service will be provisioned:
* Target database endpoint (for example, SQL endpoint, Cosmos DB endpoint, and so on)
* Storage endpoint
* Service bus endpoint
This configuration is necessary because Azure Database Migration Service lacks internet connectivity.
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-managed-instance

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

Explanation:
SQL Server VM alternative.
Your business might have requirements that make SQL Server on Azure Virtual Machines a more suitable target than Azure SQL Database.
If one of the following conditions applies to your business, consider moving to a SQL Server virtual machine (VM) instead:
* You have strict dependency on features that are still not supported, such as FileStream/FileTable, PolyBase, and cross-instance transactions.
* Etc.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-overview

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

Explanation:
Use Transactional Replication.
When you can't afford to remove your SQL Server database from production while the migration is occurring, you can use SQL Server transactional replication as your migration solution.
Note: There are two primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database. The first method (database copy or BACPAC importation) is simpler but requires some, possibly substantial, downtime during the migration. The second method (transactional replication) is more complex, but substantially eliminates downtime during the migration.
Incorrect:
Not D: The import BACPAC method includes downtime during migration.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/migrate-to-database-from-sql-server#method-1-migration-with-downtime-during-the-migration

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

Explanation:
We reduce the max length of the column from 400 to 200.
Incorrect:
Not A: Sparse column is useful when there are many null columns.
The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.
Not B, Not C: SQL Server 2005 got around the limitation of 8KB storage size and provided a workaround with varchar(max). It is a non-Unicode large variable- length character data type and can store a maximum of 2^31-1 bytes (2 GB) of non-Unicode characters.
Reference:
https://www.sqlshack.com/sql-varchar-data-type-deep-dive/
https://36chambers.wordpress.com/2020/06/18/nvarchar-everywhere-a-thought-experiment/

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 : D

Explanation:
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
Capabilities include:
Assess on-premises SQL Server instance(s) migrating to Azure SQL database(s).
Note: For large migrations (in terms of number and size of databases), we recommend that you use the Azure Database Migration Service, which can migrate databases at scale.
Migrate an on-premises SQL Server instance to a modern SQL Server instance hosted on-premises or on an Azure virtual machine (VM) that is accessible from your on-premises network.
Incorrect:
Not B: Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime.
Not C: What is the purpose of log shipping?
Similar to replication, the primary purpose of log shipping is to increase database availability by maintaining a backup server that can replace a production server quickly.
Reference:
https://docs.microsoft.com/en-us/sql/dma/dma-overview
https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-guide

Page:    1 / 15   
Exam contains 220 questions

Talk to us!


Have any questions or issues ? Please dont hesitate to contact us

Certlibrary doesn't offer Real Microsoft Exam Questions.
Certlibrary Materials do not contain actual questions and answers from Cisco's Certification Exams.
CFA Institute does not endorse, promote or warrant the accuracy or quality of Certlibrary. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.