High value 70-767 Dumps 2021

NEW QUESTION 1
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.
You need to pause the compute resources when the instance is not being used. Solution: You use SQL Server Management Studio (SSMS).
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: To pause a SQL Data Warehouse database, use any of these individual methods. Pause compute with Azure portal
Pause compute with PowerShell Pause compute with REST APIs
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-overview

NEW QUESTION 2
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
You remove the Integration Services job.
You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.
Which stored procedure should you execute?

• A. catalog.deploy_project
• B. catalog.restore_project
• C. catalog.stop.operation
• D. sys.sp.cdc.addjob
• E. sys.sp.cdc.changejob
• F. sys.sp_cdc_disable_db
• G. sys.sp_cdc_enable_db
• H. sys.sp_cdc.stopJob

Answer: F

NEW QUESTION 3
You have the Microsoft SQL Server Integration Services (SSIS) package shown in the Control flow exhibit. (Click the Exhibit button.)

The package iterates over 100 files in a local folder. For each iteration, the package increments a variable named loop as shown in the Expression task exhibit. (Click the Exhibit button) and then imports a file. The initial value of the variable loop is 0.

You suspect that there may be an issue with the variable value during the loop. You define a breakpoint on the Expression task as shown in the BreakPoint exhibit. (Click the Exhibit button.)

You need to check the value of the loop variable value.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Break condition: When the task or container receives the OnPreExecute event.
Called when a task is about to execute. This event is raised by a task or a container immediately before it runs. The loop variable does not reset.
With the debugger, you can break, or suspend, execution of your program to examine your code, evaluate and edit variables in your program, etc.

NEW QUESTION 4
You plan to deploy several Microsoft SQL Server Integration Services (SSIS) packages to a highly available SQL Server instance. The instance is configured to use an AlwaysOn availability group that has two replicas.
You need to identify which deployment method must be used to ensure that the packages are always accessible from all the nodes in the availability group.
Which deployment method should you use for the packages?

• A. Deploy to the msdb database on the secondary replica.
• B. Deploy to the msdb database on the primary replica.
• C. Deploy to a file on the hard drive of the primary replica.
• D. Deploy to a shared folder on a file server.

Answer: A

Explanation: Before you can configure SSIS to enable support of AlwaysOn on the new added secondary Replicas, you must connect to all new added secondary replicas.

Note: To use SSIS with AlwaysOn, you’ll need to add the SSIS Catalog (SSISDB) into an Availability Group. You’ll need to do the following steps:
Make sure you meet the prerequisites for using AlwaysOn
Connect to every node and create the SSISDB catalog. We need to create the catalog even on secondary nodes to create the other server-level objects (cleanup jobs, keys, accounts etc) that are used by SSIS.
Delete the SSISDB databases on secondary nodes.
Create an availability group, specifying SSISDB as the user database
Specify secondary replicas.
References: https://chrislumnah.com/2021/05/09/enabling-alwayson-for-ssisdb/

NEW QUESTION 5
You are testing a Microsoft SQL Server Integration Services (SSIS) package. The package includes the Control Flow task shown in the Control Flow exhibit (Click the Exhibit button)

and the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)

You declare a variable named seed as shown in the Variables exhibit. (Click the Exhibit button.).

The variable is changed by the Script task during execution.
You need to be able to interrogate the value of the seed variable after the Script task completes execution. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation: No Yes No Yes

NEW QUESTION 6
You need to recommend a storage solution for a data warehouse that minimizes load times. The solution must provide availability if a hard disk fails.
Which RAID configuration should you recommend for each type of database file? To answer, drag the appropriate RAID configurations to the correct database file types. Each RAID configuration may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: RAID 5
RAID 5 is the similar to that of RAID 0 provided that the number of disks is the same. However, due to the fact that it is useless to read the parity data, the read speed is just (N-1) times faster but not N times as in RAID 0.
Box 2: RAID 10
Always place log files on RAID 1+0 (or RAID 1) disks. This provides better protection from hardware failure, and better write performance.
Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

NEW QUESTION 7
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 plan to deploy a Microsoft SQL server that will host a data warehouse named DB1. The server will contain four SATA drives configured as a RAID 10 array.
You need to minimize write contention on the transaction log when data is being loaded to the database. Solution: You configure the server to automatically delete the transaction logs nightly.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: You should place the log file on a separate drive. References:
https://www.red-gate.com/simple-talk/sql/database-administration/optimizing-transaction-log-throughput/ https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-

NEW QUESTION 8
You are developing a Microsoft SQL Server Integration Services (SSIS) package. You enable the SSIS log provider for the Windows event log. You configure the package to use the ScriptTaskLogEntry event. You create a custom Script task.
You need to ensure that when the script completes, it writes the execution status to the event log on the server that hosts SSIS.
Which code segment should you add to the Script task?

• A. Dts.TaskResult = (int)ScriptResults.Failure
• B. Dts.Events.FireWarning (0, "SSIS", "Script executed with return result " Dts.TaskResult, String.Empty, 0)
• C. System.Diagnostics.EventLog.writeEntryC'SSIS", "Script executed with return result " + Dts.TaskResult, System.Diagnostics.EventLogEntryType.Information)
• D. Dts.TaskResult = (int)ScriptResults.Success

Answer: D

NEW QUESTION 9
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 on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration Services (SSIS) package.
You observe that the package no longer completes within the allotted time. You need to determine which tasks are taking a long time to complete.
Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server instance.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 10
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

Each day, you publish a Microsoft Excel workbook that contains a list of product names and current prices to an external website. Suppliers update pricing information in the workbook. Each supplier saves the workbook with a unique name.
Each night, the Products table is deleted and refreshed from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. All files must be loaded in sequence.
You need to add a data flow in an SSIS package to perform the Excel files import in the data warehouse. What should you use?

• A. Lookup transformation
• B. Merge transformation
• C. Merge Join transformation
• D. MERGE statement
• E. Union All transformation
• F. Balanced Data Distributor transformation
• G. Sequential container
• H. Foreach Loop container

Answer: A

Explanation: If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.

References:
https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-data-from-excel-to-sql

NEW QUESTION 11
You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click the Exhibit button.)

The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the OLE DB Source Editor exhibit (Click the Exhibit Button.) and the Advanced Editor for Prices exhibit (Click the Exhibit button.)


You join the Products and Prices tables by using the ReferenceNr column. You need to resolve the error with the package.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

Answer:

Explanation: There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:
The Merge Join Transformation requires sorted data for its inputs.
If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-

NEW QUESTION 12
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are developing a Microsoft SQL Server Integration Services (SSIS) projects. The project consists of several packages that load data warehouse tables.
You need to extend the control flow design for each package to use the following control flow while minimizing development efforts and maintenance:

Solution: You add the control flow to an ASP.NET assembly. You add a script task that references this assembly to each data warehouse load package.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: A package consists of a control flow and, optionally, one or more data flows. You create the control flow in a package by using the Control Flow tab in SSIS Designer.
References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/control-flow

NEW QUESTION 13
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data captule tables.
You remove the Integration Services job.
You need to stop tracking changes to the database temporarily. The solution must ensure that tracking changes can be restored quickly in a few weeks.
Which stored procedure should you execute?

• A. catalog.deploy_project
• B. catalog.restore_project
• C. catalog.stop.operation
• D. sys.sp_cdc.addJob
• E. sys.sp.cdc.changejob
• F. sys.sp_cdc_disable_db
• G. sys.sp_cdc_enable_db
• H. sys.sp_cdc.stopJob

Answer: C

Explanation: catalog.stop_operation stops a validation or instance of execution in the Integration Services catalog.
References:
https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-stop-operation-ssisd

NEW QUESTION 14
You need to load data from a CSV file to a table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.
By including the Merge transformation in a data flow, you can merge data from two data sources, such as tables and files.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-transformation?view

NEW QUESTION 15
You are the administrator of a database that hosts tables for a data warehouse.
The table named Fact1 has data from the start of calendar year 2011 through the end of 2021. The table contains at least 20 million rows of data for each year. You create the table by running the following Transact-SQL statement:
CREATE PARTITION FUNCTION PartitionFunc_Faet1(SMALLINT) AS RANGE LEFT FOR VALUES(2012,2013,2014,2015)
You need to modify the partition function so that rows for each calendar year are in a separate partition. You must also move all data prior to 2014 to another table named Fact1.old.

Answer:

Explanation: SET 2102
Merge Split 2021

NEW QUESTION 16
You are the administrator of a Microsoft SQL Server Master Data Services (MDS) model. The model was developed to provide consistent and validated snapshots of master data to the ETL processes by using
subscription views. A new model version has been created.
You need to ensure that the ETL processes retrieve the latest snapshot of master data. What should you do?

• A. Add a version flag to the new version, and create new subscription views that use this version flag.
• B. Create new subscription views for the new version.
• C. Update the subscription views to use the new version.
• D. Update the subscription views to use the last committed version.

Answer: A

Explanation: When a version is ready for users or for a subscribing system, you can set a flag to identify the version. You can move this flag from version to version as needed. Flags help users and subscribing systems identify which version of a model to use.
References: https://docs.microsoft.com/en-us/sql/master-data-services/versions-master-data-services

NEW QUESTION 17
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
A new version of that integration Services package is released that introduces several errors in the loading process.
You need to roll back the Integration Services package to the previous version. Which stored procedure should you execute?

• A. catalog.deploy_project
• B. catalog.restore_project
• C. catalog.stop.operation
• D. sys.sp_cdc.addJob
• E. sys.sp.cdc.changejob

Answer: B

Explanation: catalog.restore_project restores a project in the Integration Services catalog to a previous version. References:
https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-restore-project-ssisd

NEW QUESTION 18
You deploy a Microsoft Server database that contains a staging table named EmailAddress_Import. Each night, a bulk process will import customer information from an external database, cleanse the data, and then insert it into the EmailAddress table. Both tables contain a column named EmailAddressValue that stores the email address.
You need to implement the logic to meet the following requirements:
Email addresses that are present in the EmailAddress_Import table but not in the EmailAddress table must be inserted into the EmailAddress table.
Email addresses that are not in the EmailAddress_Import but are present in the EmailAddress table must be deleted from the EmailAddress table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation: Box 1: EmailAddress
The EmailAddress table is the target. Box 2: EmailAddress_import
The EmailAddress_import table is the source. Box 3: NOT MATCHED BY TARGET
Box 4: NOT MATCHED BY SOURCE
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql