Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 61:

  You have an Azure SQL database named DB1.

  A user named User 1 has an Azure AD account.

  You need to provide User1 with the ability to add and remove columns from the tables m DBV The solution must use the principle of least privilege.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point

  A. Assign the database user the db_owner role.

  B. Create a contained database user.

  C. Create a login and an associated database user.

  D. Assign the database user the db_ddladmin role.

  Correct Answer: BD

  B. Create a contained database user.

  D. Assign the database user the db_ddladmin role.

• Question 62:

  You have an Azure subscription that contains an Azure SQL database named SQL1.

  SQL1 is in an Azure region that does not support availability zones.

  You need to ensure that you have a secondary replica of SQL1 in the same region.

  What should you use?

  A. log shipping

  B. auto-failover groups

  C. active geo-replication

  D. Microsoft SQL Server failover clusters

  Correct Answer: C

  Active geo-replication is a feature that lets you to create a continuously synchronized readable secondary database for a primary database. The readable secondary database may be in the same Azure region as the primary, or, more commonly, in a different region. This kind of readable secondary databases are also known as geo-secondaries, or geo-replicas.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview

• Question 63:

  Your on-premises network contains a Microsoft SQL Server 2016 server that hosts a database named db1.

  You have an Azure subscription.

  You plan to migrate db1 to an Azure SQL managed instance.

  You need to create the SQL managed instance. The solution must minimize the disk latency of the instance.

  Which service tier should you use?

  A. Hyperscale

  B. General PurpAose

  C. Premium

  D. Business Critical

  Correct Answer: D

  General purpose has disk latency, Business critical puts the logs on SSD, Premium is a DTU offering which is not supported on Azure SQL MI, Hyperscale is not on Managed Instance

• Question 64:

  Your on-premises network contains a server that hosts a 60-TB database named DB 1.

  The network has a 10-Mbps internet connection.

  You need to migrate DB 1 to Azure. The solution must minimize how long it takes to migrate the database.

  What should you use?

  A. Azure Migrate

  B. Data Migration Assistant (DMA)

  C. Azure Data BOX

  D. Azure Database Migration Service

  Correct Answer: C

  Microsoft Azure Data Box is a hardware appliance designed to allow customers to import or export large amounts of data — more than 40TB — into and out of Azure offline. It is especially helpful when there is zero or limited network

  connectivity. Microsoft ships customers a proprietary Data Box storage device with a rugged casing to protect and secure data during the transit. A customer may choose Data Box for a one-time or the occasional cloud migration or an initial

  bulk data transfer followed by periodic transfers.

  Considerations for Cloud Migrations Using Azure Data Box

  Azure Data Box is a good solution to consider if online data transfer is not possible either because the network bandwidth is limited or because it can take too long.

  There are three different types of physical Data Box solutions based on data size:

  Data Box: This device has 100TB capacity and uses standard NAS protocols and common copy tools. It features AES 256-bit encryption for safer transit.

  Data Box Heavy: This larger device is designed to lift 1PB of data to the cloud.

  Data Box Discs: Discs have capacity of 8TB SSD with a USB/SATA interface featuring 128-bit encryption. Customers can buy in packs of up to five for a total of 40TB.

  Reference:

  https://www.komprise.com/glossary_terms/azure-data-box/

• Question 65:

  You have two on-premises Microsoft SQL Server 2019 instances named SQL1 and SQL2.

  You need to migrate the databases hosted on SQL1 to Azure. The solution must meet the following requirements:

  1.

  The service that hosts the migrated databases must be able to communicate with SQL2 by using linked server connections.

  2.

  Administrative effort must be minimized. What should you use to host the databases?

  A. a single Azure SQL database

  B. an Azure SQL Database elastic pool

  C. SQL Server on Azure Virtual Machines

  D. Azure SQL Managed Instance

  Correct Answer: D

  Linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.

  Linked servers are available in SQL Server Database Engine and Azure SQL Managed Instance. They are not enabled in Azure SQL Database singleton and elastic pools.

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine

• Question 66:

  You have a Microsoft SQL Server 2017 server.

  You need to migrate the server to Azure. The solution must meet the following requirements:

  Ensure that the latest version of SQL Server is used.

  Support the SQL Server Agent service.

  Minimize administrative effort.

  What should you use?

  A. an Azure SQL Database elastic pool

  B. Azure SQL Database

  C. SQL Server on Azure Virtual Machines

  D. Azure SQL Managed Instance

  Correct Answer: D

  Common solution from all 3 requirements is Azure SQL Managed Instance.

  Ensure that the latest version of SQL Server is used - Possible solutions - both MI and Azure SQL DB

  •

  Support the SQL Server Agent service - Possible solutions - both MI and VM

  •

  Minimize administrative effort - Possible solutions - both MI and Azure SQL DB

• Question 67:

  You have an Azure SQL database named DB1 that contains a private certificate named Sales. The private key for Sales is encrypted with a password. You need to change the password for the private key.

  Which Transact-SQL statement should you run?

  A. ALTER CERTIFICATE Sales WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘ EWYx9Xk+$#’);

  B. ALTER CERTIFICATE Sales WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = ‘ 6YY9YcD!pV’);

  C. ALTER CERTIFICATE Sales WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘Mb^68Kand*w%’),ENCRYPTION BY PASSWORD = ‘ 6YY9YcD!pV’);

  D. ALTER CERTIFICATE Sales WITH PRIVATE KEY (FILE = ‘D:\importkeys\SalesNew,(DECRYPTION BY PASSWORD = ‘ Mb^68Kand*w%’);

  Correct Answer: C

  Azure SQL database ALTER CERTIFICATE changes the password used to encrypt the private key of a certificate, removes the private key, or imports the private key if none is present.

  Example, changing the password that is used to encrypt the private key:

  ALTER CERTIFICATE Shipping11

  WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '95hkjdskghFDGGG4%',

  ENCRYPTION BY PASSWORD = '34958tosdgfkh##38');

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

• Question 68:

  You have an Azure subscription that contains the resources shown in the following table.

  

  You need to configure a connection between VM1 and MIL The solution must meet the following requirements:

  1.

  The connection must be encrypted.

  2.

  Network latency must be minimized. What should you implement?

  A. virtual network peering

  B. private endpoints

  C. service endpoints

  D. a site-to-site VPN

  Correct Answer: B

• Question 69:

  You have an Azure subscription that contains the resources shown in the following table.

  

  App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity. You need to modify db1 to resolve the issues experienced by App1 as soon as possible, without considering immediate costs. What should you do?

  A. Enable automatic tuning for db1.

  B. Increase the number of vCores allocated to db1.

  C. Decrease the auto-pause delay for db1.

  D. Disable auto-pause delay for db1.

  Correct Answer: D

  Applications that connect to a cloud service such as Azure SQL Database should expect periodic reconfiguration events and implement retry logic to handle these errors instead of surfacing application errors to users.

  Auto-pause delay

  In the Azure Serverless architecture, the database automatically pauses if it is inactive during the specified duration. By default, it is enabled and set to 1 hour (3600 seconds).

  Note: Serverless compute tier

  The serverless compute tier for single databases in Azure SQL Database is parameterized by a compute autoscaling range and an auto-pause delay. The configuration of these parameters shapes the database performance experience and

  compute cost.

  The auto-pause delay is a configurable parameter that defines the period of time the database must be inactive before it is automatically paused. The database is automatically resumed when the next login or other activity occurs.

  Alternatively, automatic pausing can be disabled.

• Question 70:

  You have an Azure subscription that contains a logical SQL server named Server1. The master database of Server1 contains a user named User1. You need to ensure that User1 can create databases on Server1.

  Which database role should you assign to User1?

  A. db_owner

  B. dbmanager

  C. dbo

  D. db_ddladmin

  Correct Answer: B

  dbmanager: Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles