Microsoft DP-200 Exam Questions Instant Access

Question 1

You are a data architect. The data engineering team needs to configure a synchronization of data between an on-premises Microsoft SQL Server database to Azure SQL Database.

Ad-hoc and reporting queries are being overutilized the on-premises production instance. The synchronization process must:

Perform an initial data synchronization to Azure SQL Database with minimal downtime

Perform bi-directional data synchronization after initial synchronization

You need to implement this synchronization solution.

Which synchronization method should you use?

Atransactional replication

BData Migration Assistant (DMA)

Cbackup and restore

DSQL Server Agent job

EAzure SQL Data Sync

Answer : E

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

With Data Sync, you can keep data synchronized between your on-premises databases and Azure SQL databases to enable hybrid applications.

Compare Data Sync with Transactional Replication

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data

Question 2

You are developing a data engineering solution for a company. The solution will store a large set of key-value pair data by using Microsoft Azure Cosmos DB

The solution has the following requirements:

* Data must be partitioned into multiple containers.

* Data containers must be configured separately.

* Data must be accessible from applications hosted around the world.

* The solution must minimize latency.

You need to provision Azure Cosmos DB

AConfigure account-level throughput.

BProvision an Azure Cosmos DB account with the Azure Table API Enable geo-redundancy.

CConfigure table-level throughput

DReplicate the data globally by manually adding regions lo the Azure Cosmos DB account.

EProvision an Azure Cosmos DB account with the Azure Table API. Enable multi-region writes.

Answer : E

Scale read and write throughput globally. You can enable every region to be writable and elastically scale reads and writes all around the world. The throughput that your application configures on an Azure Cosmos database or a container is guaranteed to be delivered across all regions associated with your Azure Cosmos account. The provisioned throughput is guaranteed up by financially backed SLAs.

References:

https://docs.microsoft.com/en-us/azure/cosmos-db/distribute-data-globally

Question 3

A company has a SaaS solution that uses Azure SQL Database with elastic pools. The solution contains a dedicated database for each customer organization. Customer organizations have peak usage at different periods during the year.

You need to implement the Azure SQL Database elastic pool to minimize cost.

Which option or options should you configure?

ANumber of transactions only

BeDTUs per database only

CNumber of databases only

DCPU usage only

EeDTUs and max data size

Answer : E

The best size for a pool depends on the aggregate resources needed for all databases in the pool. This involves determining the following:

Maximum resources utilized by all databases in the pool (either maximum DTUs or maximum vCores depending on your choice of resourcing model).

Maximum storage bytes utilized by all databases in the pool.

Note: Elastic pools enable the developer to purchase resources for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases. You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

Question 4

Which counter should you monitor for real-time processing to meet the technical requirements?

AConcurrent users

BSU% Utilization

CData Conversion Errors
D.

Answer : B

Scenario:

Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.

The sales data including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics.

Streaming Units (SUs) represents the computing resources that are allocated to execute a Stream Analytics job. The higher the number of SUs, the more CPU and memory resources are allocated for your job. This capacity lets you focus on the query logic and abstracts the need to manage the hardware to run your Stream Analytics job in a timely manner.

References:

https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-streaming-unit-consumption

Question 5

You plan to use Microsoft Azure SQL Database instances with strict user access control. A user object must:

Move with the database if it is run elsewhere

Be able to create additional users

You need to create the user object with correct permissions.

Which two Transact-SQL commands should you run? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

AALTER LOGIN Mary WITH PASSWORD = 'strong_password';

BCREATE LOGIN Mary WITH PASSWORD = 'strong_password';

CALTER ROLE db_owner ADD MEMBER Mary;

DCREATE USER Mary WITH PASSWORD = 'strong_password';

EGRANT ALTER ANY USER TO Mary;

Answer : C, D

C: ALTER ROLE adds or removes members to or from a database role, or changes the name of a user-defined database role.

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server.

D: CREATE USER adds a user to the current database.

Note: Logins are created at the server level, while users are created at the database level. In other words, a login allows you to connect to the SQL Server service (also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server roles (for example, serveradmin) and the database users will be assigned to roles within that database (eg. db_datareader, db_bckupoperator).

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql

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

Question 6

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 scenario, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a container named Sales in an Azure Cosmos DB database. Sales has 120 GB of dat

a. Each entry in Sales has the following structure.

The partition key is set to the OrderId attribute.

Users report that when they perform queries that retrieve data by ProductName, the queries take longer than expected to complete.

You need to reduce the amount of time it takes to execute the problematic queries.

Solution: You create a lookup collection that uses ProductName as a partition key and OrderId as a value.

Does this meet the goal?

AYes

BNo

Answer : A

One option is to have a lookup collection ''ProductName'' for the mapping of ''ProductName'' to ''OrderId''.

References:

https://azure.microsoft.com/sv-se/blog/azure-cosmos-db-partitioning-design-patterns-part-1/

Question 7

A company manages several on-premises Microsoft SQL Server databases.

You need to migrate the databases to Microsoft Azure by using a backup and restore process.

Which data technology should you use?

AAzure SQL Database single database

BAzure SQL Data Warehouse

CAzure Cosmos DB

DAzure SQL Database Managed Instance

Answer : D

Managed instance is a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers. The managed instance deployment model allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance

Microsoft Implementing an Azure Data Solution DP-200 Exam Questions