Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 81:

  You are the new database administrator for a SQL Server 2014 instance.

  You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off.

  You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need to set up a monitoring process that will capture any cases where statistics could have

  been useful if they existed.

  What should you do?

  A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
  B. Use the missing_column_statistics extended event.
  C. Query the sys.statistics system view to see all cases where the statistics were last needed.
  D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.
  B. Use the missing_column_statistics extended event.

• Question 82:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a Microsoft Azure SQL database that has Blob Auditing configured.

  You need to review the audit logs.

  Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

  

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 83:

  HOTSPOT

  A company has an on-premises Microsoft SOI Server environment and Microsoft Azure SQL Database instance's. The environment hosts several customer databases.

  You configure instances for a specific customer as an Always On Availability Group. The primary replica is located on premises the secondary replied is in Azure.

  You need to configure the availability group for planned manual failovers and forced failovers.

  In the table below, identify the failover mode that you must use for each failover type.

  NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  Column 1: Asynchronous-commit

  Asynchronous-commit replicas support only the manual failover mode.

  Column 2: Synchronous-commit

  Synchronous-commit replicas support two settings automatic or manual. The "automatic" setting supports both automatic failover and manual failover.

  Three forms of failover exist: automatic failover (without data loss), planned manual failover (without data loss), and forced manual failover (with possible data loss), typically called forced failover.

• Question 84:

  You have the following servers:

  

  SQL1 hosts a database named DB1.

  Users in the branch office must be able to access DB1 from SQL3.

  You need to configure log shipping on DB1 from the main office to the branch office.

  On which server should you configure the backup job?

  A. SQL1
  B. SQL2
  C. SQL3
  D. FS1
  A. SQL1

• Question 85:

  You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script:

  

  Each order is tracked by using one of the following statuses: Fulfilled Shipped Ordered Received

  You need to design the database to ensure that you can retrieve the status of an order on a given date.

  The solution must ensure that new statuses can be added in the future.

  What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

  A. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.
  B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
  C. Implement change data capture on the Sales.Orders table.
  D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.
  A. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.

• Question 86:

  A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

  A customer that uses an on-premises instance reports that queries take a long time to complete.

  You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.

  Which Transact-SQL segment should you use?

  A. sys.index_columns
  B. UPDATE STATISTICS
  C. CREATE STATISTICS
  D. SET AUTO_CREATE_STATISTICS ON
  D. SET AUTO_CREATE_STATISTICS ON

• Question 87:

  DRAG DROP

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

  

  You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

  

  Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the

  total database size.

  Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

  Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

  You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction.

  Changes made must not interrupt the log backup chain.

  You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

  All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the

  space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

  

  Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

  You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

  Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data.

  The queries must be load balanced over variable read-only replicas.

  Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles.

  The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.

  The wait statistics monitoring requirements for the instances are described in the following table.

  

  You need to propose a new process for the StagedExternal database.

  Which five actions should you recommended be performed 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:

  

  

  ---

  From scenario: Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

  You must minimize the growth of the StagedExternaldatabase log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction.

  Changes made must not interrupt the log backup chain.

  All databases use the full recovery model.

  References: https://technet.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

• Question 88:

  DRAG DROP

  You use SQL Server 2014 Enterprise Edition.

  Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.

  Management has decided to archive all AUDITDATA records from 2010 and prior.

  Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database.

  There is very little tolerance for performance degradation in your environment. You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible. Develop the solution by selecting

  and arranging the required SQL actions in the correct order.

  You may not need all of the actions.

  Select and Place:

  

  

  ---

  Note:

  - Create a new partitioned table with the partition function you want, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.

  -SPLIT RANGE ( boundary_value )

  Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function.

  Based on boundary_value, the Database Engine splits one of the existing ranges into two.

  Of these two, the one where the new boundary_value resides is considered the new partition.

  -BCP can be used top produce the zipped text file.

  - Example: plitting a partition of a partitioned table or index into two partitions

  The following example creates a partition function to partition a table or index into four partitions.

  ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five

  partitions.

  CREATE PARTITION FUNCTION myRangePF1 (int)

  AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );

  GO

  -Split the partition between boundary_values 100 and 1000

  -to create two partitions between boundary_values 100 and 500

  --and between boundary_values 500 and 1000.

  ALTER PARTITION FUNCTION myRangePF1 ()

  SPLIT RANGE (500);

• Question 89:

  You work as a Developer at ABC.com.

  All databases are hosted on Windows Server 2012 servers running SQL Server 2012.

  The company has a database named Products.

  Tables in the Products database contain data including part numbers, product name, color, type and size.

  Users in the Marketing department have created brochures for each product.

  The brochures have been created in the XML Paper Specification (XPS) format.

  You have been asked to add a table to the Products database to store the product brochures.

  The brochures need to be stored in a folder structure.

  Company users will also need to access the brochures from Windows applications using UNC paths. How can you meet these requirements?

  A. By implementing the XMLNAMESPACES feature.
  B. By implementing the FILEGROUP feature.
  C. By implementing the FILETABLE feature.
  D. By implementing the FILESTREAM feature.
  C. By implementing the FILETABLE feature.

• Question 90:

  You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.

  Users need to be able to SELECT from all tables in the database and execute the stored procedure.

  You need to grant only the necessary permissions.

  What should you do?

  A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
  B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
  C. Add all users to the db_owner role.
  D. Grant EXECUTE permission on p_Add!nventory to all users. Add all users to the db_datareader role.
  D. Grant EXECUTE permission on p_Add!nventory to all users. Add all users to the db_datareader role.