Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Sample Questions:

1. DRAG DROP
You administer three Microsoft SQL Server 2012 servers named ServerA, Servers, and ServerC.
ServerA is the acting principal and ServerB is the mirror.
You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB. You need to achieve this goal without delaying synchronization.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

2. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?

A) Matching Policy
B) Knowledge Discovery
C) Matching
D) Cleansing

3. You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was last processed.
Which data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)

A) Slowly Changing Dimension
B) Aggregate
C) Lookup
D) Data Conversion
E) Multicast

4. 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 deploying a new SQL Server Integration Services (SSIS) package to several servers.
The package must meet the following requirements:
- INET Common Language Runtime (CLR) integration in SQL Server must not be enabled. - The Connection Managers used in the package must be configurable without editing the package. - The deployment procedure must be automated as much as possible.
You need to set up a deployment strategy that meets the requirements.
What should you do?

A) Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
B) create an OnError event handler.
C) Run the package by using the dtexec /rep /conn command.
D) Add a data tap on the output of a component in the package data flow.
E) create a reusable custom logging component.
F) Use the gacutil command.
G) Deploy the package by using an msi file.
H) Run the package by using the dtexec /dumperror /conn command.
I) use the dtutll /copy command.
J) use the Project Deployment Wizard.
K) Run the package by using the dtexecui.exe utility and the SQL Log provider.

5. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?

A) Create a view on the sales table.
B) Join the two fact tables.
C) Create a time dimension that can join to both fact tables at their respective granularity.
D) Create a surrogate key for the time dimension.

Solutions: