Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 311:

  DRAG DROP

  You have an Azure subscription.

  You need to deploy an Azure SQL managed instance by using an Azure Resource Manager (ARM) template. The solution must meet the following requirements:

  1.

  The SQL managed instance must be assigned a unique identity.

  2.

  The SQL managed instance must be available in the event of an Azure datacenter outage.

  How should you complete the template? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  Correct Answer:

  

  Box 1: "SystemAssigned"

  managedInstances, property value: identity

  The Azure Active Directory identity of the managed instance.

  ResourceIdentity

  The identity type. Set this to 'SystemAssigned' in order to automatically create and assign an Azure Active Directory principal for the resource.

  Possible values:

  'None'

  'SystemAssigned'

  'SystemAssigned,UserAssigned'

  'UserAssigned'

  Box 2: "zoneRedundant"

  ManagedInstanceProperties include:

  * zoneRedundant

  Whether or not the multi-az is enabled.

  Zone-redundant storage (ZRS) replicates your storage account synchronously across three Azure availability zones in the primary region. Each availability zone is a separate physical location with independent power, cooling, and networking.

  Incorrect:

  * dnsZonePartner

  The resource id of another managed instance whose DNS zone this managed instance will share after creation.

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

  https://learn.microsoft.com/en-us/azure/storage/common/storage-redundancy

• Question 312:

  DRAG DROP

  You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures.

  You need to ensure that SOL Server Agent jobs running on SQL 1 can notify administrators when a failure occurs.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

• Question 313:

  DRAG DROP

  You have a burstable Azure virtual machine named VMI that hosts an instance of Microsoft SQL Server.

  You need to attach an Azure ultra disk to VMI. The solution must minimize downtime on VMI.

  In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

• Question 314:

  DRAG DROP

  You have two instances of SQL Server on Azure virtual Machines named VM1 and VM2. VM1 hosts a database named dbl.

  You plan to create a database availability group (DAG) for dbl. The solution must use certificate authentication between VM1 and VM2.

  You need to configure authentication for the outbound connections of VM1.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

• Question 315:

  DRAG DROP

  You have an Azure SQL database named DB1.

  You need to create a partitioned table in DB1.

  Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Create partitioned tables and indexes

  Creating a partitioned table or index typically happens in three or four parts:

  1.

  Optionally create a filegroup or filegroups and corresponding data files that will hold the partitions specified by the partition scheme. The main reason to place partitions on multiple filegroups is to ensure you can independently perform backup and restore operations on filegroups. If this is not required, you may choose to assign all partitions to a single filegroup, using either an existing filegroup, such as PRIMARY, or a new filegroup with related data file(s). In nearly all scenarios, you will achieve all benefits of partitioning whether or not you use multiple filegroups.

  2.

  Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column. You can use a single partition function to partition multiple objects. (Step 1)

  3.

  Create a partition scheme that maps the partitions of a partitioned table or index to one filegroup or to multiple filegroups. You can use a single partition scheme to partition multiple objects. (Step 2)

  4.

  Create or alter a table or index and specify the partition scheme as the storage location, along with the column that will serve as the partitioning column. (Step 3)

  Aligns all indexes of the table that are partitioned with the same partition scheme. When a table and its indexes are aligned, you can move partitions in and out of partitioned tables more effectively, because your data is partitioned in the same way. Step 1: a partition function Step 2: a partition scheme Step 3: an aligned index Reference:

  https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes

• Question 316:

  DRAG DROP

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

  You plan to perform a classification scan of DB1 by using Azure Purview.

  You need to ensure that you can register DB1.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create a Purview account.

  Prerequisites

  An Azure account with an active subscription. Create an account for free.

  An active Microsoft Purview account.

  You'll need to be a Data Source Administrator and Data Reader to register a source and manage it in the Microsoft Purview governance portal.

  Step 2: Modify the Access control (IAM) settings for DB1.

  A collection is a tool that the Microsoft Purview Data Map uses to group assets, sources, and other artifacts into a hierarchy for discoverability and to manage access control. All accesses to the Microsoft Purview governance portal's

  resources are managed from collections in the Microsoft Purview Data Map

  Step 3: Create a system-assigned managed identity.

  Authentication for a scan

  To scan your data source, you'll need to configure an authentication method in the Azure SQL Database.

  The following options are supported:

  *

  System-assigned managed identity (Recommended) - This is an identity associated directly with your Microsoft Purview account that allows you to authenticate directly with other Azure resources without needing to manage a go-between user or credential set.

  *

  Etc.

  Reference: https://docs.microsoft.com/en-us/azure/purview/register-scan-azure-sql-database

• Question 317:

  DRAG DROP

  You have an Azure key vault named Vault1 and a SQL server on Azure Virtual Machines instance named SQL1. SQL1 hosts a database named DB1.

  You need to configure Transparent Data Encryption (TDE) on DB1 to use a key in Vault1.

  Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create an Azure AD service principal and grant the service principal permissions for Vault1.

  Set up SQL Server TDE Extensible Key Management by using Azure Key Vault

  Set up an Azure AD service principal

  To grant your SQL Server instance access permissions to your Azure key vault, you need a service principal account in Azure AD.

  Step 2: On SQL1, create an asymmetric key.

  Be sure to use an asymmetric RSA 2048 or 3072 key so that it's supported by SQL Server.

  Step 3: On SQL1, crate a cryptographic provider and Microsoft SQL Server credential.

  Configure SQL Server

  Create a cryptographic provider by using the SQL Server Connector, which is an EKM provider for the Azure key vault.

  Set up a SQL Server credential for a SQL Server login to use the key vault.

  Step 4: On SQL1, create a login from the asymmetric key.

  Create a new login from the asymmetric key in SQL Server.

  Reference:

  https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault

• Question 318:

  DRAG DROP

  You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures.

  You need to ensure that SQL Server Agent jobs running on SQL1 can notify administrators when a failure occurs.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create a Database Mail account

  You would need to setup email account information that contain address of the e-mail server that will actually send the emails, with login/password information required to access e-mail server. In the following script set $(mailserver),

  $(loginEmail), and $(password) information, change titles/description shown as '...' and run it:

  -- Create a Database Mail account

  EXECUTE msdb.dbo.sysmail_add_account_sp

  @account_name = '...',

  @description = '...',

  @email_address = '$(loginEmail)',

  @display_name = '...',

  @mailserver_name = '$(mailserver)' ,

  @username = '$(loginEmail)' ,

  @password = '$(password)'

  Step 2: Create a profile named AzureManagedInstance_dbmail_profile

  If you want to send e-mail using SQL Agent jobs, there should be a profile that must be called 'AzureManagedInstance_dbmail_profile'. Otherwise, Managed Instance will be unable to send emails via SQL Agent.

  Step 3: Enable email notifications upon failure.

  Sending email notifications to operators

  You can also define the operators with assigned email addresses and send emails to them using sp_notify_operator procedure. First, you need to add an operator and specify his email address:

  EXEC msdb.dbo.sp_add_operator

  @name = N'SQL DevOp',

  @enabled = 1,

  @email_address = N'$(email)',

  @weekday_pager_start_time = 080000,

  @weekday_pager_end_time = 170000,

  @pager_days = 62;

  Then, you can send an email notification to the operator:

  DECLARE @body VARCHAR(4000) = 'The email is sent using sp_notify_operator from ' + @@SERVERNAME;

  EXEC msdb.dbo.sp_notify_operator

  @profile_name = N'AzureManagedInstance_dbmail_profile',

  @name = N'SQL DevOp',

  @subject = N'Azure SQL Instance - Test Notification',

  @body = @body;

  Again, the important thing here is that you need to specify AzureManagedInstance_dbmail_profile as the email profile that will be used to send the notifications.

  Job notifications

  Reference: https://techcommunity.microsoft.com/t5/azure-sql-blog/sending-emails-in-azure-sql-managed-instance/ba-p/386235

• Question 319:

  DRAG DROP

  You create an Azure SQL managed instance and a job that performs backups.

  You need to configure the job to notify a distribution group by email when the job fails. The solution must minimize administrative effort.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer:

  

  Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance

  Using SQL Server Agent in SQL Server and SQL Managed Instance, you can create and schedule jobs that could be periodically executed against one or many databases to run Transact-SQL (T-SQL) queries and perform maintenance

  tasks.

  Step 1: Configure Database Mail

  If it isn't already enabled, first you would need to configure the Database Mail feature on SQL Managed Instance:

  GO

  EXEC sp_configure 'show advanced options', 1;

  GO

  RECONFIGURE;

  GO

  EXEC sp_configure 'Database Mail XPs', 1;

  GO

  RECONFIGURE

  Step 2: Create an operator

  You can notify the operator that something happened with your SQL Agent jobs. An operator defines contact information for an individual responsible for the maintenance of one or more instances in SQL Managed Instance. Sometimes,

  operator responsibilities are assigned to one individual.

  You can create operators using SQL Server Management Studio (SSMS) or the Transact-SQL script shown in the following example:

  EXEC msdb.dbo.sp_add_operator @name=N'AzureSQLTeam', @enabled=1, @email_address=N'[email protected]';

  Step 3: Configure a job notification

  Job notifications

  SQL Agent jobs enable you to get notifications when the job finishes successfully or fails. You can receive notifications via email.

  Example:

  You can then modify any SQL Agent job and assign operators that will be notified via email if the job completes, fails, or succeeds using SSMS or the following T-SQL script:

  EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS', @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete @notify_email_operator_name=N'AzureSQLTeam';

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

• Question 320:

  DRAG DROP

  You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a virtual machine named Server1 that runs Windows Server. SQLVM1 and Server1 are joined to an Active Directory Domain Services (AD DS) domain. Server1 hosts a file share named Share1.

  You need to ensure that a SOL Server Agent job step on SQLVM1 can access the files in Share1. The solution must use the principle of least privilege.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Explanation:

  Step 1: Create a credential

  You must create a credential before you create a proxy if one is not already available.

  Note: SQL Server Agent uses Proxies to define the security context for job steps. Basically, a proxy is an object that provides SQL Server Agent access to stored credentials for a Windows user. When running a job step that is configured to

  use a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step using that security context.

  Step 2: Create a proxy

  A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL

  Server Agent subsystem, and then assign that proxy to the job step.

  Step 3: Assign the proxy to the job step

  Reference:

  https://learn.microsoft.com/en-us/answers/questions/613796/ssis-proxy-and-credential-manager

  https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy