Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 141:

  DRAG DROP

  You administer three Microsoft SQL Server 2012 servers named ServerA, ServerB, and ServerC. ServerA is the acting principal and ServerB is the mirror.

  You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB.

  You need to achieve this goal without delaying synchronization.

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

  Which of the following conditions must exist prior to restoring a system database in SQL Server 2012?

  A. The hard drive must be reformatted.
  B. You must detach the user databases from the SQL 2012 instance.
  C. The SQL Server 2012 instance must be started in single-user mode.
  D. The SQL Server 2012 instance must be removed.
  C. The SQL Server 2012 instance must be started in single-user mode.

  ---

  References: https://msdn.microsoft.com/en-us/library/ms188236(v=sql.110).aspx

• Question 143:

  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 database 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 UserA is disallowed to select from the Regions table.

  Which Transact-SQL statement should you use?

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

• Question 144:

  You administer a Microsoft SQL Server 2012 database.

  You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated permissions.

  You need to ensure that the job step can run using a different user account.

  What should you use?

  A. A notification
  B. A schedule
  C. A job category
  D. A proxy
  E. An operator
  F. An alert
  D. A proxy

  ---

  Because job steps run in the context of a specific Microsoft Windows user, that user must have the permissions and configuration necessary for the job step to execute. For example, if you create a job that requires a drive letter or a Universal Naming Convention (UNC) path, the job steps may run under your Windows user account while testing the tasks. However, the Windows user for the job step must also have the necessary permissions, drive letter configurations, or access to the required drive. Otherwise, the job step fails. To prevent this problem, ensure that the proxy for each job step has the necessary permissions for the task that the job step perform

  References: https://docs.microsoft.com/en-us/sql/ssms/agent/manage-job-steps

• Question 145:

  You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction.

  You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.

  You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. Which Transact-SQL batch should you use?

  

  B. ALTER INDEX ALL ON AccountTransaction REBUILD
  C. D.
  A

• Question 146:

  You administer a Microsoft SQL Server instance.

  You discover that the SQL Agent Error Log file is rapidly growing in size.

  You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute.

  What should you do?

  A. Execute the sp_cycle_agent_errorlog stored procedure.
  B. Configure event forwarding.
  C. Enable the Auto Shrink option on the master database.
  D. Enable the Auto Shrink option on the msdb database.
  E. Disable the Include execution trace messages feature.
  E. Disable the Include execution trace messages feature.

• Question 147:

  You are the lead database administrator (DBA) of a Microsoft SQL Server environment.

  All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server.

  You need to create a server role named SpecialDBARole that can perform the following functions:

  View all databases.

  View the server state.

  Assign GRANT, DENY, and REVOKE permissions on logins.

  You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary. Which three SQL statements should you use? Each correct answer presents part of the solution.

  A. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
  B. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  C. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  D. CREATE SERVER ROLE [SpecialDBARole]; AUTHORIZATION serveradmin;
  E. GRANT VIEW DEFINITION TO [SpecialDBARole];
  F. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
  G. GRANT VIEW ANY DATABASE TO [SpecialDBARole];
  H. CREATE SERVER ROLE [SpecialDBARole]; AUTHORIZATION sysadmin
  A. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
  B. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  C. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];

  ---

  A: View all databases. View the server state.

  B: Assign GRANT, DENY, and REVOKE permissions on logins.

  Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have

  access to a database. Additionally, they can reset passwords for SQL Server logins.

  C: You need to add DOMAIN\JrDBAs to the server role.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

  References: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175892(v=sql.105)

• Question 148:

  DRAG DROP

  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:

  1. Both nodes receive the update.

  2. Downtime is minimized.

  3. 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:

  

  

  ---

  Step 1: Install the security patch on Node A.

  Execute the SQL Server Service Pack package at the first passive node. The passive node in this scenario is Node A.

  Do not start with Node B: If you update a SQL Server failover cluster instance on an active node, SQL Server services will be stopped. This causes SQL Server downtime.

  Step 2: is Failover from Node B to Node A

  Failover from Active to Passive Node.

  Step 3: Install the security patch on Node B

  Note: Starting with the update to Microsoft SQL Server 2012 Service Pack 1, customers with SQL Server 2012 failover cluster instances can greatly benefit from the fully automated update process provided by Cluster-Aware Updating (CAU).

  CAU causes minimal down time for SQL Server 2012 client connectivity. While the primary node is being updated, the SQL Server clustered role (also called the resource group) goes through two failovers. The first failover happens before

  updating the primary node when CAU brings the original primary node into maintenance mode. During the update process on the original primary node, the SQL clustered role is temporarily brought online on a new primary node. The second

  failover happens once the update process is complete on the original primary node, and CAU fails back the SQL Server clustered role to the original primary node and brings it online.

  References:

  https://stevethompsonmvp.wordpress.com/2014/12/29/installing-a-service-pack-for-sql-server-cluster/ https://support.microsoft.com/am-et/help/958734/sql-server-failover-cluster-rolling-patch-and-service-pack-process

• Question 149:

  DRAG DROP

  You are migrating an OLTP database from Windows Azure SQL Database to on-premise. You are planning the installation of a Microsoft SQL Server 2012 server failover cluster. The server has the following partitions:

  

  You install the operating system on the C: drive.

  The database solution will use row versioning, triggers, and cursors. The tempdb database cannot be placed on the same disk subsystem as the OLTP database.

  The OLTP database needs to be on the fastest disk subsystem possible. The database is currently 200GB in size. It will not substantially increase in size in the next 3 years.

  You need to plan the usage of the disk subsystems.

  To which hard disk should each database belong? (To answer, drag the appropriate database or databases to their corresponding hard disk or disks in the answer area. Answer choices may be used once, more than once, or not at all.

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

  Select and Place:

  

  

• Question 150:

  What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?

  A. 10
  B. 25
  C. 50
  D. 100
  C. 50