Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 11:

  You plan to deploy an Azure SQL managed instance. You need to restore database backups across regions. Which type of storage account should you use?

  A. locally-redundant storage (LRS)

  B. zone-redundant storage (ZRS)

  C. geo-zone-redundant storage (GZRS)

  D. geo-redundant storage (GRS)

  Correct Answer: D

  Geo-restore

  You can use geo-restore to restore a deleted database by using the Azure portal, the Azure CLI, Azure PowerShell, and the REST API.

  Geo-restore uses geo-replicated backups as the source. You can restore a database on any logical server in any Azure region from the most recent geo-replicated backups. You can request a geo-restore even if an outage has made the

  database or the entire region inaccessible.

  Note: Geo-restore is available only for databases configured with geo-redundant backup storage.

  Backup storage redundancy changes made to existing databases apply to future backups only. The default value is geo-redundant storage. There are differences in pricing between locally redundant, zone-redundant, and geo-redundant

  backup storage.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups

  https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-change-settings

• Question 12:

  You have an instance of SQL Server on Azure Virtual Machines named VM1.

  You need to implement a disaster recovery solution that meets the following requirements:

  1.

  Returns the solution to an operational state within 15 minutes of a failure

  2.

  Can perform disaster recovery testing in an isolated environment

  3.

  Minimizes administrative effort What should you include in the solution?

  A. active geo-replication

  B. auto-failover groups

  C. Azure Site Recovery

  D. a failover cluster instance (FCI)

  Correct Answer: C

  Run a test failover (disaster recovery drill) to Azure.

  You can a disaster recovery drill to Azure, using a Site Recovery test failover.

  You run a test failover to validate your replication and disaster recovery strategy, without any data loss or downtime. A test failover doesn't impact ongoing replication, or your production environment. You can run a test failover on a specific

  virtual machine (VM), or on a recovery plan containing multiple VMs.

  Reference:

  https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-test-failover-to-azure

• Question 13:

  You have an Azure subscription that contains three instances of SQL Server on Azure Virtual Machines.

  You plan to implement a disaster recovery solution.

  You need to be able to perform disaster recovery drills regularly. The solution must meet the following requirements:

  1.

  Minimize administrative effort for the recovery drills.

  2.

  Isolate the recovery environment from the production environment What should you use?

  A. native Microsoft SQL Server backup

  B. Azure Site Recovery

  C. Recovery Services vaults

  D. Azure Backup

  Correct Answer: B

  Set up disaster recovery for SQL Server

  You can protect the SQL Server back end of an application. You do so by using a combination of SQL Server business continuity and disaster recovery (BCDR) technologies and Azure Site Recovery.

  SQL Server disaster recovery capabilities include:

  Failover clustering

  Always On availability groups

  Database mirroring

  Log shipping

  Active geo-replication

  Auto-failover groups

  Note: Azure Recovery Services contributes to your BCDR strategy:

  Site Recovery service: Site Recovery helps ensure business continuity by keeping business apps and workloads running during outages. Site Recovery replicates workloads running on physical and virtual machines (VMs) from a primary site

  to a secondary location. When an outage occurs at your primary site, you fail over to a secondary location, and access apps from there. After the primary location is running again, you can fail back to it.

  Backup service: The Azure Backup service keeps your data safe and recoverable.

  Site Recovery can manage replication for:

  Azure VMs replicating between Azure regions Replication from Azure Public Multi-Access Edge Compute (MEC) to the region Replication between two Azure Public MECs On-premises VMs, Azure Stack VMs, and physical servers

  Reference: https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-sql https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-overview

• Question 14:

  You are modifying an existing disaster recovery solution for an Azure SQL managed instance that contains a failover group named FG1.

  You need to ensure the maximum in-transit time for FG1 when an automatic failover oсcurs.

  What should you configure?

  A. an availability group

  B. a secondary managed instance

  C. a failover policy

  D. a grace period

  Correct Answer: D

  Configuring Auto-Failover group on Azure SQL server Select the read/write failover policy. Select Automatic if you need automatic failover when the primary goes down. Select the read-write grace period. As the data is replicated asynchronously, the immediate failover may result in data loss. So, we must set the grace period accordingly to reduce data loss. The grace period is the time the SQL service waits before triggering automatic failover

  when an outage occurs.

  Reference:

  https://www.sqlshack.com/azure-sql-server-auto-failover-groups

• Question 15:

  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:

  1.

  Each partition must be compressed.

  2.

  The compression ratio must be maximized.

  3.

  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

  Correct Answer: D

  SQL Server, Azure SQL Database, and Azure SQL Managed Instance support row and page compression for rowstore tables and indexes, and support columnstore and columnstore archival compression for columnstore tables and indexes.

  For columnstore tables and indexes, all columnstore tables and indexes always use columnstore compression and this is not user configurable.

  Compressing columnstore indexes with archival compression, causes the index to perform slower than columnstore indexes that do not have the archival compression. Use archival compression only when you can afford to use extra time and

  CPU resources to compress and retrieve the data.

  The benefit of archival compression, is reduced storage, which is useful for data that is not accessed frequently. For example, if you have a partition for each month of data, and most of your activity is for the most recent months, you could archive older months to reduce the storage requirements. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

• Question 16:

  You deploy an instance of SQL Server on Azure Virtual Machines: named SQL1 that hosts multiple databases.

  You configure the full recovery model for all the databases.

  You perform a full backup of the master database on SQL1.

  You need to a perform an additional backup of the master database on SQL1. The solution must minimize how long it takes to perform the backup.

  Which type of backup should you perform?

  A. log

  B. full

  C. differential

  D. tail-log

  Correct Answer: B

  Answer is B. running differential/log backup will give you error:

  Msg 3024, Level 16, State 0, Line 1

  You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.

  Msg 3013, Level 16, State 1, Line 1

  BACKUP DATABASE is terminating abnormally.

• Question 17:

  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

  Correct Answer: C

  az sql db create Create a database

  Syntax: az sql db create --name --resource-group --server

  Plus many additonal optional parameters.

  Required Parameters

  --name -n

  Name of the Azure SQL Database.

  --resource-group -g

  Name of resource group. You can configure the default group using az configure --defaults group=.

  --server -s

  Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=.

  Example:

  echo "Creating $database on $server..."

  az sql db create --resource-group $resourceGroup --server $server --name $database --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2 --zone-redundant true # zone redundancy is only supported on

  premium and business critical service tiers

  Reference:

  https://learn.microsoft.com/en-us/cli/azure/sql/db

• Question 18:

  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.

  Correct Answer: ACE

  A: Change elastic pool storage size

  *

  vCore-based purchasing model

  Storage can be provisioned up to the max size limit:

  For storage in the Standard or General Purpose service tiers, increase or decrease size in 10-GB increments

  For storage in the Premium or Business Critical service tiers, increase or decrease size in 250-GB increments

  Storage for an elastic pool can be provisioned by increasing or decreasing its max size.

  *

  DTU-based purchasing model

  The eDTU price for an elastic pool includes a certain amount of storage at no additional cost. Extra storage beyond the included amount can be provisioned for an additional cost up to the max size limit in increments of 250 GB up to 1 TB,

  and then in increments of 256 GB beyond 1 TB.

  C: You can add or remove databases to/from the pool.

  E: Under some circumstances, you may need to shrink a database to reclaim unused space.

  Reclaim unused allocated space

  Shrink commands impact database performance while running, and if possible should be run during periods of low usage.

  Reference:

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

  https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage

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

• Question 19:

  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.

  Correct Answer: A

  Benefits of using user-assigned managed identities (UMI) There are several benefits of using UMI as a server identity.

  *

  User flexibility to create and maintain their own user-assigned managed identities for a given tenant. UMI can be used as server identities for Azure SQL. UMI is managed by the user, compared to an SMI, which identity is uniquely defined per server, and assigned by the system.

  *

  Users can choose a specific UMI to be the server or instance identity for all SQL Databases or Managed Instances in the tenant, or have multiple UMIs assigned to different servers or instances. For example, different UMIs can be used in different servers representing different features. For example, a UMI serving transparent data encryption in one server, and a UMI serving Azure AD authentication in another server.

  *

  Etc Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql

• Question 20:

  You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.

  You need to ensure that User1 can create a scheduled task to perform a full backup of DB1. The solution must use the principle of least privilege.

  Which built-in database role should you assign to User1?

  A. db_owner

  B. SQLAgentReaderRole

  C. SQLAgentUserRole

  D. SQLAgentOperatorRole

  Correct Answer: C