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

1. ----
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has S GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the backup size is as small as possible.
Which backup should you perform every two hours?

A) FULL
B) NORECOVERY
C) SKIP
D) SIMPLE
E) STANDBY
F) BULK_LOGGED
G) Differential
H) Transaction log
I) RESTART
J) COPY_ONLY
K) DBO_ONLY
L) CHECKSUM
M) CONTINUE_AFTER_ERROR
N) NO_CHECKSUM

2. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to
FALSE.
You need to add and configure a container to enable this design.
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.)

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) Lookup
C) Aggregate
D) Multicast
E) Data Conversion

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

5. You are developing a SQL Server Integration Services (SSIS) package to load data into a
Windows Azure SQL Database database. Thepackage consists of several data flow tasks.
The package has the following auditing requirements:
- If a data flow task fails, a Transact-SQL (T-SQL) script must be executed. - The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?

A) Store the System::ServerExecutionID variable in the custom log table.
B) Use an event handler for OnError for the package.
C) View the job history for the SQL Server Agent job.
D) Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
E) View the All Messages subsection of the All Executions report for the package.
F) Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
G) Deploy the project by using dtutil.exe with the /COPY DTS option.
H) Use an event handler for OnError for each data flow task.
I) Store the System::SourceID variable in the custom log table.
J) Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K) Deploy the project by using dtutil.exe with the /COPY SQL option.
L) Store the System::ExecutionInstanceGUID variable in the custom log table.
M) Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
N) Deploy the .ispac file by using the Integration Services Deployment Wizard.
O) Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.*tart_execution stored procedures.
P) Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
Q) Use an event handler for OnTaskFailed for the package.
R) Enable the SSIS log provider for SQL Server for OnError in the package control flow.

Solutions: