HOTSPOT (Drag and Drop is not supported)You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.You need to ensure that User1 can perform the following tasks on DB1:Create jobs.View all jobs.Modify, delete, and disable the jobs the user created.The solution must use the principle of least privilege.Which built-in database role should you assign to User1, and where is the role defined? To answer, select the appropriate options in the answer area.Hot Area:
Answer(s): A
Box 1: msdbSQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:SQLAgentUserRoleSQLAgentReaderRoleSQLAgentOperatorRoleBox 2: SQLAgentReaderRoleSQLAgentReaderRole PermissionsSQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.Incorrect:* SQLAgentUserRole Permissions (too few)SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.* SQLAgentOperatorRole Permissions (too much)SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server.
https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.You need to collect performance metrics for SQLMI1 and stream the metrics to Workspace1.What should you do first?
Answer(s): D
Instances in Azure SQL Managed InstanceYou can set up a managed instance resource to collect the following diagnostic telemetry:ResourceUsageStats contains vCores count, average CPU percentage, IO requests, bytes read/written, reserved storage space, and used storage space.To configure streaming of diagnostic telemetry for managed instance and instance databases, you will need to separately configure each:Enable streaming of diagnostic telemetry for managed instance Enable streaming of diagnostic telemetry for each instance database The managed instance container has its own telemetry separate from each instance database's telemetry.To enable streaming of diagnostic telemetry for a managed instance resource, follow these steps:1. Go to the managed instance resource in Azure portal.2. Select Diagnostics settings.3. Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.4. Enter a setting name for your own reference.5. Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.6. For log analytics, select Configure and create a new workspace by selecting +Create New Workspace, or use an existing workspace.7. Select the check box for instance diagnostic telemetry: ResourceUsageStats.8. Select Save.
https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming- export-configure
HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database named DB1 in the General Purpose service tier.You need to monitor DB1 by using SQL Insights.What should you include in the solution? To answer, select the appropriate options in the answer area.Hot Area:
Box 1: The Azure Monitor agentSQL Insights performs all monitoring remotely. Monitoring agents on dedicated virtual machines connect to your SQL resources and remotely gather data. The gathered data is stored in Azure Monitor Logs to enable easy aggregation, filtering, and trend analysis. You can view the collected data from the SQL Insights workbook template, or you can delve directly into the data by using log queries.The following diagram details the steps taken by information from the database engine and Azure resource logs, and how they can be surfaced.Box 2: A Log Analytics workspaceLog Analytics workspacesFor the Log Analytics workspaces, you're charged based on the pricing published on the Azure Monitor pricing page. The Log Analytics workspaces that SQL Insights uses will incur costs for data ingestion, data retention, and (optionally) data export.
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview
You have an Azure SQL database named DB1 in the General Purpose service tier.The performance metrics for DB1 are shown in the following exhibit.You need to reduce the Log IO percentage. The solution must minimize costs.What should you do?
High Log IO is usually seen when you hit the IOPS/throughput limits for the respective pricing tier.* Data/Log IOPS (approximate)General Purpose service tier: 500 - 7500 per fileIncrease file size to get more IOPSBusiness Critical:16 K - 320 K (4000 IOPS/vCore)Add more vCores to get better IO performance.
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
You have an Azure SQL database named DB1 that contains a nonclustered index named index1.End users report slow queries when they use index1.You need to identify the operations that are being performed on the index.Which dynamic management view should you use?
Answer(s): C
Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-300 content, but please register or login to continue.