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 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to
BillingUsers.
Which Transact-SQL statement should you use?

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

2. You administer two Microsoft SQL Server 2012 servers. Each server resides in a different,
untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?

A) Use SQL Authenticated Logins.
B) Configure firewalls to allow Windows Logins.
C) Use Kerberos authentication.
D) Use certificates.

3. 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.)

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.
To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.
The methodology has the following requirements:
Centralized logging in SQL Server
Simple deployment
Availability of log information through reports orT-SQL
Automatic purge of older log entries
Configurable log details
-----
You need to configure a logging methodology that meets the requirements while minimizing
the amount of deployment and development effort.
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) Run the package by using the dtexecui.exe utility and the SQL Log provider.
C) create an OnError event handler.
D) Run the package by using the dtexec /dumperror /conn command.
E) Use the gacutil command.
F) Run the package by using the dtexec /rep /conn command.
G) create a reusable custom logging component.
H) use the dtutil /copy command.
I) Deploy the package by using an msi file.
J) Add a data tap on the output of a component in the package data flow.
K) use the Project Deployment Wizard.

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 time mapping table.
B) Merge the fact tables.
C) Join the two fact tables.
D) Create a time dimension that can join to both fact tables at their respective granularity.

Solutions: