Microsoft 70-458 Exam Syllabus Topics:

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

Question #1
DRAG DROP
You are building a fact table In a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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.)

Question #2
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 a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.
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 Subscriber to use the Local System account.
D. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.

Question #3
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package.
The package contains an ActiveX Data Objects (ADO) source variable that holds a result set that was returned by a stored procedure execution.
You need to add and configure a container that will execute several tasks for each row in the ADO object source variable.
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.)

Question #4
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?

A. EXEC 3p_droprolemember 'Sales', 'UserA'
B. REVOKE SELECT ON Schema::Customers FROM UserA
C. REVOKE SELECT ON Schema: Customers FROM Soles
D. DENY SELECT ON Object::Regions FROM UserA
E. DENY SELECT ON Schema:: Customers FROM Soles
F. REVOKE SELECT ON Object::Regions FROM UserA
G. EXEC sp_oddrolemember 'Sales', 'UserA'
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema: Customers FROM UserA
J. REVOKE SELECT ON Object::Regions FROM Soles

Question #5
You are preparing to install SQL Server 2012 Master Data Services (MDS).
You need to ensure that the database requirements are met.
What should you install?

A. Microsoft SharePoint Foundation 2010 SP1
B. SQL Server 2012 Enterprise (64-bit) x64 on the database server
C. SQL Server 2008 Enterprise (64-bit) x64 on the database server
D. Microsoft SharePoint Server 2010 Enterprise Edition SP1

Solutions: