Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 271:

  HOTSPOT

  You have an Azure SQL database that contains a table named Customer. Customer has the columns shown in the following table.

  

  You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements:

  1.

  The first six numerals of each customer's phone number must be masked.

  2.

  The last four digits of each customer's phone number must be visible.

  3.

  Hyphens must be preserved and displayed.

  How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

  Box 1: 0 Custom String : Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix Box 2: xxx-xxx

  Box 3: 5 Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

• Question 272:

  HOTSPOT

  You have a Microsoft SQL Server database named DB1 that contains a table named Table1.

  The database role membership for a user named User1 is 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:

  

  Correct Answer:

  

  Box 1: delete a row from Table1

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

  Box 2: db_datareader

  Members of the db_datareader fixed database role can read all data from all user tables.

  Reference:

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

• Question 273:

  HOTSPOT

  You have an on-premises Microsoft SQL Server 2016 server named Server1 that contains a database named DB1.

  You need to perform an online migration of DB1 to an Azure SQL Database managed instance by using Azure Database Migration Service.

  How should you configure the backup of DB1? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Full and log backups only

  Make sure to take every backup on a separate backup media (backup files). Azure Database Migration Service doesn't support backups that are appended to a single backup file. Take full backup and log backups to separate backup files.

  Box 2: WITH CHECKSUM

  Azure Database Migration Service uses the backup and restore method to migrate your on-premises databases to SQL Managed Instance. Azure Database Migration Service only supports backups created using checksum.

  Incorrect Answers:

  NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

  UNLOAD

  Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.

  Reference:

  https://docs.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online

• Question 274:

  HOTSPOT

  You need to deploy an Azure SQL Database elastic pool by using a Bicep 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:

  

  Correct Answer:

  

  Box 1: servers

  Box 2: minCapacity

  To create a Microsoft.Sql/servers/elasticPools resource, add the following Bicep to your template.

  resource symbolicname 'Microsoft.Sql/servers/elasticPools@2022-05-01-preview' = {

  name: 'string'

  location: 'string'

  tags: {

  tagName1: 'tagValue1'

  tagName2: 'tagValue2'

  }

  sku: {

  capacity: int

  family: 'string'

  name: 'string'

  size: 'string'

  tier: 'string'

  }

  parent: resourceSymbolicName

  properties: {

  highAvailabilityReplicaCount: int

  licenseType: 'string'

  maintenanceConfigurationId: 'string'

  maxSizeBytes: int

  minCapacity: int

  perDatabaseSettings: {

  maxCapacity: int

  minCapacity: int

  }

  zoneRedundant: bool

  } }

  Reference: https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/elasticpools?pivots=deployment-language-bicep

• Question 275:

  HOTSPOT

  You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named DB1.

  You need to ensure that Group1 has read access to new tables created in DB1. The solution must use the principle of least privilege.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: SELECT

  SELECT permission means READ permissions.

  Note: GRANT Object Permissions (Transact-SQL) grants permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym.

  Syntax: GRANT [ ,...n ] ON [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ] TO [ ,...n ] [ WITH GRANT OPTION ] [ AS ]

  ON [ OBJECT :: ] [ schema_name ] . object_name Specifies the object on which the permission is being granted. The OBJECT phrase is optional if schema_name is specified. If the OBJECT phrase is used, the scope qualifier (::) is required. If schema_name is not specified, the default schema is used. If schema_name is specified, the schema scope qualifier (.) is required.

  Box 2: Schema

  Reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql

• Question 276:

  HOTSPOT

  You have an Azure subscription.

  You need to deploy a logical SQL server by using an Azure Resource Manager (ARM) template. The solution must ensure that the server will allow inbound connectivity from any Azure resource.

  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:

  

  Correct Answer:

  

  Box 1: "0.0.0.0"

  startIpAddress

  The start IP address of the firewall rule. Must be IPv4 format. Use value '0.0.0.0' for all Azure-internal IP addresses.

  SQL server firewall rules should not permit start and end IP addresses to be 0.0.0.0. Adding a rule with range 0.0.0.0 to 0.0.0.0 is the same as enabling the “Allow access to Azure services” setting, which allows all connections from Azure,

  including from other subscriptions. Disabling this setting helps prevent malicious Azure users from connecting to your database and accessing sensitive data.

  Box 2: "0.0.0.0"

  endIpAddress

  The end IP address of the firewall rule. Must be IPv4 format. Must be greater than or equal to startIpAddress. Use value '0.0.0.0' for all Azure-internal IP addresses.

  Reference:

  https://docs.fugue.co/FG_R00221.html

• Question 277:

  HOTSPOT

  You have an Azure subscription.

  You plan to migrate 10 on-premises Microsoft SQL Server instances to Azure.

  You need to ensure that the migrated environment can be managed by using multiserver administration and supports master/target (MSX/TSX) jobs.

  The solution must minimize administrative effort.

  Which SQL deployment options should you select as the master server (MSX) and the target server (TSX)? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: SQL Managed Instance MSX

  Make a Master Server Applies to: SQL Server, Azure SQL Managed Instance On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported.

  To make a master server

  1.

  Connect to the Database Engine.

  2.

  From the Standard bar, click New Query.

  3.

  Copy and paste the following example into the query window and click Execute. This example enlists the current server into the AdventureWorks1 master server. The location for the current server is Building 21, Room 309, Rack 5.

  USE msdb ; GO

  EXEC dbo.sp_msx_enlist N'AdventureWorks1', N'Building 21, Room 309, Rack 5' ; GO

  Box 2: SQL Managed Instance

  TSX

  Make a Target Server Applies to: SQL Server, Azure SQL Managed Instance On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported.

  Reference: https://learn.microsoft.com/en-us/sql/ssms/agent/make-a-master-server https://learn.microsoft.com/en-us/sql/ssms/agent/make-a-target-server

• Question 278:

  HOTSPOT

  You configure backups for an Azure SQL database 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:

  

  Correct Answer:

  

  Box 1: point-time restore (PITR) backup.

  What is point-in-time restore in Azure?

  Point-In-Time Restore allows you to restore an existing database as a new database to an earlier point in time on the same logical server using SQL Database automated backups.

  Box 2: 65 backup copies

  52 weekly, 12 monthly, and one yearly.

  Reference: https://github.com/uglide/azure-content/blob/master/articles/sql-database/sql-database-recovery-using-backups.md

• Question 279:

  HOTSPOT

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

  You need to use an Azure Automation runbook to initiate a SQL Server database backup on VM1.

  How should you complete the command? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Explanation:

  Box 1: Start-AzAutomationRunBook

  The Start-AzAutomationRunbook cmdlet starts an Azure Automation runbook job. Specify the ID or name of a runbook.

  Syntax:

  Start-AzAutomationRunbook [-Name] [-Parameters ] [-RunOn ] [-Wait] [-MaxWaitSeconds ] [-ResourceGroupName] [-AutomationAccountName] [-DefaultProfile ] []

  Example: Start a runbook job

  Start-AzAutomationRunbook -AutomationAccountName "Contoso17" -Name "Runbk01" -ResourceGroupName "ResourceGroup01"

  This command starts a runbook job for the runbook named Runbk01 in the Azure Automation account named Contoso17.

  Incorrect:

  * ADAccount Box 2: -AutomationAccountName Reference:

  https://learn.microsoft.com/en-us/powershell/module/az.automation/start-azautomationrunbook

• Question 280:

  HOTSPOT

  You have an Azure subscription that contains 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? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Explanation:

  Box 1: 'SHARED ACCESS SIGNATURE'

  No Key Vault mentioned in the question, and the only other option available for Azure Databases is SHARED ACCESS SIGNATURE.

  Note: CREATE CREDENTIAL (Transact-SQL)

  Applies to: SQL Server Azure SQL Managed Instance

  Creates a server-level credential. A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password. For example, saving

  a database backup to some location might require SQL Server to provide special credentials to access that location.

  Syntax

  CREATE CREDENTIAL credential_name WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ] [ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

  IDENTITY ='identity_name' Specifies the name of the account to be used when connecting outside the server. When the credential is used to access the Azure Key Vault, the IDENTITY is the name of the key vault.

  IDENTITY ='identity_name' Specifies the name of the account to be used when connecting outside the server. When the credential is used to access the Azure Key Vault, the IDENTITY is the name of the key vault. When the credential is

  using a shared access signature (SAS), the IDENTITY is SHARED ACCESS SIGNATURE.

  Important

  Azure SQL Database only supports Azure Key Vault and Shared Access Signature identities. Windows user identities are not supported.

  Creating a Credential using a SAS Token

  Applies to: SQL Server 2014 (12.x) through current version and Azure SQL Managed Instance.

  The following example creates a shared access signature credential using a SAS token.

  Important

  THE CREDENTIAL NAME argument requires that the name match the container path, start with https and not contain a trailing forward slash. The IDENTITY argument requires the name, SHARED ACCESS SIGNATURE. The SECRET

  argument requires the shared access signature token.

  USE master

  CREATE CREDENTIAL [https://.blob.core.windows.net/] -- this name must match the container path, start with https and must not contain a trailing forward slash. WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it. , SECRET = 'sharedaccesssignature' -- this is the shared access signature token

  GO

  Box 2: COPY_ONLY

  SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.

  The backup command should be:

  USE [master]

  BACKUP DATABASE [SQLTestDB]

  TO URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak'

  WITH COPY_ONLY, CHECKSUM

  GO

  Reference:

  https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql

  https://stackoverflow.com/questions/60967056/how-to-take-backup-of-azure-sql-managed-instance-backup-to-azure-blob