Prep and try our 70-457 valid and latest training questions & answers

Microsoft 70-457 Exam Overview:

Microsoft 70-457 Exam Syllabus Topics:

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

1. You administer a Microsoft SQL Server 2012 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost.
Data can be recovered by using the minimum amount of administrative effort.
What should you do? Choose all that apply.

A) Create a DIFFERENTIAL database backup every 24 hours.
B) Configure the database to use the FULL recovery model.
C) Create a DIFFERENTIAL database backup every 4 hours.
D) Configure the database to use the SIMPLE recovery model.
E) Create a LOG backup every 5 minutes.
F) Create a FULL database backup every 24 hours.

2. You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You have the following query:

You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

A) SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID,ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC
B) SELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
MAX(DiscountAmount)
FROM Sales.Details
ORDER BY SalesTerritoryID DESC, ProductID DESC

3. 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) Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C) Deny Delete permissions on each table in the Sales schema for each user.
D) Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

4. You administer a Microsoft SQL Server 2012. A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

A) EXEC sp_helpdb 60
B) DBCC OPENTRAN
C) SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
D) SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60

5. You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database. You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention. Which configuration should you use?

A) Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
B) Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
C) Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
D) Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E) SQL Server that includes an application database configured to perform transactional replication
F) SQL Server that includes an application database configured to perform snapshot replication
G) Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
H) Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

Solutions: