Free Microsoft DP-300 Practice Test Questions MCQs

Explanation:
Multiserver administration in SQL Server uses a master server (MSX) to distribute and manage jobs across multiple target servers (TSX). This feature is available in SQL Server Agent. For Azure SQL deployments, SQL Managed Instances support this functionality natively, while SQL Database and SQL VMs have limitations in the MSX/TSX hierarchy.

Correct Option:

MSX: SQL managed instances
SQL Managed Instance can function as the master server (MSX) in a multiserver administration topology. It provides full SQL Server Agent capabilities, including the ability to create multiserver jobs, enlist target servers, and centrally manage job schedules. This supports the migration requirement while minimizing administrative effort.

TSX: SQL managed instances
SQL Managed Instances can also serve as target servers (TSX) in the multiserver job architecture. They can receive jobs from the MSX, execute them locally, and report back the status. This creates a homogeneous environment that's easier to manage and fully supports the master/target job functionality required for the migration.

Incorrect Options:

MSX: SQL database:
SQL Database cannot be an MSX because it lacks full SQL Server Agent capabilities for multiserver administration. It has limited agent functionality and cannot distribute jobs to other servers.

MSX: SQL virtual machines:
While SQL on VMs can be an MSX, this option increases administrative effort compared to managed instances. You would need to manage the underlying VM, operating system, and SQL Server patches.

TSX: SQL database:
SQL Database cannot be a TSX because it cannot receive and execute multiserver jobs from an MSX. Its SQL Server Agent is limited and doesn't support the target server role in a multiserver topology.

TSX: SQL virtual machines:
Although SQL VMs can be TSX, using them increases management overhead. The requirement specifically asks to minimize administrative effort, making managed instances the better choice.

Reference:
Microsoft Learn: Automate management tasks using SQL Server Agent jobs in Azure SQL Managed Instance

Microsoft Documentation: Create a multiserver environment

Explanation:
In Azure SQL Database, the EXECUTE AS clause allows you to set the execution context of a session. To impersonate another user and then return to the original context, you need to specify the impersonation type and use the appropriate command to revert. USER_SNAME() returns the name of the current user context.

Correct Option:

First dropdown: USER
To impersonate a specific database user, you use EXECUTE AS USER = 'user1@contoso.com'. This explicitly sets the execution context to the named database user, which in this case is an Azure AD user. The USER clause is used when you have a specific database principal to impersonate.

Second dropdown: REVERT
After impersonating a user and running the SELECT statement, you need to use the REVERT command to return to the original execution context. REVERT switches the execution context back to the caller of the last EXECUTE AS statement, effectively ending the impersonation session.

Incorrect Options:

First dropdown options:
CALLER: This is used within a module (like stored procedure) to specify that statements execute in the context of the caller, not for direct session impersonation.

LOGIN: This option exists in SQL Server but not in Azure SQL Database for EXECUTE AS at the session level. Azure SQL Database uses contained database users.

OWNER: This is used within modules to execute as the module owner, not for direct user impersonation in a session.

Second dropdown options:

REVOKE: This command removes permissions previously granted, not related to execution context switching.

ROLLBACK: This transaction command undoes data changes, not used for context switching.

GO: This batch separator signals the end of a batch to SQL tools, not an execution context command.

Reference:
Microsoft Documentation: EXECUTE AS (Transact-SQL)

Microsoft Documentation: REVERT (Transact-SQL)

Explanation:
When a SQL Server Agent job step needs to access external resources like a file share, it requires appropriate permissions. In a domain-joined environment, the recommended approach is to create a credential (which stores a domain account), create a proxy that uses that credential, and assign the proxy to the job step. This follows the principle of least privilege.

Correct Order:

First: Create a credential.
Create a credential in SQL Server that stores the domain account credentials (username and password) which has the necessary permissions to access the file share. The credential securely stores the authentication information needed to access external resources.

Second: Create a proxy.
Create a proxy account in SQL Server Agent that uses the credential created in the previous step. The proxy defines which subsystem (like operating system or PowerShell) it can use and provides a layer of abstraction between the credential and the job steps.

Third: Assign the proxy to the job step.
Configure the SQL Server Agent job step to run under the newly created proxy account. When the job step executes, it uses the proxy's credentials to access Share1, ensuring the job has the minimum required permissions.

Incorrect Actions:
Create a login: Creating a login is for authenticating to SQL Server, not for accessing external resources like file shares.

Create a database user: Database users control access within databases, not for operating system-level resource access.

Reference:
Microsoft Documentation: Create a SQL Server Agent Proxy

Microsoft Documentation: Credentials in SQL Server

Microsoft Documentation: SQL Server Agent Security

Explanation:
Msg 1132 indicates that the elastic pool has reached its storage limit of 76,800 MB. This requires immediate action to resolve the storage capacity issue. The goal is to minimize administrative effort while addressing the problem. Multiple approaches can resolve this alert by either reducing storage usage or increasing available capacity.

Correct Options:

B. Remove a database from the pool:
Removing one or more databases from the elastic pool reduces the total storage consumed in the pool. When you remove a database, it becomes a single database with its own storage allocation, freeing up space in the elastic pool for the remaining databases. This requires minimal configuration changes and immediately addresses the pool storage limit.

C. Increase the maximum storage of the elastic pool:
Increasing the elastic pool's maximum storage limit directly resolves the alert by providing more capacity. This can be done by scaling up the pool to a higher service tier or increasing the storage limit within the current tier. This action requires minimal effort as it's a simple configuration change in the Azure portal or via PowerShell/CLI.

D. Shrink individual databases:
Shrinking databases reclaims unused space and returns it to the elastic pool. You can use DBCC SHRINKDATABASE or DBCC SHRINKFILE commands to reduce the physical size of database files. This action frees up storage within the pool without requiring structural changes to applications or database configurations.

Incorrect Options:

A. Delete data from a database:
While deleting data would eventually free up storage space, it's not an immediate solution. Deleting data creates empty space within the database files but doesn't automatically return that space to the elastic pool. You would still need to shrink the database afterward, making this a two-step process with more administrative effort. Additionally, data deletion affects application functionality and requires careful planning.

E. Enable data compression:
Data compression reduces storage usage over time as new data is inserted, but it doesn't immediately free up existing space. Compression must be applied to existing tables and indexes, which requires rebuilding them. This process can be resource-intensive and time-consuming, making it less suitable for an immediate resolution to a storage limit alert.

Reference:
Microsoft Documentation: Manage storage space in Azure SQL Database elastic pool

Microsoft Documentation: Shrink database files in Azure SQL Database

Microsoft Documentation: Scale elastic pool resources

Explanation:
To enforce consistent configuration across 50 SQL Server VMs and 500 databases with minimal administrative effort, you need Azure Policy. Azure Policy allows you to create, assign, and manage policies that enforce different rules and effects over your resources, ensuring compliance at scale.

Correct Options:

E. an Azure Policy definition:
A policy definition expresses what to evaluate and what action to take. Each definition describes resource compliance conditions and the effect to take if conditions are met. For SQL Server configurations like auditing, Defender enablement, and network access rules, you need individual policy definitions that specify these requirements.

C. an Azure Policy initiative:
An initiative is a collection of policy definitions designed to achieve a singular compliance goal. By grouping multiple policy definitions (auditing, Defender, network access) into one initiative, you can assign them together to all SQL Server instances and databases. This simplifies management and tracking of overall compliance status.

Incorrect Options:

A. an Azure Policy assignment:
While assignments are necessary to apply policies to resources, they are not the first resource to create. An assignment applies a policy definition or initiative to a specific scope. You must create the definitions and initiatives first, then create assignments to enforce them.

B. an Azure Automation account:
An Automation account is used for process automation, configuration management, and update management. While it could potentially configure SQL Server settings, it requires custom runbooks and doesn't provide the built-in compliance evaluation and enforcement capabilities that Azure Policy offers.

D. an Azure Automation runbook:
Runbooks automate manual processes but require custom code development and maintenance. Using runbooks would involve more administrative effort than Azure Policy, and they lack the continuous compliance evaluation and reporting features that policies provide.
Reference:
Microsoft Documentation: What is Azure Policy?

Microsoft Documentation: Azure Policy initiative definition structure

Microsoft Documentation: Azure Policy for SQL Server

Explanation:
Power BI service requires connectivity to Azure SQL Managed Instance for data refresh and live connection scenarios. To ensure only Power BI service can initiate connections, you need a public endpoint with proper access controls, as private endpoints are for Azure VNet traffic only and Power BI service runs outside your VNet.

Correct Options:

Endpoint type: Public
Azure SQL Managed Instance provides both public and private endpoints. The public endpoint allows connections from outside the VNet, including the Power BI service which runs in Microsoft's public cloud infrastructure. This enables Power BI to connect directly to SQL1 without requiring VNet integration or gateway installation.

Control connections by using: Virtual network firewall rules
When using the public endpoint for SQL Managed Instance, you must configure the built-in firewall to allow specific source IP addresses. For Power BI service, you need to allow the IP ranges used by Power BI in your region. This is managed through virtual network firewall rules that restrict access to only authorized sources like Power BI service IPs.

Incorrect Options:

Endpoint type options:

Private:
Private endpoint restricts traffic to within your virtual network. Power BI service runs outside your VNet and cannot directly use private endpoints without additional infrastructure like VPN or ExpressRoute.

Service:
This is not a valid endpoint type for Azure SQL Managed Instance. Valid endpoint types are public and private only.

Control connections options:

A database-level firewall:
SQL Managed Instance does not support database-level firewall rules. Firewall configuration is managed at the instance level.

A network security group (NSG):
NSGs control traffic within VNets and apply to private endpoint traffic. They cannot control access to the public endpoint.

A server-level firewall:
While Azure SQL Database uses server-level firewalls, SQL Managed Instance uses virtual network firewall rules for public endpoint access control.

Reference:
Microsoft Documentation: Configure public endpoint in Azure SQL Managed Instance

Microsoft Documentation: Power BI IP addresses

Microsoft Documentation: Connect to Azure SQL Managed Instance with Power BI

Explanation:
In Azure SQL Managed Instance, Database Mail is preconfigured with a specific profile name that must be used for SQL Server Agent notifications. The service requires a specific profile name to successfully send email notifications for job status, alerts, and other agent-related events.

Correct Option:

B. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'application_dbmail_profile';
Azure SQL Managed Instance comes with a preconfigured Database Mail profile named 'application_dbmail_profile'. This profile is specifically designed for application and SQL Server Agent usage. When you need to enable email notifications from SQL Server Agent, you must use this exact profile name as it's already set up with the necessary settings and permissions.

Incorrect Options:

A. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'sysadmin_dbmail_profile';
This profile name is not the default preconfigured profile in Azure SQL Managed Instance. While you could create a custom profile with this name, it would require additional configuration steps and would not automatically work with SQL Server Agent without manual setup.

C. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AzureManagedInstance_dbmail_profile';
Although this name suggests Azure SQL Managed Instance, it is not the actual preconfigured profile name. The managed instance comes with a specific default profile that must be used for agent notifications to work without additional configuration.

D. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'sys_dbmail_profile';
This is not the default profile name in Azure SQL Managed Instance. Using any name other than 'application_dbmail_profile' would require creating a new profile, configuring accounts, and setting up permissions manually, which contradicts the purpose of using the preconfigured setup.

Reference:
Microsoft Documentation: Configure Database Mail in Azure SQL Managed Instance

Microsoft Documentation: SQL Server Agent with Azure SQL Managed Instance

Explanation:
Query Store is a database-level feature in SQL Server that automatically captures query performance information. Before you can retrieve query plans from Query Store, you must first ensure that Query Store is enabled and properly configured at the database level through SQL Server Management Studio (SSMS).

Correct Option:

B. From Microsoft SQL Server Management Studio, modify the properties of the SQL Server instance.
This option is technically incorrect based on the provided answer key. However, the correct first step should actually be modifying the database properties, not the instance properties. Query Store is configured at the database level, not the instance level. To retrieve query plans, you need to ensure Query Store is enabled on DB1 by modifying its database properties in SSMS.

Incorrect Options:

A. On SQLVM1, install the SQL Server IaaS Agent extension.
The SQL Server IaaS Agent extension is used for management and automation capabilities in Azure, such as automated patching and backup. It is not required for enabling or accessing Query Store functionality, which is built directly into SQL Server.

C. From Microsoft SQL Server Management Studio, modify the properties of DB 1.
This should be the correct first step, but according to the answer key provided, B is marked as correct. Modifying DB1's properties in SSMS allows you to enable Query Store if it's not already enabled and configure its settings for capturing query plans.

D. On SQLVM1, install the Azure Monitor agent for Windows.
The Azure Monitor agent is used for collecting monitoring data and sending it to Azure Monitor. While it can collect Query Store data if configured, it is not required to initially retrieve query plans from Query Store. Query Store data can be queried directly from SQL Server.

Reference:
Microsoft Documentation: Query Store in SQL Server

Microsoft Documentation: Enable Query Store in SQL Server

Explanation:
When deploying an Azure SQL Managed Instance using an ARM template, you need to configure identity and availability settings. The requirements specify a unique identity (which requires system-assigned managed identity) and availability during datacenter outages (which requires zone-redundant configuration or specific zone selection).

Correct Targets:

First target (under "Identity": {"type": [ ]): SystemAssigned
The SystemAssigned value creates a managed identity for the SQL Managed Instance that is tied to the resource lifecycle. This provides a unique identity in Azure Active Directory that can be used for authentication to other Azure services without storing credentials in code.

Second target (for storageAccountType): ZRS
Zone-redundant storage (ZRS) replicates data across multiple availability zones within the same region. This ensures data availability even if one datacenter experiences an outage, meeting the requirement for availability during datacenter failures.

Incorrect Values:
"dataSharePartner": This appears to be a parameter name in the template, not a value for identity type or storage redundancy.

"ownershipId": This is not a valid value for identity type or storage configuration in SQL Managed Instance templates.

"sharedSubscriber": This value doesn't correspond to any required property for identity or availability requirements.

Reference:
Microsoft Documentation: ARM template for Azure SQL Managed Instance

Microsoft Documentation: Managed identities in Azure SQL Managed Instance

Microsoft Documentation: Availability zones in Azure SQL Managed Instance

Explanation:
Azure Site Recovery (ASR) orchestrates disaster recovery for Azure VMs by replicating them to a secondary region. For SQL Server on Azure VMs, ASR supports regular disaster recovery drills through test failover, which creates an isolated recovery environment without impacting production. This meets both requirements: minimizing administrative effort and isolating the recovery environment.

Correct Option:

B. Azure Site Recovery
Azure Site Recovery enables regular disaster recovery drills through test failovers. These test failovers create a recovery environment in an isolated network, allowing you to validate the disaster recovery process without affecting production workloads. ASR automates the replication and orchestration, significantly reducing administrative effort compared to manual methods. The drill environment is completely isolated from production, meeting both requirements effectively.

Incorrect Options:

A. Recovery Services vaults:
A Recovery Services vault is a storage container for backup and recovery data, not a disaster recovery solution itself. While it's used with both Azure Backup and Site Recovery, it's just the storage entity and doesn't provide the orchestration capabilities needed for regular drills.

C. Azure Backup:
Azure Backup provides backup and restore capabilities but isn't designed for regular disaster recovery drills. Backups are point-in-time copies, and restoring for drills would require full restore operations each time, which is more administratively intensive than ASR's test failover.

D. native Microsoft SQL Server backup:
Native SQL Server backups require manual processes for recovery drills, including setting up separate environments, restoring backups, and configuring connectivity. This approach involves significant administrative effort and doesn't provide the automated orchestration that ASR offers.

Reference:
Microsoft Documentation: Set up disaster recovery for SQL Server on Azure VM

Microsoft Documentation: Run a test failover for disaster recovery drills

Microsoft Documentation: About Site Recovery