HOTSPOT
You are building a database in an Azure Synapse Analytics serverless SQL pool.
You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container.
Records are structured as shown in the following sample.
The records contain two applicants at most.
You need to build a table that includes only the address fields.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
HOTSPOT
You have the following Azure Resource Manager template.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
DRAG DROP
You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1.
DB1 contains a fact table named Table.
You need to identify the extent of the data skew in Table1.
What should you do in Synapse Studio?
A. Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.
B. Connect to the built-in pool and run DBCC CHECKALLOC.
C. Connect to Pool1 and run DBCC CHECKALLOC.
D. Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.
You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information.
You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information.
What should you include in the recommendation?
A. row-level security
B. data masking
C. Always Encrypted
D. column-level security
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while
others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Does this meet the goal?
A. Yes
B. No
You have an Azure SQL database named DB1.
You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled.
During a database audit, you discover that none of the fields contain more than 150 characters.
You need to ensure that you can apply page compression to Table1.
What should you do?
A. Configure the columns as sparse.
B. Change the column type to NVARCHAR(MAX).
C. Change the column type to VARCHAR(MAX).
D. Change the column type to VARCHAR(200).
You have a single availability set that contains two SQL Server on Azure Virtual Machines instances.
The instances were deployed by using an Azure Marketplace SQL Server 2019 Enterprise image that has the latest cumulative updates applied. The instances are configured as the nodes of a failover cluster instance (FCI) named FCI1. You need to ensure that client applications can connect to FCI1. The solution must meet the following requirements:
1.
Provide an availability SLA
2.
Minimize costs. What should you create?
A. an Azure Standard Load Balancer
B. a virtual network name (VNN) resource
C. a Basic Azure Load Balancer
D. a distributed network name (DNN) resource
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 8 vCPUs and 64 GB of memory.
You need to reduce tempdb contention without negatively affecting server performance.
What is the number of secondary data files that you should configure for tempdb?
A. 2
B. 4
C. 8
D. 64
You have an Azure SQL database named SQL1.
You need to implement a disaster recovery solution for SQL1. The solution must minimize the following:
1.
The recovery point objective (RPO)
2.
The recovery time objective (RTO)
3.
Administrative effort
What should you include in the solution?
A. Azure Site Recovery
B. active geo-replication
C. availability groups
D. auto-failover groups
