Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 161:

  You administer a Microsoft SQL Server 2012 database.

  You create an availability group named haContosoDbs. Your primary replica is available at Server01 \Contoso01.

  You need to configure the availability group to have the highest availability. You also need to ensure that no data is lost.

  Which Transact-SQL statement should you use?

  A. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
  B. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)
  C. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
  D. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)
  C. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

• Question 162:

  You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance.

  What should you do?

  A. Use the Data Quality Client to configure the application.
  B. Start the SQL Server Browser Service.
  C. Use the Master Data Services Configuration Manager to configure the application.
  D. Start the SQL Server Integration Services Service.
  B. Start the SQL Server Browser Service.

• Question 163:

  You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

  

  You write the following Transact-SQL query:

  

  You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

  A. Use a FORCESCAN hint in the query.
  B. Add a clustered index on SalesOrderId in SalesOrderHeader.
  C. Use a FORCESEEK hint in the query.
  D. Update statistics on SalesOrderId on both tables.
  D. Update statistics on SalesOrderId on both tables.

• Question 164:

  You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 165:

  You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster.

  Node B is primary, and Node A is secondary.

  You need to install a security patch on both nodes.

  You need to ensure that the following requirements are met:

  Both nodes receive the update.

  Downtime is minimized.

  No data is lost.

  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.)

  Select and Place:

  

  

• Question 166:

  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 the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.

  Which Transact-SQL statement should you use?

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

• Question 167:

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

• Question 168:

  You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 169:

  You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.

  You need to prevent users from disabling server audits in Server01.

  What should you create?

  A. an Alert
  B. a Resource Pool
  C. an Extended Event session
  D. a Database Audit Specification
  E. a SQL Profiler Trace
  F. a Server Audit Specification
  G. a Policy
  G. a Policy

• Question 170:

  You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.

  The tables are related by a column named CustomerID.

  You need to create a query that meets the following requirements:

  Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. Results must include customers who have not placed any orders.

  Which Transact-SQL query should you use?

  A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  D. SELECT CustomerName, CrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  D. SELECT CustomerName, CrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID