Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 81:

  You have a SQL Server instance that uses database roles. The instance contains two databases named Customers and Sales.

  A user named User1 has the required roles to access Customers.

  You need to ensure that User1 can access Sales.

  Which statement should you execute first?

  A. ALTER ROLE...ADD MEMBER
  B. CREATE USER
  C. CREATE LOGIN
  D. ALTER SERVER ROLE...ADD MEMBER
  A. ALTER ROLE...ADD MEMBER

  ---

  The ALTER ROLE...ADD MEMBER command adds or removes members to or from a database role.

  Example: This example creates a database role named Sales. It adds a database user named Barry to the membership.

  CREATE ROLE Sales;

  ALTER ROLE Sales ADD MEMBER Barry;

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql

• Question 82:

  You administer a Microsoft SQL Server database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.

  Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.

  You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.

  Which type of solution should you use?

  A. Partitioned tables
  B. Online index rebuild
  C. Change data capture
  D. Change tracking
  A. Partitioned tables

• Question 83:

  Which of the following is a prerequisite for an SQL Server 2012 installation?

  A. .NET Framework 3.5 SP1
  B. Visual Basic 6
  C. Internet Explorer 10
  D. SQL Server Integration Services
  A. .NET Framework 3.5 SP1

  ---

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

• Question 84:

  You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.

  You grant User1 access to execute the stored procedure.

  You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Grant the VIEW SERVER STATE permission to User1.
  B. Move the stored procedure to the User1 schema.
  C. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  D. Grant the db_datareader role on the database to User1.
  E. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS (newlogin) statement.
  C. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  E. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS (newlogin) statement.

  ---

  References: http://msdn.microsoft.com/en-us/library/ms187861.aspx http://msdn.microsoft.com/en-us/library/ms191291.aspx

• Question 85:

  You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size.

  The media files will require fast access and will be retrieved frequently.

  What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  F. Use the VARBINARY data type.

  ---

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

• Question 86:

  DRAG DROP

  You administer three Microsoft SQL Server 2008 R2 instances.

  Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:

  SQL1 is the Principal server.

  SQL2 is the mirror server.

  SQL3 is the witness server.

  You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade.

  Which six 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:

  

  

  ---

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

• Question 87:

  You administer a Microsoft SQL Server database.

  Users report that a billing application becomes unresponsive during busy times of the day.

  While investigating, you notice large number of processes taking or waiting for table locks.

  You suspect that SQL Server is assigning stronger locks to queries.

  You start a SQL Profiler trace.

  Which event should you select?

  A. Deadlock graph
  B. Lock: Escalation
  C. Lock: Timeout
  D. Lock: Deadlock
  B. Lock: Escalation

• Question 88:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.

  You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.

  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:

  

  

  ---

  Example: The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table in the AdventureWorks2012 database. Copy USE master ; GO -- Create the server audit. CREATE SERVER AUDIT Payrole_Security_Audit TO FILE ( FILEPATH = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ; GO -- Enable the server audit. ALTER SERVER AUDIT Payrole_Security_Audit WITH (STATE = ON) ; GO -- Move to the target database. USE AdventureWorks2012 ; GO -- Create the database audit specification. CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ; GO References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql http://msdn.microsoft.com/en-us/library/cc280386.aspx http://msdn.microsoft.com/en-us/library/cc280448.aspx http://msdn.microsoft.com/en-us/library/cc280404.aspx

• Question 89:

  You administer a Microsoft SQL Server database.

  Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.

  You need to identify the root cause of the issue by retrieving the error message.

  What should you do?

  A. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
  B. Flag all stored procedures for recompilation by using sp_recompile.
  C. Create an Extended Events session by using the sqlserver.error_reported event.
  D. Create an Extended Events session by using the sqlserver.errorlog_written event.
  C. Create an Extended Events session by using the sqlserver.error_reported event.

  ---

  Event sqlserver.error_reported: This event gets fired every time that an error happens in the server

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

• Question 90:

  You are the administrator of a Microsoft SQL Server 2012 server.

  Some applications consume significant resources.

  You need to manage the server workload by restricting resource-intensive applications. You need to dynamically limit resource consumption.

  What should you do?

  A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources.
  B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.
  C. Configure Extended Events to monitor and restrict resource limits allowed by each application type.
  D. Create a new Plan Guide with a Scope Type of SQL and define the resource limits for each application.
  B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.

  ---

  SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that

  incoming application requests can use.

  The following three concepts are fundamental to understanding and using Resource Governor:

  Resource pools. A resource pool, represents the physical resources of the server.

  Workload groups. A workload group serves as a container for session requests that have similar classification criteria.

  Classification. The Classification process assigns incoming sessions to a workload group based on the characteristics of the session. You can tailor the classification logic by writing a user-defined function, called a classifier function.

  Resource Governor also supports a classifier user-defined function for implementing classification rules.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor