View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 361

Which Azure SQL feature allows read-only queries to be routed to a readable secondary replica?

1. Read scale-out
2. Transparent Data Encryption
3. Dynamic Data Masking
4. Row-Level Security

Correct Answer: 1

Explanation

Read scale-out allows supported Azure SQL workloads to send read-only queries to secondary replicas instead of placing all read workload on the primary replica. This can help improve performance for applications with significant read traffic. The application or connection must use the appropriate read-only routing configuration. Read scale-out is different from disaster recovery because its main purpose is to provide additional read capacity rather than simply serving as a backup for the primary database.

Question 362

Which connection string setting indicates that an application intends to perform read-only operations?

1. ApplicationIntent=ReadOnly
2. ApplicationMode=Backup
3. ReadOnly=TrueOnly
4. DatabaseIntent=Secondary

Correct Answer: 1

Explanation

ApplicationIntent=ReadOnly tells the SQL client that the application intends to perform read-only operations. In supported Azure SQL configurations, this setting can help route read-only workloads to readable secondary replicas. It is commonly used with read scale-out or availability configurations where secondary replicas are available for read workloads. The setting does not make a user automatically read-only; database permissions and the actual application behavior still determine what operations are allowed.

Question 363

Which Azure SQL feature provides a read-only endpoint for accessing a readable secondary replica in supported configurations?

1. Read-only routing
2. Azure Firewall
3. Query Store
4. SQL Server Agent

Correct Answer: 2

Explanation

Read-only routing allows connections that indicate read-only intent to be directed to an appropriate readable secondary replica when the Azure SQL configuration supports this capability. This can reduce read workload on the primary database. Applications typically use the appropriate connection string settings to indicate their intent. Administrators should verify that the selected service and configuration support readable replicas before designing an application around read-only routing.

Question 364

Which Azure SQL Database capability allows a database to continue serving read workloads from a secondary replica while the primary database is handling write workloads?

1. Read scale-out
2. Database shrinking
3. Transparent Data Encryption
4. Dynamic Data Masking

Correct Answer: 1

Explanation

Read scale-out provides additional capacity for read-only workloads by allowing queries to use secondary replicas. The primary replica remains responsible for write operations while supported read workloads can be distributed to readable replicas. This can be helpful for applications such as reporting systems, dashboards, and analytical queries that generate significant read traffic. Administrators should evaluate workload patterns before enabling or depending on read scale-out because not every query or application scenario benefits from it.

Question 365

Which SQL Server database file normally stores transaction log information?

1. .mdf
2. .ndf
3. .ldf
4. .bak

Correct Answer: 3

Explanation

The .ldf file is the standard SQL Server transaction log file. It records transaction activity needed for recovery and other database operations. The primary data file normally uses the .mdf extension, while additional data files commonly use .ndf. Backup files commonly use .bak. Administrators should monitor transaction log growth because an improperly managed log can consume available storage and affect database operations.

Question 366

Which SQL Server file extension is normally associated with the primary database data file?

1. .ldf
2. .mdf
3. .trn
4. .bak

Correct Answer: 2

Explanation

The .mdf extension is commonly used for the primary data file of a SQL Server database. It contains database data and database objects such as tables and indexes. Additional data files commonly use the .ndf extension, while transaction log files use .ldf. Backup files may use extensions such as .bak or .trn. Administrators should understand these file types when managing storage, configuring file growth, troubleshooting capacity problems, or planning database migrations.

Question 367

Which SQL Server file extension is commonly used for an additional secondary data file?

1. .ndf
2. .ldf
3. .mdf
4. .bak

Correct Answer: 1

Explanation

The .ndf extension is commonly used for secondary or additional data files in SQL Server databases. A database can have multiple data files when additional storage or specific storage layouts are required. The primary data file normally uses .mdf, while transaction log files use .ldf. Administrators can configure filegroups and multiple data files to organize storage, but simply adding files does not automatically improve performance unless the storage and workload design benefit from the configuration.

Question 368

Which SQL Server command can be used to view database file information?

1. DBCC SHRINKDATABASE
2. FILEPROPERTY
3. sp_helpfile
4. BACKUP DATABASE

Correct Answer: 3

Explanation

sp_helpfile displays information about the files associated with the current database, including file names, sizes, maximum sizes, and growth settings. It can help administrators inspect how database storage is configured. This is useful when investigating storage consumption or planning changes to database files. DBCC SHRINKDATABASE is instead related to shrinking database files, which should not be performed routinely because unnecessary shrinking can lead to fragmentation and repeated file growth.

Question 369

Which command can be used to view database filegroups and their associated files?

1. sp_helpfilegroup
2. sp_who2
3. sp_helptext
4. DBCC CHECKDB

Correct Answer: 1

Explanation

sp_helpfilegroup provides information about the filegroups defined in the current database. Filegroups are logical containers used to organize database data files. They can help administrators manage storage layouts and, in some designs, control where database objects are stored. sp_who2 is used for session and process information, while sp_helptext displays the definition of programmable objects. Understanding filegroups is useful when managing large databases and planning storage configurations.

Question 370

Which action is generally recommended when a SQL Server transaction log repeatedly grows because transactions are not being backed up?

1. Use appropriate transaction log backups
2. Disable all database indexes
3. Delete the .ldf file
4. Shrink the database every minute

Correct Answer: 1

Explanation

In the Full or Bulk-logged recovery model, regular transaction log backups are important for managing log reuse and supporting point-in-time recovery. If log backups are not being performed, the transaction log can continue growing as log space cannot be reused for the expected recovery process. Administrators should identify the actual reason for log growth before taking action. Deleting the log file is not a valid solution, and frequent shrinking is generally discouraged.

Question 371

Which database recovery model generally does not support point-in-time recovery through transaction log backups?

1. Full
2. Bulk-logged
3. Simple
4. Enterprise

Correct Answer: 3

Explanation

The Simple recovery model automatically reuses transaction log space after transactions are no longer needed for recovery, reducing the need for transaction log backups. Because transaction log backups are not supported in the Simple recovery model, it does not provide traditional point-in-time recovery through a sequence of log backups. Full recovery is used when point-in-time recovery is required. Administrators should select a recovery model based on recovery objectives and workload requirements.

Question 372

Which SQL Server recovery model is typically selected when an organization requires point-in-time recovery using transaction log backups?

1. Simple
2. Full
3. Read-only
4. Offline

Correct Answer: 2

Explanation

The Full recovery model supports transaction log backups and allows administrators to restore a database to a specific point in time when an appropriate backup chain is available. This makes it suitable for systems with strict recovery requirements. Administrators must perform regular log backups to prevent unnecessary log growth and maintain the recovery chain. Full recovery alone does not guarantee recovery; successful backups and proper restore procedures must also be maintained and tested.

Question 373

What is the main purpose of a database compatibility level in SQL Server and Azure SQL?

1. To control database backup encryption
2. To influence database engine behavior and feature compatibility
3. To create database users
4. To allocate additional storage automatically

Correct Answer: 2

Explanation

Database compatibility level controls certain database engine behaviors and allows applications to run with behavior associated with a selected SQL Server version level. It can be useful when migrating databases to a newer SQL platform because administrators can separate the database engine upgrade from some application behavior changes. Compatibility level does not change the physical database version itself. Administrators should test applications and queries before changing compatibility level in production.

Question 374

Which command can be used to change the compatibility level of a SQL Server database?

1. ALTER DATABASE
2. ALTER LOGIN
3. ALTER INDEX
4. ALTER SERVER

Correct Answer: 1

Explanation

The ALTER DATABASE statement can be used to change various database-level settings, including compatibility level. For example, an administrator can specify a supported compatibility level using an ALTER DATABASE command. Changing compatibility level may affect query optimization and certain database behaviors, so testing is recommended before applying the change to production. Administrators should verify that the selected compatibility level is supported by the SQL platform and appropriate for the application.

Question 375

Which database setting can help control whether Query Store collects query information?

1. Query Store state
2. Filegroup ownership
3. Login policy
4. Firewall mode

Correct Answer: 1

Explanation

Query Store has configuration settings that control whether query, plan, and runtime information is collected. Administrators can enable or disable Query Store and configure settings such as data retention and capture behavior. Query Store is useful for tracking query performance over time and identifying plan changes. Proper configuration helps balance the amount of performance history retained with storage and workload considerations.

Question 376

What is one major benefit of Query Store for database administrators?

1. It permanently encrypts all database files
2. It tracks query performance and execution plans over time
3. It replaces database backups
4. It automatically creates every required database user

Correct Answer: 2

Explanation

Query Store captures query-related information, including execution plans and runtime performance data, allowing administrators to investigate performance changes over time. It can help identify queries whose performance has degraded after a plan change and can support plan forcing when appropriate. Query Store does not replace backups or encryption features. Its main purpose is performance monitoring and troubleshooting at the query level.

Question 377

Which feature can automatically correct certain query performance problems by forcing a previously successful execution plan?

1. Automatic plan correction
2. Dynamic Data Masking
3. Azure Firewall
4. Transparent Data Encryption

Correct Answer: 1

Explanation

Automatic plan correction can detect certain plan regressions and force a previously known good execution plan when the feature and workload support it. This capability is associated with Azure SQL automatic tuning and Query Store information. It can reduce the need for administrators to manually investigate every plan regression. However, administrators should monitor automatic actions and review recommendations because workload changes may make a previously successful plan less appropriate later.

Question 378

Which tool is commonly used to migrate SQL Server schema and database objects as a deployment package?

1. DACPAC
2. JPEG
3. TXT
4. CSV

Correct Answer: 1

Explanation

A DACPAC is a deployment package that represents the schema of a SQL database. It can be used with tools such as SqlPackage to deploy database schema changes to supported SQL platforms. DACPACs are useful for database development and deployment workflows because they help package database structure separately from application code. A DACPAC should not be confused with a BACPAC, which can include both schema and data for database export and import scenarios.

Question 379

Which tool can be used to publish a DACPAC to a SQL database?

1. SqlPackage
2. Azure Monitor
3. Kusto Explorer
4. Azure Advisor

Correct Answer: 1

Explanation

SqlPackage is a command-line utility that can perform various database deployment and migration operations, including publishing a DACPAC to a target database. It is useful for automated deployment pipelines and database DevOps workflows. Administrators can use SqlPackage to generate scripts, deploy schema changes, and perform other supported database operations. Before deploying changes to production, administrators should validate the package and test its effects on the target environment.

Question 380

Which Microsoft tool can assess SQL Server databases and identify potential compatibility or migration issues before moving them to Azure?

1. Data Migration Assistant
2. Azure Calculator
3. Azure Cost Management
4. Azure Storage Explorer

Correct Answer: 1

Explanation

Data Migration Assistant (DMA) can assess SQL Server databases for compatibility issues and provide recommendations before migration to Azure or a newer SQL Server version. It can identify features or behaviors that may require attention during migration. This assessment helps administrators reduce unexpected problems after moving workloads. DMA is different from Azure Cost Management, which focuses on spending, and Azure Storage Explorer, which is primarily used to manage storage resources.