DP-300 Question #410: Real Exam Question with Answer & Explanation

Question

SIMULATION You need to capture the following information for all the databases on sq12345678: - Queries to the databases - Users that executed the queries The captured information must be stored in sa12345678. You may need to use SQL Server Management Studio and the Azure portal. Answer: Extended Events is a lightweight performance monitoring system aimed at collecting data for monitoring and troubleshooting SQL Server problems. To capture SQL Server queries and the users who executed them using Extended Events, you can configure an event session with the sql_statement_completed event and the sql_server_login event, as well as add actions to collect relevant information. Step 1: Open SQL Server Management Studio (SSMS) Step 2: .In Object Explorer locate the SQL Server named sq12345678. Step 3: Create a New Event Session: In SQL Server Management Studio, navigate to Management > Extended Events > Sessions, right-click, and select "New Session". Step 4: Define Events: sql_statement_completed: This event captures information about completed SQL statements. It will provide the SQL query text and other details. sql_server_login: This event captures login information, including the username and IP address. Step 5: Add Actions: * sql_statement_completed: Add actions like collect_system_time, collect_statement_text, collect_database_id, collect_principal_name, and collect_login_name to gather specific data. Add an action to capture the client application name (e.g., collect_application_name). * sql_server_login: Add actions like collect_principal_name, collect_login_name, collect_session_id, and collect_client_hostname to capture login details. Step 6: Select the Data Storage page, Choose Targets: event_file: Store captured events in a file (blob) in Azure Storage for persistent storage and analysis using SSMS XEvent Profiler. [Select sa12345678 by using the browse button] Step 7: Configure and Start: Set the desired filter conditions (e.g., duration of queries) [skip] and start the event session. Reference: https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended- events-in-sql-server server/

Options

• taskCapture the following information for all the databases on sq12345678: - Queries to the databases - Users that executed the queries. The captured information must be stored in sa12345678.
• prerequisitesSQL Server Management Studio and Azure portal access