1. Home
  2. Microsoft
  3. 70-764

Microsoft 70-764 Online Practice Questions and Exam Preparation

70-764 Exam Details

  • Exam Code
    :70-764
  • Exam Name
    :Administering a SQL Database Infrastructure
  • Certification
    :Microsoft Certifications
  • Vendor
    :Microsoft
  • Total Questions
    :452 Q&As
  • Last Updated
    :Feb 07, 2022

Microsoft 70-764 Online Questions & Answers

  • Question 341:

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

  • Question 342:

    DRAG DROP

    Overview

    Application Overview

    Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.

    Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.

    The new version will use SQL Server 2014.

    The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.

    You receive several support calls reporting unexpected behavior in the ERP application.

    After analyzing the calls, you conclude that users made changes directly to the tables in the database.

    Tables

    The current database schema contains a table named OrderDetails.

    The OrderDetails table contains information about the items sold for each purchase order.

    OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.

    The product price is stored in a table named Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.

    A column named ProductName was created by using the varchar data type. The database contains a table named Orders.

    Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.

    The previous version of the ERP application relied on table-level security.

    Stored Procedures

    The current version of the database contains stored procedures that change two tables.

    The following shows the relevant portions of the two stored procedures:

    Customer Problems Installation Issues The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times. Index Fragmentation Issues Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:

    Backup Issues

    Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

    Search Issues

    Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

    Missing Data Issues

    Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

    Query Performance Issues

    Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks.

    Import Issues During the monthly import process, database administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required

    to import the data.

    Design Requirements

    File Storage Requirements

    The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

    Data Recovery Requirements

    If the import process fails, the database must be returned to its prior state immediately.

    Security Requirements

    You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

    Concurrency Requirements

    You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.

    You need to recommend which statement should be used to update SalesOrder.

    How should you recommend completing the statement? To answer, drag the appropriate elements to the correct locations. Each element 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 343:

    A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups.

    DB1 experiences a failure. You must restore FG1 and then FG2.

    You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.

    What should you specify when you run the recovery command?

    A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
    B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
    C. the WITH RECOVERY clause for both FG1 and FG2
    D. the WITH NORECOVERY clause for both FG1 and FG2

  • Question 344:

    You administer a Microsoft SQL Server 2016 server.

    You plan to deploy new features to an application.

    You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.

    What should you do?

    A. Query the sys.dm_db_index_usage_stats DMV.
    B. Query the sys.dm_db_missing_index_details DMV.
    C. Use the Database Engine Tuning Advisor.
    D. Query the sys.dm_db_missing_index_columns DMV.

  • Question 345:

    You have multiple Microsoft SQL Server databases that are deployed in an Always On availability group. You configure the SQL Server Agent service to start automatically.

    You need to automate backups for all user databases.

    What should you create?

    A. SQL script
    B. SQL Agent operator
    C. SQL Server message
    D. SQL Agent job

  • Question 346:

    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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

    You have a database named DB1 that contains a table named Table1.

    You need to audit all updates to Table1.

    Solution: From Local Group Policy Editor, you configure auditing for object access.

    Does this meet the goal?

    A. Yes
    B. No

  • Question 347:

    DRAG DROP

    You are configuring a new Microsoft SQL Server Always On Availability Group. You plan to configure a shared network location at \\DATA-CI1\SQL.

    You need to create an availability group listener named AGL1 on port 1433.

    In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

    Select and Place:

  • Question 348:

    You work as a Database Administrator (DBA) for a company named ABC.com.

    The company uses a Microsoft SQL Server 2012 infrastructure.

    You are configuring a highly-available database solution using an AlwaysOn availability group on two servers running SQL Server 2012.

    The two servers are in separate datacenters.

    The two datacenters are connected by a fast WAN link with a network latency of less than 10ms.

    Which of the following failover types should you configure for the availability group?

    A. You should configure the asynchronous manual failover failover type.
    B. You should configure the synchronous manual failover failover type.
    C. You should configure the synchronous automatic failover failover type.
    D. You should configure the Asynchronous automatic failover failover type.

  • Question 349:

    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.

    Start of repeated scenario.

    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.

    End of repeated scenario.

    You need to reduce the amount of time it takes to back up OperationsMain.

    What should you do?

    A. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
    B. Modify the backup script to use the keywords INIT and SKIP in the WITH statement.
    C. Run the following Transact-SQL statement for each file in OperationsMain: BACKUP DATABASE OperationsMain FILE [...]
    D. Run the following Transact-SQL statement: BACKUP DATABASE OperationsMain READ_WRITE_FILEGROUPS

  • Question 350:

    You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.

    You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

    What should you create?

    A. an Extended Event session
    B. a Policy
    C. a Database Audit Specification
    D. an Alert
    E. a Server Audit Specification
    F. a SQL Profiler Trace
    G. a Resource Pool

Related Exams:

Tips on How to Prepare for the Exams

Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only Microsoft exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your 70-764 exam preparations and Microsoft certification application, do not hesitate to visit our Vcedump.com to find your solutions here.