Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 151:

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

• Question 152:

  You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).

  Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.

  You need to reduce the occurrence of these timeout errors. What should you do?

  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
  B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
  C. Change the Merge agent on the problem subscribers to run continuously.
  D. Set the Remote Connection Timeout on the Publisher to 0.
  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.

• Question 153:

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

  Select and Place:

  

  

• Question 154:

  You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012. The development team develops a new

  application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.

  You need to migrate the database without affecting the production environment.

  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 155:

  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:

  

  One of the hard disk drives that stores the reporting database fails at 16:40 hours.

  You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.

  What should you do?

  A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  B. Perform a partial restore.
  C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  D. Restore the latest full backup.
  E. Perform a page restore.
  F. 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.
  G. Restore the latest full backup. Then, restore the latest differential backup.
  H. Perform a point-in-time restore.
  G. Restore the latest full backup. Then, restore the latest differential backup.

• Question 156:

  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. Query the sys.dm_db_missing_index_details DMV.
  C. Use the Database Engine Tuning Advisor.
  D. Query the sys.dm_db_missing_index_columns DMV.
  C. Use the Database Engine Tuning Advisor.

• Question 157:

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

  Select and Place:

  

  

• Question 158:

  You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.

  A sample of this data is as shown in the following table.

  

  You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact-SQL statement should you use?

  

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

• Question 159:

  You administer a Microsoft SQL Server 2012 database.

  You use an OrderDetail table that has the following definition:

  

  You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.

  Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 160:

  You administer a SQL 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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.

  Which Transact-SQL statement should you use?

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