Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 311:

  You administer a Microsoft SQL Server failover cluster.

  You need to ensure that a failover occurs when the server diagnostics returns query_processing error.

  Which server configuration property should you set?

  A. FailureConditionLevel
  B. HealthCheckTimeout
  C. Verbose Logging
  D. SqlDumperDumpTimeout
  A. FailureConditionLevel

• Question 312:

  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 track all SELECT statements issued in the Contoso database only by users in a role named Sales.

  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
  H. A Data Collector Set
  F. A Database Audit Specification

• Question 313:

  You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Microsoft SQL Server 2012. All databases are hosted on a SAN (Storage Area Network).

  You need to design a database solution for a new application.

  You are tasked with designing a high-availability database solution.

  The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution?

  A. You should include two servers configured as a failover cluster.
  B. You should include two servers and database mirroring.
  C. You should include two servers and log shipping.
  D. You should include two servers configure as a SQL Server Availability Group
  A. You should include two servers configured as a failover cluster.

• Question 314:

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

  You administer a Microsoft SQL Server database 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:

  

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

• Question 315:

  You use a contained database named ContosoDb within a domain.

  You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

  Which type of user should you create?

  A. User mapped to a certificate
  B. SQL user without login
  C. Domain user
  D. SQL user with login
  C. Domain user

  ---

  Contained user

  There are two types of users for contained databases.

  Contained database user with password

  Contained database users with passwords are authenticated by the database.

  Windows principals

  Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.

  The database trusts the authentication by Windows.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-2017

• Question 316:

  You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

  

  You discover that all files except Sales_2.ndf are corrupt.

  You need to recover the corrupted data in the minimum amount of time.

  What should you do?

  A. Perform a file restore.
  B. Perform a transaction log restore.
  C. Perform a restore from a full backup.
  D. Perform a filegroup restore.
  A. Perform a file restore.

  ---

  Under the simple recovery model, the file must belong to a read-only filegroup.

  Under the full or bulk-logged recovery model, before you can restore files, you must back up the active transaction log (known as the tail of the log). For more information, see Back Up a Transaction Log (SQL Server).

  To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate

  that is used to encrypt the database encryption key must be retained as long as the backup is needed. For more information, see SQL Server Certificates and Asymmetric Keys.

  Incorrect:

  Not C: We only need to restore the corrupt files.

  References:

  http://technet.microsoft.com/en-us/library/ms187048.aspx

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

• Question 317:

  You administer a Microsoft SQL Server database.

  You provide temporary securityadmin access to User1 to the database server.

  You need to know if User1 adds logins to securityadmin.

  Which server-level audit action group should you use?

  A. SERVER_STATE_CHANGE_GROUP
  B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
  C. SUCCESSFUL_LOGIN_GROUP
  D. SERVER_ROLE_MEMBER_CHANGE_GROUP
  E. DATABASE_ROLE_MEMBER_CHANGE_GROUP
  F. SERVER_OBJECT_CHANGE_GROUP
  G. SERVER_PERMISSION_CHANGE_GROUP
  D. SERVER_ROLE_MEMBER_CHANGE_GROUP

  ---

  The SERVER_ROLE_MEMBER_CHANGE_GROUP event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to the Audit Add Login to Server Role Event Class.

  Incorrect Answers:

  A. The SERVER_STATE_CHANGE_GROUP event is raised when the SQL Server service state is modified. Equivalent to the Audit Server Starts and Stops Event Class.

  B. The SERVER_PRINCIPAL_IMPERSONATION_GROUP event is raised when there is an impersonation within server scope, such as EXECUTE AS . Equivalent to the Audit Server Principal Impersonation Event Class.

  C. The SUCCESSFUL_LOGIN_GROUP Indicates that a principal has successfully logged in to SQL Server. Events in this class are raised by new connections or by connections that are reused from a connection pool. Equivalent to the Audit Login Event Class.

  References: http://technet.microsoft.com/en-us/library/cc280663.aspx

• Question 318:

  You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.

  The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

  

  A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
  B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
  C. Create an AFTER UPDATE trigger on the view.
  D. Create an INSTEAD OF UPDATE trigger on the view.
  D. Create an INSTEAD OF UPDATE trigger on the view.

• Question 319:

  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 any of the tables in the Customers schema.

  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'
  F. DENY SELECT ON Schema::Customers FROM UserA

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-schema-permissions-transact-sql http://msdn.microsoft.com/en-us/library/ff848791.aspx

• Question 320:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  You need to convert the database to a contained database. You also need to ensure that all users are converted to contained users.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of http://msdn.microsoft.com/en -us/library/ms191464.aspx actions to the answer area and arrange them in the correct order.)

  Select and Place: