Free Practice Questions for Microsoft DP-300 Exam

There are CPU and Data I/O spikes for the PaaS prototype. Business Critical 4-vCore is needed.

Scenario: In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The sol ution must provide automatic failover.

The auto-failover groups feature allows you to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region. It is a declarative abstraction on top of the existing active geo-replication feature, designed to simplify deployment and management of geo-replicated databases at scale. You can initiate failover manually or you can delegate it to the Azure service based on a user-defined policy.

The latter o ption allows you to automatically recover multiple related databases in a secondary region after a catastrophic failure or other unplanned event that results in full or partial loss of the SQL Database or SQL Managed Instance availability in the primary re gion.

The lifecycle management policy lets you:

Delete blobs, blob versions, and blob snapshots at the end of their lifecycles

Box 1: Yes

DBAGroup1 is member of the Contributor role.

The Contributor role grants full access to manage all resources, but does not allow you to assign roles in Azure RBAC, manage assignments in Azure Blueprints, or share image galleries.

Box 2: No

Box 3: Yes

DBAGroup2 is member of the SQL DB Contributor role.

The SQL DB Contributor role lets you manage SQL databases, but not access to them. Also, you can ' t manage their security-related policie s or their parent SQL servers. As a member of this role you can create and manage SQL databases.

Scenario: Contoso requirements for the sales transaction dataset include:

Implement a surrogate key to account for changes to the retail store addresses.

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the

table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The pro duct will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn’t frequently join with data from other tables.

When there are no obvious keys to join.

To configure sq1370O6895 to support the creation of new database users from Azure AD identities, you need to do the following steps:

Set up a Microsoft Entra tenant and associate it with your Azure subscription.  You can use the Microsoft Entra portal or the Azure portal to create and manage your Microsoft Entra users and groups 1 2 .

Configure a Microsoft Entra admin for sq1370O6895.  You can use the Azure portal or the Azure CLI to set a Microsoft Entra user as the ad min for the server 3 4 .  The Microsoft Entra admin can create other database users from Microsoft Entra identities 5 .

Connect to db1 using the Microsoft Entra admin account and run the following Transact-SQL statement to create a new database user from a Microsoft Entra identity:  CREATE USER [Microsoft Entra user name] FROM EXTERNAL PROVIDER; 6  You can replace the Microsoft Entra user name with the name of the user or group that you want to create in the database.

Grant the appropriate permissions to the new database user by adding them to a database role or granting them specific privileges. For example, you can run the following Transact-SQL statement to add the new user to the db_datareader role:  ALTER ROLE db_datareader ADD MEMBER [Microsoft Entra user name];

These are the steps to configure sq1370O6895 to support the creation of new database users from Azure AD identities.

To prevent db1 from reverting to the last known good query plan, you need to disable the automatic plan correction feature for the database.  This feature is enabled by default and allows the Query Store to detect and fix plan performance regressions by forcing the last good plan 1 .  However, if you want to test the performance of different plans without interference from the Query Store, you can turn off this feature by using the ALTER DATABASE SCOPED CONFIGURATION statement 2 .

Here are the steps to disable the automatic plan correction feature for db1:

Connect to db1 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.

Open a new query window and run the following command:  ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF); GO

This command will disable the automatic plan correction feature for db1 and allow the Query Optimizer to choose the best plan based on the current statistics and parameters 3 .

To ve rify that the automatic plan correction feature is disabled for db1, you can query the sys.database_scoped_configurations catalog view. The value of the force_last_good_plan column should be 0 for db1.

These are the steps to disable the automatic plan corr ection feature for db1.

SQL injection attacks are a type of cyberattack that exploit a vulnerability in the application code that interacts with the database.  An attacker can inject malicious SQL statements into the user input, such as a form field or a URL parameter, and execute them on the databas e server, resulting in data theft, corruption, or unauthorized access 1 .

To protect all the databases on sql37006S95 from SQL injection atta cks, you need to follow some best practices for securing your application and database layers. Here are some of the recommended steps:

Use parameterized queries or stored procedures to separate the SQL code from the user input.  This will prevent the user input from being interpreted as part of the SQL statement and avoid SQL injection 2 3 .

Validate and sanitize the user input before passing it to the database.  This will ensure that the input conforms to the expected format and type, and remove any potentia lly harmful characters or keywords 4 .

Implement least privilege access for the database users and roles.  This will limit the permissions and actions that the application can perform on the database, and reduce the impact of a successful SQL injection attack 5 .

Enable Advanced Threat Protection for Azure SQL Database. This is a feature that detects and alerts you of anomalous activities and potential threats on yo ur database, such as SQL injection, brute force attacks, or unusual access patterns. You can configure the alert settings and notifications using the Azure portal or PowerShell.

These are some of the steps to protect all the databases on sql37006S95 from S QL injection attacks.