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

1. You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected.
The secondary server should allow reporting queries to be performed.
If the primary server goes offline, the secondary server should not automatically
take over.
Which Transact-SQL statement should you insert at line 06?
---

A) Option E
B) Option D
C) Option B
D) Option A
E) Option C

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

3. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues.
What should you do?

A) Run the DTEXEC utility with the /Reporting V option.
B) Run the DTEXEC utility with the /Logger option.
C) Execute the catalog.add_data_tap stored procedure with the package execution_id.
D) Execute the catalog.create_execution_dump stored procedure with the package execution_id.

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

5. 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.

Solutions: