Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 221:

  HOTSPOT You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named 081. You need to ensure that Group 1 has read access to new tables created m061 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:

  

• Question 222:

  HOTSPOT

  You have an Azure SQL database named D61.

  You need to identify how much unused space in megabytes was allocated to DB1.

  How should you complete the Transact-SQL query? To answer select the appropriate options m the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: 128.0 -- Connect to database -- Database data space allocated in MB and database data space allocated unused in MB SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB FROM sys.database_files GROUP BY type_desc HAVING type_desc = 'ROWS'

  Box 2: sys.database_files

  Reference: https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-get-azure-sql-database-size/ba-p/369189

• Question 223:

  HOTSPOT

  You configure backup for an Azure SQL database as shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice the completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 224:

  HOTSPOT You need to use an Azure Resource Manager ARM) template to deploy an Azure virtual machine that will host a Microsoft SQL Server instance. The solution must maximize disk I/O performance for the SQL Server database and log files 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:

  

• Question 225:

  HOTSPOT

  You have an Azure SQL database named that contains a table named Table1.

  

  You run a query to bad data into Table1.

  The performance Of Table1 during the load operation are shown in exhibit.

  Hot Area:

  

  Correct Answer:

  

• Question 226:

  HOTSPOT

  You have an Azure subscription that is linked to an Azure AD tenant named contoso.com. The subscription contains an Azure SQL database named SQL 1 and an Azure web named app1. App1 has the managed identity feature enabled.

  You need to create a new database user for app1.

  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: [App1]

  Connect to SQL Database from .NET App Service without secrets using a managed identity

  In the SQL prompt for the database you want, run the following commands to grant the minimum permissions your app needs. For example,

  CREATE USER [] FROM EXTERNAL PROVIDER;

  ALTER ROLE db_datareader ADD MEMBER [];

  ALTER ROLE db_datawriter ADD MEMBER [];

  ALTER ROLE db_ddladmin ADD MEMBER [];

  is the name of the managed identity in Azure AD. If the identity is system-assigned, the name is always the same as the name of your App Service app.

  Box 2: EXTERNAL PROVIDER

  Reference: https://docs.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database

• Question 227:

  HOTSPOT

  You have an Azure SQL logical server.

  You run the following script.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: No

  Rows can be inserted into the table. The APPEND_ONLY = ON option is not specified.

  Ledger functionality is introduced to tables in two forms:

  *

  Updatable ledger tables, which allow you to update and delete rows in your tables.

  *

  Append-only ledger tables, which only allow insertions to your tables.

  You can create an append-only ledger table by specifying the LEDGER = ON argument in your CREATE TABLE (Transact-SQL) statement and specifying the APPEND_ONLY = ON option.

  Box 2: No

  This is not a Ledger database. The database was not created using the clause: WITH LEDGER = ON.

  Note: Enable ledger database using T-SQL

  Open a query editor like SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your logical SQL Server. The below example creates a General Purpose database. The WITH LEDGER=ON clause will create the ledger

  database.

  SQL

  CREATE DATABASE Database01

  (

  EDITION = 'GeneralPurpose',

  SERVICE_OBJECTIVE='GP_Gen5_2',

  MAXSIZE = 2 GB

  ) WITH LEDGER = ON; GO

  Ledger database Ledger databases provide an easy solution for applications that require the integrity of all data to be protected for the entire lifetime of the database. A ledger database can only contain ledger tables. Creating regular tables (that are not ledger tables) is not supported. Each table is, by default, created as an Updatable ledger table with default settings, which makes creating such tables very easy. You configure a database as a ledger database at creation. Once created, a ledger database cannot be converted to a regular database.

  Box 3: Yes A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END

  Note 1: In the example below, a new table is created with system-versioning enabled without defining the name of the history table.

  SQL

  CREATE TABLE Department (

  DeptID INT NOT NULL PRIMARY KEY CLUSTERED

  , DeptName VARCHAR(50) NOT NULL

  , ManagerID INT NULL

  , ParentDeptID INT NULL

  , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL

  , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL

  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

  )

  WITH (SYSTEM_VERSIONING = ON);

  Note 2: What is a system-versioned temporal table?

  A system-versioned temporal table is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period

  of validity for each row is managed by the system (that is, the database engine).

  Reference:

  https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-append-only-ledger-tables

  https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview

  https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-configure-ledger-database

  https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table

• Question 228:

  HOTSPOT

  You have an Azure SQL database named DB1 that contains a table named Orders. The Orders table contains a row for each sales order. Each sales order includes the name of the user who placed the order.

  You need to implement row-level security (RLS). The solution must ensure that the users can view only their respective sales orders.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: A security policy in the Orders table

  Implement RLS by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table-valued functions.

  Box 2: A table-valued function

  Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. The function is then invoked and enforced by a security policy.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

• Question 229:

  HOTSPOT

  You have a SQL Server on Azure Virtual Machines instance that hosts a database named Db1.

  You need to configure the autogrow and autoshrink settings for DB1.

  Which statements should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: ALTER DATABASE MODIFY File and Filegroup options

  With the below script we can change the database Autogrowth settings to grow data file at 512 MB and Log file at 256 MB.

  Pass logical filename of the database file for under NAME section and replace DBName with the database name.

  USE [master]

  GO

  ALTER DATABASE [DBName]

  MODIFY FILE ( NAME = N’DBName’, FILEGROWTH = 512MB )

  GO

  ALTER DATABASE [DBName]

  MODIFY FILE ( NAME = N’DBName_log’, FILEGROWTH = 256MB )

  GO

  Box 2: ALTER DATABASE SET options

  Use SET options to configure AUTO_SHRINK settings.

  Reference:

  https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/considerations-autogrow-autoshrink

• Question 230:

  HOTSPOT

  You have an Azure SQL database named DB 1 in the General Purpose service tier.

  You need to monitor DB 1 by using SQL Insights.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1 = Azure Monitor Agent Box 2 = An Azure SQL database https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview?view=azuresql