Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 101:

  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 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_droprolemember '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

  ---

  References: http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx http://msdn.microsoft.com/en-us/library/ms187728.aspx

• Question 102:

  You administer a Microsoft SQL Server instance that contains a financial database hosted on a storage area network (SAN).

  The financial database has the following characteristics:

  A data file of 2 terabytes is located on a dedicated LUN (drive D).

  A transaction log of 10 GB is located on a dedicated LUN (drive E).

  Drive D has 1 terabyte of free disk space.

  Drive E has 5 GB of free disk space.

  The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

  The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

  These data load operations must occur in the minimum amount of time.

  A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

  The financial database has been damaged.

  You need to perform a tail-log backup.

  Which backup option should you use?

  A. NORECOVERY
  B. FULL
  C. NO_CHECKSUM
  D. CHECKSUM
  E. Differential
  F. BULK_LOGGED
  G. STANDBY
  H. RESTART
  I. SKIP
  J. Transaction log
  A. NORECOVERY

  ---

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQL statement.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

• Question 103:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 instance.

  An application executes a large volume of dynamic queries.

  You need to reduce the amount of memory used for cached query plans.

  What should you do? (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:

  

  

  ---

  Box 1: EXEC sp_configure 'show advanced options', 1; RECONFIGURE Box 2: sp_CONFIGURE ‘optimize for ad hoc workloads

• Question 104:

  You administer a Microsoft SQL Server 2012 instance.

  You need to stop a blocking process that has an SPID of 64 without stopping other processes

  What should you do?

  A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
  B. Restart the SQL Server service.
  C. Execute the following Transact-SQL statement: KILL 64
  D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64'
  C. Execute the following Transact-SQL statement: KILL 64

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms173730.aspx

• Question 105:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a Product 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 Product table. What should you do?

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

  ---

  References: http://msdn.microsoft.com/en-us/library/cc280449.aspx http://msdn.microsoft.com/en-us/library/cc280464.aspx http://msdn.microsoft.com/en-us/library/cc280576.aspx http://msdn.microsoft.com/en-us/library/ee240835.aspx

• Question 106:

  Which of the following is NOT a database role that you can assign users in SQL Server 2012?

  A. db_ddladmin
  B. db_owner
  C. db_datawriter
  D. db_databaseadmin
  D. db_databaseadmin

  ---

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

• Question 107:

  You administer a Microsoft SQL Server.

  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. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
  B. SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60
  C. DBCC INPUTBUFFER (60)
  D. DBCC OPENTRAN
  A. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql

• Question 108:

  You administer a Microsoft Azure SQL Database instance named Inventory that contains a stored procedure named p_AddInventory.

  Users need to be able to SELECT from all tables in the database and execute the stored procedure.

  You need to grant only the necessary permissions.

  What should you do?

  A. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role.
  B. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Grant VIEW DEFINITION to the role. Add all users to the role.
  C. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role.
  D. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
  A. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role.

  ---

  The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database. References: https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

• Question 109:

  You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.

  Currently, Server01/HA is the primary replica.

  You need to ensure that the following requirements are met:

  Backup operations occur on Server03/HA.

  If Server03/HA is unavailable, backup operations occur on Server02/HA. Backup operations do not occur on Server01/HA.

  How should you configure HaContoso?

  A. Set the backup preference of HaContoso to Prefer Secondary. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  B. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  C. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.
  D. Set the exclude replica of Server01/HA to true. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.
  C. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

• Question 110:

  You administer a Microsoft SQL Server environment. One of the SQL Server instances contains a database named Sales.

  You plan to migrate Sales to Microsoft Azure SQL Database.

  To do so, you need to implement a contained database.

  What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Set database containment to AZURE.
  B. Enable server property contained database authentication.
  C. Disable server property cross db ownership chaining.
  D. Set database containment to PARTIAL.
  E. Disable server property contained database authentication.
  F. Set database containment to FULL.
  B. Enable server property contained database authentication.
  D. Set database containment to PARTIAL.