Learn with Microsoft : 70-458 training material for 100% pass

Microsoft 70-458 Exam Overview:

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

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 Server 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 ensure that UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?

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

Question 2

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

A. Query the sys.dm_db_index_usage_stats DMV.
B. Use the Database Engine Tuning Advisor.
C. Query the sys.dm_db_missing_index_columns DMV.
D. Query the sys.dm_db_missing_index_details DMV.

Question 3

You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.
You need to run an update statement against the table.
What should you do first?

A. Change the recovery model of the database to Bulk-logged.
B. Drop the columnstore index.
C. Pause the columnstore index.
D. Change the non-clustered columnstore index to be a clustered columnstore index.

Question 4

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 implement log shipping of the financial database to another SQL Server 2012 instance. You decide to failoverto this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?

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

Question 5

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certaingroups of tasks will share variable and transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group.
Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)

Solutions: