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

    You have a customer who has several SQL Server 2012 database servers. You are designing a data warehouse for the customer. The data warehouse will use columnstore indexes.

    The customer identifies that the following must be supported for the column store indexes. Data manipulation language (DML) statements Nonclustered columnstore indexes Clustered columnstore indexes - Partitioning

    You need to identify which technology requires the customer to implement an SQL Server 2014 database. What should you identify?

    A. clustered columnstore indexes
    B. nonclustered columnstore indexes
    C. data manipulation language (DML) statements
    D. partitioning

  • Question 192:

    Overview

    You are a database administrator for a company named Litware, Inc.

    Litware is a book publishing house. Litware has a main office and a branch office.

    You are designing the database infrastructure to support a new web-based application that is being developed.

    The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

    You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

    App1_DB will remain in production.

    Requirements

    Planned Changes

    You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

    All database files will be stored in a highly available SAN.

    Database1 will contain two tables named Orders and OrderDetails.

    Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

    The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. The rows returned from the first query must be returned on the second query unchanged

    along with any rows added to the table between the two read operations.

    Database1 will contain several queries that access data in the Database2 tables.

    Database2 will contain a table named Inventory.

    Inventory will contain over 100 GB of data.

    The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

    The column that is used as the primary key will use the identity property.

    Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does

    not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes

    occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

    Business Requirements

    You have the following requirements:

    Costs for new licenses must be minimized.

    Private information that is accessed by Application must be stored in a secure format.

    Development effort must be minimized whenever possible.

    The storage requirements for databases must be minimized.

    System administrators must be able to run real-time reports on disk usage.

    The databases must be available if the SQL Server service fails.

    Database administrators must receive a detailed report that contains allocation errors and data corruption.

    Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

    You must encrypt the backup files to meet regulatory compliance requirements.

    The encryption strategy must minimize changes to the databases and to the applications.

    You need to recommend a database reporting solution that meets the business requirements.

    What should you include in the recommendation?

    A. Data collection
    B. Performance Monitor
    C. A maintenance plan
    D. A dynamic management view

  • Question 193:

    Overview

    You are a database administrator for a company named Litware, Inc.

    Litware is a book publishing house. Litware has a main office and a branch office.

    You are designing the database infrastructure to support a new web-based application that is being developed.

    The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

    You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

    App1_DB will remain in production.

    Requirements

    Planned Changes

    You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

    All database files will be stored in a highly available SAN.

    Database1 will contain two tables named Orders and OrderDetails.

    Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

    The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. The rows returned from the first query must be returned on the second query unchanged

    along with any rows added to the table between the two read operations.

    Database1 will contain several queries that access data in the Database2 tables.

    Database2 will contain a table named Inventory.

    Inventory will contain over 100 GB of data.

    The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

    The column that is used as the primary key will use the identity property.

    Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does

    not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes

    occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

    Business Requirements

    You have the following requirements:

    Costs for new licenses must be minimized.

    Private information that is accessed by Application must be stored in a secure format.

    Development effort must be minimized whenever possible.

    The storage requirements for databases must be minimized.

    System administrators must be able to run real-time reports on disk usage.

    The databases must be available if the SQL Server service fails.

    Database administrators must receive a detailed report that contains allocation errors and data corruption.

    Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

    You must encrypt the backup files to meet regulatory compliance requirements.

    The encryption strategy must minimize changes to the databases and to the applications.

    During performance testing, you discover that database INSERT operations against the Inventory table are slow.

    You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. What should you recommend?

    A. Partition the nonclustered index.
    B. Partition the Inventory table.snapshot replication
    C. Create a column store index.Master Data Services
    D. Drop the clustered index.change data capture

  • Question 194:

    HOTSPOT

    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 maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

    The backup strategies for each database are described in the following table.

    Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

    SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages: database name impacted file id impacted file physical name impacted page id event type that identifies the error type error count Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements: Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB). Capture queries based on resource consumption. Use a stale query threshold value of 60 days. The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table. Both SalesDb1 and SalesDb2 experience blocking and deadlock issues. You create an Extended Events session to monitor the databases. (Click the Exhibit button.) You need to create and configure the Extended Events session. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

    Hot Area:

  • Question 195:

    DRAG DROP

    You plan to deploy three highly available SQL Server environments that will use SQL Server 2014. You identify the following specifications for each environment as shown following table.

    You need to recommend which high-availability feature is required for each environment.

    Which features should you identify? To answer, drag the appropriate feature to the correct requirement in the answer area.

    Select and Place:

  • Question 196:

    You are a database administrator for an organization. You must allow the members of the human resources department to add new employee records to the HR.Employees table. All members of the human resources department are

    assigned the HumanResources database only.

    You need to ensure that members of the human resources department can add records to the table.

    Which Transact-SQL statement should you run?

    A. GRANT INSERT ON HR.Employees to HumanResources
    B. GRANT UPDATE ON HR.Employees to HumanResources
    C. GRANT REFERENCES ON SCHEMA :: HR to HumanResources
    D. GRANT ALTER ON HR.Employees to HumanResources

  • Question 197:

    You administer a Microsoft SQL Server 2016 instance. The instance contains a database that supports a retail sales application.

    The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database.

    You need to ensure that the following requirements are met:

    No more than 5 minutes worth of transactions are lost.

    Data can be recovered by using the minimum amount of administrative effort.

    What should you do? Choose all that apply.

    A. Configure the database to use the SIMPLE recovery model.
    B. Create a DIFFERENTIAL database backup every 4 hours.
    C. Create a LOG backup every 5 minutes.
    D. Configure the database to use the FULL recovery model.
    E. Create a FULL database backup every 24 hours.
    F. Create a DIFFERENTIAL database backup every 24 hours.

  • Question 198:

    You administer all the deployments of Microsoft SQL Server 2016 in your company.

    You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.

    You also need to minimize the amount of storage used by the database.

    Which configuration should you use?

    A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
    B. SQL Server that includes an application database configured to perform transactional replication
    C. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
    D. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
    E. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
    F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
    G. SQL Server that includes an application database configured to perform snapshot replication
    H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

  • Question 199:

    You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.

    You need to configure the encryption options for backups.

    What should you configure?

    A. a certificate
    B. an MD5 hash
    C. a DES key
    D. an AES 256-bit key

  • Question 200:

    HOTSPOT

    You manage a Microsoft-SQL Server database named sales Orders.

    You need to verify the integrity of the database and attempt to repair any errors that are found. Repair must not cause any data to be lost in the database.

    How should you complete the DBCC command? To answer, select the appropriate options in the answer area.

    Hot Area:

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.