Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 241:

  You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role named BillingUsers.

  All objects in Contoso are in the dbo schema.

  You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers.

  Which Transact-SQL statement should you use?

  A. EXEC sp_addrolemember' db_procexecutor', 'BillingUsers'
  B. CREATE ROLE proc_calller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
  C. GRANTEXECUTE ON Schema::dbo TO BillingUsers
  D. GRANTEXECUTE ON Contoso::dbo TO BillingUsers
  C. GRANTEXECUTE ON Schema::dbo TO BillingUsers

  ---

  If you want to do it on schema level: GRANT EXECUTE ON SCHEMA ::dbo TO Incorrect Answers:

  B: DB_Executor is none of the predefined SQL Server database role C, D: Incorrect schema.

  References: http://www.sqlservercentral.com/articles/Permissions/107472/

• Question 242:

  You administer all the deployments of Microsoft SQL Server 2012 in your company.

  A database contains a large product catalog that is updated periodically.

  You need to be able to send the entire product catalog to all branch offices on a monthly basis.

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

• Question 243:

  Before moving an SQL Server 2012 database file to a new location, which of the following must first be accomplished?

  A. The database must be set to OFFLINE.
  B. The SQL Server Service must be stopped.
  C. The database must be deleted.
  D. The database must have the "shrink" operation run.
  A. The database must be set to OFFLINE.

  ---

  References: https://msdn.microsoft.com/en-us/library/ms345483%28v=sql.110%29.aspx

• Question 244:

  Which of the following files keeps track of all transactions that occur in an SQL Server database?

  A. Transaction Log
  B. Redo Log
  C. Archive Log
  D. Data File
  A. Transaction Log

  ---

  References: https://msdn.microsoft.com/en-us/library/ms190925%28v=sql.110%29.aspx

• Question 245:

  DRAG DROP

  You develop an application that uses data from a Microsoft SQL Server database.

  A stored procedure named MyProcedure experiences excessive blocking problems.

  You need to enable row versioning. However, you do not want to force every connection to have row versioning enabled.

  Which code segment or segments should you use to complete the Transact-SQL statements? To answer, drag the appropriate command or commands to the appropriate position or positions in the answer area to complete the Transact-SQL

  statements. Answer choices may be used once, more than once, or not at all. Answer targets may be used once. Additionally, you may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1: SET ALLOW_SNAPSHOT_ISOLATION ON

  Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

  The following Transact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION: ALTER DATABASE AdventureWorks2008R2 SET ALLOW_SNAPSHOT_ISOLATION ON;

  Incorrect: Not READ_COMMITTED_SNAPSHOT

  When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database.

  Box 2: SET TRANSACTION ISOLATION LEVEL SNAPSHOT

  The ALLOW_SNAPSHOT_ISOLATION ON option transactions can specify the SNAPSHOT transaction isolation level.

  References:

  https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx

  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options

• Question 246:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a customer table created by using the following definition:

  

  You need to ensure that the minimum amount of disk space is used to store the data in the customer table. What should you do?

  A. Implement row-level compression.
  B. Implement page-level compression.
  C. Convert all indexes to Column Store indexes.
  D. Implement Unicode compression.
  B. Implement page-level compression.

• Question 247:

  HOTSPOT

  You administer a Microsoft SQL Server database.

  The database is in the Simple recovery mode.

  You schedule the following backup plan:

  Full backup every day at midnight

  Differential backups every hour on the hour, except at midnight

  The database fails at 20:45 hours.

  You need to use SQL Server Management Studio (SSMS) to begin restoring the database to a new server instance with a minimum amount of data loss.

  Which options should you select on the Options page of the Restore Database window? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

  ---

  First, restore full database backup, differential database backup and all transaction log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option. References: https://blog.sqlauthority.com/2009/07/15/sql-server-restore-sequence-and-understanding-norecovery-and-recovery/

• Question 248:

  Which of the following SQL Server 2012 database is NOT created by default in a new installation?

  A. model
  B. msdb
  C. tempdb
  D. sql
  E. master
  A. model

• Question 249:

  DRAG DROP

  You administer a single Microsoft SQL Server instance on a two-node failover cluster that has nodes named Node A and Node B. The instance is currently running on Node A.

  You want to patch both Node A and Node B by using the most recent SQL Server Service Pack.

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

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

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

  You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.

  What should you create?

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