Try Microsoft : 70-457 valid & accurate questions and 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:

Question 1

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:
<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00"
Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00"
Name="Customer A" Country="Australia" />
Which Transact-SQL query should you use?

A. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
C. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
D. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
F. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
G. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
H. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

Question 2

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the database. Which configuration should you use?

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

Question 3

You administer a Microsoft SQL Server 2012 server that has multiple databases. You need to ensure that users are unable to create stored procedures that begin with sp_. 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:

Question 4

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
B. Grant the sysadmin role on the database to User1.
C. Grant the db_owner role on the database to User1.
D. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
E. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.

Question 5

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that data changes are sent to a non-SQL Server database server in near real time. You also need to ensure that data on the primary server is unaffected. Which configuration should you use?

A. 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
B. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
C. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
D. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. SQL Server that includes an application database configured to perform transactional replication
G. SQL Server that includes an application database configured to perform snapshot replication
H. 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

Solutions: