Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 281:

  You have a SQL Server database named DB1.

  You plan to load 10 million rows to DB1 by running bcp.exe.

  You need to modify DB1 to minimize the amount of space required to store the transaction logs during the load operation. The solution must ensure that you can perform a point-in-time restore.

  What data base setting should you modify?

  A. Compatibility level to 120
  B. Recovery model to Bulk logged
  C. Containment type to Partial
  D. Recovery model to Simple
  C. Containment type to Partial

  ---

  A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

  The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

  Incorrect Answers:

  A: To change the compatibility level, select a different option from the list. The choices are SQL Server 2008 (100), SQL Server 2012 (110), or SQL Server 2014 (120).

  B: Point-in-time recovery is not supported in the Bulk logged recovery model.

  D: The Simple recovery model does not support Point-in-time restores

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

• Question 282:

  You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.

  You have permissions on both Database1 and Database2.

  You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.

  You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.

  Which Transact-SQL statement should you use?

  A. USE Database2
  B. EXECUTE AS OWNER
  C. USE Database1
  D. EXECUTE AS CALLER
  B. EXECUTE AS OWNER

  ---

  References:

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

  http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-on-linked-server/

• Question 283:

  You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance.

  You need to ensure that users can create subscriptions.

  What should you configure?

  A. item security for the reports
  B. SQL Agent Proxy Account
  C. SSRS Execution Account
  D. processing options for the reports
  A. item security for the reports

  ---

  User requirements. The author of the subscription must have permission to "Manage reports" and "Manage all subscriptions." The author must also have the necessary credentials to access the external data source that contains subscriber data.

  An item-level task is a collection of permissions that relate to a report, folder, report model, resource, or shared data source.

  References: https://docs.microsoft.com/en-us/sql/reporting-services/security/tasks-and-permissions-item-level-tasks

• Question 284:

  You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.

  You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.

  You need to ensure that AgentPortalUser can send email messages.

  What should you do?

  A. Configure the AgentPortalUser user as a member of SQLAgentOperatorRole in the msdb database.
  B. Configure the Microsoft SQL Server Agent to use Database Mail.
  C. Enable the guest user in the msdb database.
  D. Configure the AgentPortalUser user as a member of DatabaseMailUserRole in the msdb database.
  E. Disable the guest user in the msdb database.
  F. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
  D. Configure the AgentPortalUser user as a member of DatabaseMailUserRole in the msdb database.

  ---

  To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database. Incorrect Answers:

  B: SQL Server Agent can be configured to use Database Mail. This is required for alert notifications and automatic notification when a job completes. References: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail

• Question 285:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 server.

  A variety of issues occur from time to time in the production environment. You need to identify the appropriate tool for each issue.

  Which tool or tools should you use? (To answer, drag the appropriate tool or tools to the correct issue or issues in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes

  or scroll to view content.)

  Select and Place:

  

  

• Question 286:

  You administer a SQL Server database instance.

  You need to configure the SQL Server Database Engine service on a failover cluster.

  Which user account should you use?

  A. A domain user
  B. The BUILTIN\SYSTEM account
  C. A local user with Run as Service permissions
  D. The SQLBrowser account
  A. A domain user

  ---

  

  References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2017

• Question 287:

  You are implementing the indexing strategy for a fact table in a data warehouse.

  The fact table is named Quotes.

  The table has no indexes and consists of seven columns:

  [ID]

  [QuoteDate]

  [Open]

  [Close]

  [High]

  [Low]

  [Volume]

  Each of the following queries must be able to use a columnstore index:

  SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

  SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

  SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

  You need to ensure that the indexing strategy meets the requirements.

  The strategy must also minimize the number and size of the indexes.

  What should you do?

  A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
  B. Create three columnstore indexes: One containing [QuoteDate] and [Close] One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
  C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
  D. Create two columnstore indexes: One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]
  C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].

  ---

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

• Question 288:

  You administer a Microsoft SQL Server 2012 database.

  You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

  

  You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.

  A hardware failure occurs and so a new server must be installed and configured.

  After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.

  You need to be able to restore the database.

  Which Transact-SQL statement should you use before attempting the restore?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 289:

  DRAG DROP

  You administer a Microsoft SQL Server database.

  The database contains a schema named PRODUCT. PRODUCT contains several tables and views with sensitive data, as well as various stored procedures and functions.

  In order to configure security for PRODUCT, you need to determine how to meet the security requirements listed in the answer area.

  Which command or commands should you use? To answer, drag the appropriate command or commands to their corresponding security requirement or requirements 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:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017

• Question 290:

  DRAG DROP

  You have a consolidated SQL Server instance that contains databases for several applications.

  You need to limit the maximum degree of parallelism for an application named App1.

  Which four 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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

  ---

  Step 1: Enable Resource Governor

  The Resource Governor is turned off by default. You can enable the Resource Governor by using either SQL Server Management Studio or Transact-SQL.

  Step 2: Create a resource pool.

  In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine.

  Step 3: Create a workload group

  In the SQL Server Resource Governor, a workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions.

  Each workload group is in a resource pool, which represents a subset of the physical resources of an instance of the Database Engine. When a session is started, the Resource Governor classifier assigns the session to a specific workload

  group, and the session must run using the policies assigned to the workload group and the resources defined for the resource pool.

  Step 4: Write a classifier function.

  The SQL Server resource governor 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.

  Incorrect Answers:

  Not: Restart SQL Server

  The setting takes effect immediately without restarting the server.

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