DP-300 Practice Exam — DP-300: Administering Relational Databases on Microsoft Azure

Option Analysis: A. This option is correct, because the total combined storage size of all individual databases in the pool is a required metric to size the elastic pool to accommodate all data. B. This option is incorrect, because geo-replication support is a feature configuration, not a sizing metric used to determine required compute or storage capacity for the elastic pool. C. This option is correct, because the product of concurrently peaking databases and peak CPU per database calculates the peak aggregate CPU demand the pool must handle, which is critical for proper sizing. D. This option is incorrect, because maximum concurrent sessions is not a primary metric used for sizing Azure SQL Database elastic pool vCore capacity. E. This option is correct, because the product of total databases and average CPU utilization per database calculates the average aggregate CPU demand, which is used alongside peak demand to right-size the pool. Key Concept: Sizing an Azure SQL Database elastic pool using the vCore model requires evaluating combined storage, average aggregate CPU utilization, and peak aggregate CPU utilization across all databases to match workload demands. References: Sizing elastic pools in Azure SQL Database, https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview?view=azuresql#sizing-elastic-pools

" target="_blank" rel="nofollow noopener">https://cdn.zhangxuetu.online/download/mx8OANm14_Onrj8s.png"> Analysis: The correct ordered sequence of actions is: 1. Attach the SSISDB database, 2. Turn on the TRUSTWORTHY property and the CLR property, 3. Open the master key for the SSISDB database, 4. Encrypt a copy of the master key by using the service master key. Since the original master database was lost, we first reattach the existing SSISDB database to the recovered SQL Server instance. Next, we enable the TRUSTWORTHY database property to allow the database to use external resources that SSIS requires, and enable CLR to support CLR-integrated objects used by SSISDB. We then open the existing database master key for SSISDB, and finally re-encrypt the copy of the master key with the new instance's service master key to enable automatic decryption for SSIS operations. Key Concept: When restoring or reattaching SSISDB after losing the SQL Server instance master database, you must follow the correct post-reattachment key configuration steps to restore SSIS functionality. References: Restore the SSIS Catalog Database, Microsoft Learn, https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-ssms?view=sql-server-ver15#restore-the-ssis-catalog-database Recovering SSISDB after master database loss, Microsoft Learn

Option Analysis: A. Page compression is a row-store compression that provides less space reduction than columnstore-based compression methods, so this option is incorrect. B. Row compression provides only moderate space reduction for row-store tables and is far less space-efficient than columnstore archival compression, so this option is incorrect. C. Columnstore compression provides good space reduction for large fact tables, but it offers less space reduction than columnstore archival compression, so this option is incorrect. D. Columnstore archival compression provides the highest possible level of compression for large read-oriented data warehouses like this 6 billion row fact table that is loaded nightly via batch, resulting in the greatest space reduction, so this option is correct. Key Concept: For large read-optimized data warehouse tables, columnstore archival compression provides the maximum possible space reduction in Azure SQL Database compared to row-store and standard columnstore compression options. References: Data Compression, Microsoft Learn, https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16 Columnstore Indexes Guide, Microsoft Learn, https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16

Option Analysis: A. Clustered columnstore indexes are fully supported in Azure SQL Database, so this option is incorrect. B. PolyBase is not supported on Azure SQL Database single database/elastic pool, so this option is correct and the feature must be removed or replaced before migration. C. Change tracking is fully supported in Azure SQL Database, so this option is incorrect. D. Automatic tuning is a native supported feature in Azure SQL Database, so this option is incorrect. Key Concept: When migrating an on-premises SQL Server database to Azure SQL Database, you must identify and remove any on-premises SQL Server features that are not supported in the PaaS Azure SQL Database service. References: Features unsupported by Azure SQL Database, PolyBase connectivity configuration, https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15

Option Analysis: A. Distributed availability groups require additional infrastructure and are not the recommended minimal-downtime approach for this migration scenario, so this option is incorrect. B. Database mirroring is a deprecated high availability feature that does not support native migration to Azure SQL Managed Instance with minimal downtime, so this option is incorrect. C. Always On Availability Groups can be used for migration but require more manual configuration and management compared to the native Azure migration tool, so this option is incorrect. D. Azure Database Migration Service supports near-zero downtime online migrations of SQL Server databases to Azure SQL Database Managed Instance, meeting the requirements of minimal downtime and data loss, so this option is correct. Key Concept: Azure Database Migration Service is the recommended Azure service for performing minimal-downtime migrations of on-premises SQL Server databases to Azure SQL Managed Instance. References: Migrate SQL Server to Azure SQL Managed Instance online, https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-managed-instance Overview: Migrate SQL Server to Azure SQL Managed Instance, https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-overview