Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 (70-457) Free Practice Test

Question 1

You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster. An additional node named Node C has been added to the existing cluster. You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node

B. Run the Add Node to SQL Server Failover Cluster Wizard on Node

C. Use Cluster Administrator to add a new Resource Group to Node B.

D. Use Node A to install SQL Server on Node C.

Correct Answer: B

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 2

Question 3

Question 4

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

A. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

B. Deny Delete permissions on each table in the Sales schema for each user.

C. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

D. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 5

You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks. You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA. 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.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - YES
I don't think this question will ever come up. The MSDN link says it is being deprecated and to use Availability Groups instead: http://msdn.microsoft.com/en-us/library/ms189852.aspx
However, the answer is correct. http://msdn.microsoft.com/en-us/library/ms190941.aspx

Question 6

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O. What should you configure?

A. Processor affinity

B. Max worker threads

C. Lightweight pooling

D. I/O affinity

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 7

You administer a Microsoft SQL Server 2012 default instance. The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server. You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. What should you do? Choose all that apply.

A. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.

B. Execute the following Transact-SQL command: sp_configure 'remote admin connections',

C. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.

D. Restart the SQL Server Agent Service.

E. Execute the following Transact-SQL command: sp_configure 'remote access', 1

F. Execute the Reconfigure command.

Correct Answer: B,C,F

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 8

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal. What should you do?

A. Perform a page restore.

B. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.

C. Perform a partial restore.

D. Restore the latest full backup.

E. Perform a point-in-time restore.

F. Restore the latest full backup. Then, restore the latest differential backup.

G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.

H. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 9

You create a view based on the following statement:

You grant the Select permission to User1 for this view. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - NO
Reference: http://msdn.microsoft.com/en-us/library/hh213020.aspx Reference: http://msdn.microsoft.com/en-us/library/ms186819.aspx Reference: http://msdn.microsoft.com/en-us/library/ms173846.aspx

Question 10

You generate a daily report according to the following query:

You need to improve the performance of the query. What should you do?

A. Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)

B. Rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.
CustomerID, 90))
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime)
RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())

C. Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())

D. Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS ( SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] FROM Sales.SalesOrder GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Welcome to TestSimulate

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 (70-457) Free Practice Test