Practice Questions DP 300

______encrypts all the data within a target database at the page level. The data is encrypted as the data is written to the data page on disk and decrypted when the data page is read into memory. The end result is that all data pages on disk are encrypted. A database file is not readable by someone who is not authorized. Database backups will also be encrypted.

• Encryption for data at rest

• Encryption of data in the cloud

• Transparent Data Encryption

• ColumnStore encryption

You are designing a streaming data solution that will ingest variable volumes of data. You need to ensure that you can change the partition count after creation. Which service should you use to ingest the data?

• Azure Events Hub Standard

• Azure Stream Analytics

• Azure data factory

• Azure Event Hubs Dedicated

You have an Azure Synapse Analytics Apache Spark pool named Pool1. You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file. You need to load the files into the tables. The solution must maintain the source data types. What should you do?

• Load the data by using PySpark

• Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.

• Use a Get Metadata activity in Azure Data Factory.

• Use a Conditional Split transformation in an Azure Synapse data flow.

Scenario: The Dufours Precision Manufacturing Corporation was the business inherited by Countess Stephanie de la Spiroza from her late husband.

The Countess has come to your consulting firm to advise on their Azure migration and their IT team is considering SQL Server PaaS. They want to be able to restore an on-premises database backup to Azure.

Which of the following should you advise to the Countess to utilize?

• They can use both Azure SQL database and managed instance

• Azure SQL database

• Azure SQL Managed Instance

• Azure Reserved VM Instances

You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?

• HASH

• REPLICATE

• ROUND_ROBIN

Answer explanation

A replicated table has a full copy of the table available on every Compute node. Queries run fast on replicated tables since joins on replicated tables don't require data movement. Replication requires extra storage, though, and isn't practical for large tables.

You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1. You plan to migrate DB1 to an Azure SQL Database managed instance. What should you use to minimize downtime and data loss during the migration?

• Distributed availability groups

• database mirroring

• Always On Availability Group

• Azure Database Migration Service

You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1. You plan to create a database named DB1 in Pool1. You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool. Which format should you use for the tables in DB1?

• JSON

• CSV

• Parquet

• C#

Which DMV should you use to find index utilization?

• sys.dm_exec_query_plan_stats

• sys.dm_db_missing_index_details

• sys.dm_db_index_usage_stats

You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements:

✑ Send the output to an Azure Synapse.

✑ Identify spikes and dips in time series data.

✑ Minimize development and configuration effort. Which should you include in the solution?

• Azure Stream Analytics

• Azure Databricks

• Azure SQL Database

______ is a concept where a query is built programmatically and allows T-SQL statements to be generated within a stored procedure or a query itself. See the following code as an example.

EXEC sp_executesql N'SELECT * FROM customer';

• SQL Scripting

• Dynamic SQL

• SQL programming

• Artificial Intelligence