DP-300 practice questions 141–150

1. Question 141 · 354 in bank

   HOTSPOT - You have an Azure SQL database named DB1. You have 10 Azure virtual machines that connect to a virtual network subnet named Subnet1. You need to implement a database-level firewall that meets the following requirements: • Ensures that only the 10 virtual machines can access DB1 • Follows the principle of least privilege How should you configure the firewall rule, and how should you establish network connectivity from the virtual machines to DB1? To answer, select the appropriate options in the answer area.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Firewall Rule C) Allow traffic from a specific Virtual Network This option is the most secure and aligns with the principle of least privilege by restricting access to only the specified virtual network, which contains your VMs. By allowing traffic from a specific Virtual Network, you ensure that only resources within that network can access the database, minimizing exposure to unauthorized access. Network Connectivity B) Create a private endpoint Creating a private endpoint allows secure connectivity to DB1 from your VMs over the Azure backbone network. This method ensures that traffic does not traverse the public internet, enhancing security and performance. It also simplifies network management by using private IP addresses for communication.

   Source: Adapted from community discussion — Misawakazuki

   References

   • Microsoft Learn

   

   Next question

2. Question 142 · 354 in bank

   You have an Azure subscription that contains an Azure SQL database named DB1. You need to host elastic jobs by using DB1. DB1 will also be configured as a job target. The solution must support the use of location-based Conditional Access policies. What should the elastic jobs use to access DB1?

   • Aa system-assigned managed identity
   • BAzure SQL sign-in credentials
   • Cdatabase-scoped credentials
   • Da user-assigned managed identity

   Show answer

   Answer: D

   Next question

3. Question 143 · 354 in bank

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have the on-premises networks shown in the following table.

   

   You have an Azure subscription that contains an Azure SQL Database server named SQL1. SQL1 contains two databases named DB1 and DB2. You need to configure access to DB1 and DB2. The solution must meet the following requirements: • Ensure that DB1 can be accessed only by users in Branch1. • Ensure that DB2 can be accessed only by users in Branch2. Solution: You connect to DB1 and run the following command. EXECUTE sp_set_firewall_rule ‘Allow db1 users’, ‘131.107.10.0’, ‘131.107.10.255’ You connect to DB2 and run the following command. EXECUTE sp_set_database_firewall_rule ‘Allow db2 users’, ‘131.107.11.0’, ‘131.107.11.255’ Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Next question

4. Question 144 · 354 in bank

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have the on-premises networks shown in the following table.

   

   You have an Azure subscription that contains an Azure SQL Database server named SQL1. SQL1 contains two databases named DB1 and DB2. You need to configure access to DB1 and DB2. The solution must meet the following requirements: • Ensure that DB1 can be accessed only by users in Branch1. • Ensure that DB2 can be accessed only by users in Branch2. Solution: You connect to the master of SQL1 and run the following command. EXECUTE sp_set_firewall_rule ‘Allow db1 and db2 users’, ‘131.107.11.0’, ‘131.107.11.255’ Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Next question

5. Question 145 · 354 in bank

   HOTSPOT - You have an Azure SQL database named DB1 that contains two tables named Table1 and Table2. Both tables contain a column named a Column1. Column1 is used for joins by an application named App1. You need to protect the contents of Column1 at rest, in transit, and in use. How should you protect the contents of Column1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Column encryption Key - Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. Incorrect Answers: TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). Box 2: Deterministic - Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption. Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. Incorrect Answers: ✑ Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns. ✑ Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

   Source: Adapted from community discussion — Volhva

   References

   • Microsoft Learn

   

   Next question

6. Question 146 · 354 in bank

   Select 2

   You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server. You need to ensure that each customer can only connect to and access their respective database. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • AImplement row-level security (RLS).
   • BCreate users in each database.
   • CConfigure the database firewall.
   • DConfigure the server firewall.
   • ECreate logins in the master database.
   • FImplement Always Encrypted.

   Show answer

   Answer: BC

   Explanation

   The key is "only connect to and access their respective database", which means Contained DB's. In that case creating in each DB is the right choice.

   Source: Adapted from community discussion — Mend

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 147 · 354 in bank

   DRAG DROP - You have an Azure SQL Database instance named DatabaseA on a server named Server1. You plan to add a new user named App1 to DatabaseA and grant App1 db_datareader permissions. App1 will use SQL Server Authentication. You need to create App1. The solution must ensure that App1 can be given access to other databases by using the same credentials. 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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Step 1: On the master database, run CREATE LOGIN [App1] WITH PASSWORD = 'p@aaW0rd!' Logins are server wide login and password pairs, where the login has the same password across all databases. Here is some sample Transact-SQL that creates a login: CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a'; You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command. Step 2: On DatabaseA, run CREATE USER [App1] FROM LOGIN [App1] Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user: CREATE USER readonlyuser FROM LOGIN readonlylogin; Step 3: On DatabaseA run ALTER ROLE db_datareader ADD Member [App1] Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the readonlyuser is given read only permissions to the database via the db_datareader role. EXEC sp_addrolemember 'db_datareader', 'readonlyuser'; Reference: https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

   

   Next question

8. Question 148 · 354 in bank

   Which windowing function should you use to perform the streaming aggregation of the sales data?

   • ASliding
   • BHopping
   • CSession
   • DTumbling

   Show answer

   Answer: D

   Explanation

   Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.

   Source: Adapted from community discussion — eric0718

   Next question

9. Question 149 · 354 in bank

   Which counter should you monitor for real-time processing to meet the technical requirements?

   • ASU% Utilization
   • BCPU% utilization
   • CConcurrent users
   • DData Conversion Errors

   Show answer

   Answer: B

   References

   • Microsoft Learn

   Next question

10. Question 150 · 354 in bank

    Select 2

    You need to identify the cause of the performance issues on SalesSQLDb1. Which two dynamic management views should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    • Asys.dm_pdw_nodes_tran_locks
    • Bsys.dm_exec_compute_node_errors
    • Csys.dm_exec_requests
    • Dsys.dm_cdc_errors
    • Esys.dm_pdw_nodes_os_wait_stats
    • Fsys.dm_tran_locks

    Show answer

    Answer: CF

    Explanation

    SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries. Seeing a Count of All Active SQL Server Wait Types. Sometimes we're trying to diagnose a problem and we want to know if we're seeing a large number of wait types occurring. We can do this using sys.dm_exec_requests because the current wait type being experienced is presented. Therefore, if we filter out any background or sleeping tasks, we can get a picture of what the waits are for active requests and we can also see if we have a problem. Here's the query: SELECT COALESCE(wait_type, 'None') AS wait_type, COUNT(*) AS Total FROM sys.dm_exec_requests - WHERE NOT status IN ('Background', 'Sleeping') GROUP BY wait_type - ORDER BY Total DESC; Here is an example of the query output: We see that we have two LCK_M_S wait types. This is the wait type we get when we have requests waiting on obtaining a shared lock. We can then query along with sys.dm_tran_locks to determine what types of locks these active requests were trying to obtain: SELECT L.request_session_id, L.resource_type, L.resource_subtype, L.request_mode, L.request_type FROM sys.dm_tran_locks AS L - JOIN sys.dm_exec_requests AS DER ON L.request_session_id = DER.session_id WHERE DER.wait_type = 'LCK_M_S'; Incorrect: Not A: Azure SQL database does not have sys.dm_pdw_nodes_tran_locks. Not E: Azure SQL database does not have sys.dm_pdw_nodes_os_wait_stats. Reference: https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/

    Source: Adapted from community discussion — Dalamain

    References

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 141. 354 questions available.