Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 221:

  DRAG DROP

  Your company has a data warehouse that contains all of the sales data for your company. Several business applications read data from the data warehouse.

  You plan to deploy an indexing strategy for a 2-TB table named FactSales contains a clustered index.

  You need to recommend a solution for index maintenance that meets the following requirements:

  Maintenance must be performed only when index fragmentation reaches 50 percent.

  Maintenance must minimize the impact on the database reads from the business applications.

  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.

  Select and Place:

  

  

  ---

  Step 1: Partition the table

  Step 2: Create a partition function

  To migrate SQL Server partition definitions to SQL Data Warehouse simply:

  Eliminate the SQL Server partition scheme.

  Add the partition function definition to your CREATE TABLE.

  Step 3: Create a partition scheme

  Partitioning requires a partition function and a partition scheme.

  Step 4: Rebuild the clustered index

  Rebuild the clustered index onto new partition function/scheme.

  References:

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

  https://blog.sqlrx.com/2017/02/24/sql-2016-partitioning-an-existing-table-with-a-columnstore-index/

• Question 222:

  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:

  

  At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database.

  You need to ensure that the transactional database is restored.

  You also need to ensure that data loss is minimal.

  What should you do?

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

  ---

  Requirements for Restoring Pages

  A page restore is subject to the following requirements:

  The databases must be using the full or bulk-logged recovery model. Some issues exist if you are using the bulk-logged model. For more information, see the following section.

  Pages in read-only filegroups cannot be restored. Trying to make a filegroup read-only will fail if there is a page restore going on at the same time in the filegroup.

  The restore sequence must start with a full, file, or filegroup backup.

  A page restore requires an unbroken chain of log backups up to the current log file, and they must all be applied so that the page is brought up to date with the current log file.

  As in a file-restore sequence, in each restore step, you can add more pages to the roll forward set.

  A database backup and page restore cannot be run at the same time.

  Bulk-logged Recovery Model and Page Restore

  For a database that uses the bulk-logged recovery model, page restore has the following additional conditions:

  Backing up while filegroup or page data is offline is problematic for bulk-logged data, because the offline data is not recorded in the log. Any offline page can prevent backing up the log. In this cases, consider using DBCC REPAIR, because

  this might cause less data loss than restoring to the most recent backup.

  If a log backup of a bulk-logged database encounters a bad page, it fails unless WITH

  CONTINUE_AFTER_ERROR is specified.

  Page restore generally does not work with bulk-logged recovery.

  A best practice for performing page restore is to set the database to the full recovery model, and try a log backup. If the log backup works, you can continue with the page restore. If the log backup fails, you either have to lose work since the

  previous log backup or you have to try running DBCC must be run with the REPAIR_ALLOW_DATA_LOSS option.

• Question 223:

  You administer a Microsoft SQL Server database server. One of the databases on the server supports a highly active OLTP application.

  Users report abnormally long wait times when they submit data into the application.

  You need to gather blocking data over an extended period of time. You also need to ensure minimum impact to server performance.

  What should you do?

  A. Run the sp_who2 command from a query window and examine the BlkBy column.
  B. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
  C. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
  D. Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000 ms.
  E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
  E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

  ---

  You can track down queries that are causing deadlocks. On solution is using a trace flag to write the deadlock information to the error log. You can implement trace flag 1222 to do just that. There are two ways to enable global trace flags. You can enable the trace flag when SQL Server starts by using the -T1222 startup option, or you can use the DBCC TRACEON(1222,-1) command after SQL Server has started.

  References: http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/ http://msdn.microsoft.com/en-us/library/ms188396.aspx

• Question 224:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model.

  You plan to perform a bulk import of a very large text file.

  You need to ensure that the following requirements are met during the bulk operation:

  The database transaction log is minimally affected.

  The database is online and all user transactions are recoverable.

  All transactions are fully recoverable prior to the import.

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

  You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.

  You discover performance issues when complex calculations are performed on large amounts of data under heavy system load.

  You need to limit the number of cores that process the calculations.

  What should you configure?

  A. Max worker threads
  B. Processor affinity
  C. I/O affinity
  D. Lightweight pooling
  B. Processor affinity

  ---

  To carry out multitasking, the operating system sometimes moves process threads among different processors. This is efficient from an operating system point of view, but can reduce SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.

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

• Question 226:

  You administer a Microsoft SQL Server database instance. You create a new user named UserA.

  You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server Agent jobs owned by UserA

  To which role should you add UserA?

  A. RSExecRole
  B. SQLAgentUserRole
  C. serveradmin
  D. DatabaseMailUserRole
  B. SQLAgentUserRole

• Question 227:

  You administer several Microsoft SQL Server database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).

  Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.

  You need to reduce the occurrence of these timeout errors.

  What should you do?

  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
  B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
  C. Change the Merge agent on the problem subscribers to run continuously.
  D. Set the Remote Connection Timeout on the Publisher to 0.
  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.

  ---

  When replication is configured, a set of agent profiles is installed on the Distributor. An agent profile contains a set of parameters that are used each time an agent runs: each agent logs in to the Distributor during its startup process and

  queries for the parameters in its profile.

  For merge subscriptions that use Web synchronization, profiles are downloaded and stored at the Subscriber. If the profile is changed, the profile at the Subscriber is updated the next time the Merge Agent runs. For more information about

  Web synchronization, see Web Synchronization for Merge Replication.

  Replication provides a default profile for each agent and additional predefined profiles for the

  Log Reader Agent, Distribution Agent, and Merge Agent. In addition to the profiles provided, you can create profiles suited to your application requirements. An agent profile allows you to change key parameters easily for all agents associated

  with that profile. For example, if you have 20 Snapshot Agents and need to change the query timeout value (the - QueryTimeout parameter), you can update the profile used by the Snapshot Agents and all agents of that type will begin using

  the new value automatically the next time they run.

  You might also have different profiles for different instances of an agent. For example, a

  Merge Agent that connects to the Publisher and Distributor over a dialup connection could use a set of parameters that are better suited to the slower communications link by using the slow link profile.

• Question 228:

  You administer a Microsoft SQL Server database that has multiple tables in the Sales schema.

  Some users must be prevented from deleting records in any of the tables in the Sales schema.

  You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort.

  What should you do?

  A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
  B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
  C. Deny Delete permissions on each table in the Sales schema for each user.
  D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.
  A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

• Question 229:

  DRAG DROP

  You administer a Microsoft SQL Server database that is used by an application.

  Users of the application report performance issues.

  You need to choose the appropriate tool for performance-tuning of SQL Server databases.

  Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks 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:

  

  

  ---

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

• Question 230:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database called Human_Resources. The database contains a table named dbo.Salary.

  You need to ensure that all read activity against dbo.Salary is audited and written to the Windows Security Log.

  What should you do? (To answer, move the three appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Note:

  An audit must exist before creating a server audit specification for it. When a server audit specification is created, it is in a disabled state.

  The general process for creating and using an audit is as follows.

  1. Create an audit and define the target.

  2. Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification.

  3. Enable the audit.

  4. Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

  Example (step 2) (we should audit SELECT on the Salary table):

  USE AdventureWorks2012 ;

  GO

  -- Create the database audit specification.

  

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