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

1. 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) Aggregate
B) Lookup
C) Slowly Changing Dimension
D) Data Conversion
E) Multicast

2. ----
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) Transaction log
B) CONTINUE_AFTER_ERROR
C) DBO_ONLY
D) NO_CHECKSUM
E) FULL
F) NORECOVERY
G) STANDBY
H) SKIP
I) COPY_ONLY
J) SIMPLE
K) BULK_LOGGED
L) CHECKSUM
M) RESTART
N) Differential

3. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.
An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.
You need to establish the automated validation process by using the least amount of administrative effort.
What should you do?

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

4. You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.
The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.)."
You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?

A) Configure the Subscriber to use the Local System account.
B) Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.
C) Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.
D) Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.

5. ----
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 5 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 your backup will continue if any invalid checksum is encountered.
Which backup option should you use?

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

Solutions: