Virtual 70-764 Exam Dumps 2019

Exam Code: 70-764 (70-462 questions and answers), Exam Name: Administering a SQL Database Infrastructure (beta), Certification Provider: Microsoft Certifitcation, Free Today! Guaranteed Training- Pass 70-764 Exam.

Check 70-764 free dumps before getting the full version:

NEW QUESTION 1
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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.
You need to optimize query performance. Solution: You rebuild all indexes.
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: A

Explanation: You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. References: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

NEW QUESTION 2
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 attempt to restore a database on a new SQL Server instance and receive the following error message:
70-764 dumps exhibit
You need to ensure that you can restore the database successfully. Solution: You generate a new certificate on the new instance.
Does this meet the goal?

  • A. Yes
  • B. No

Answer: A

Explanation: In order to successfully make the restore in a different server you will need to create a master certificate in the detonation and transfer the certificates and backups in that order.
References:
https://deibymarcos.wordpress.com/2017/11/15/how-to-restore-encrypted-databases-cannot-find-server-certifica

NEW QUESTION 3
You have a database named DB1.
DB1 contains a table named Sales.SalesPerson that has an index named AK_SalesPerson_rowguid. Queries that do not use the index take approximately 10 times longer to complete than queries that use the
index. You discover that AK_SalesPerson_rowguid has severe fragmentation. You need to recommend a solution to defragment the index.
The solution must ensure that the index can be used by queries during the defragmentation.
What statement should you use? 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
70-764 dumps exhibit

    Answer:

    Explanation: Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
    Use: ALTER INDEX REBUILD WITH (ONLINE = ON)
    References: Reorganize and Rebuild Indexes https://msdn.microsoft.com/en-us/library/ms189858.aspx

    NEW QUESTION 4
    You administer a Microsoft SQL Server 2016 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:
    70-764 dumps exhibit
    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 backu
    • C. Then, restore each log backup taken before the time of failure from the most recent differential backup.
    • D. Perform a point-in-time restore.
    • E. Restore the latest full backup.
    • F. Restore the latest full backup, and restore the latest differential backu
    • G. Then, restore the latest log backup.
    • H. Perform a page restore.
    • I. Restore the latest full backu
    • J. Then, restore each differential backup taken before the time of failure from the most recent full backup.
    • K. Restore the latest full backu
    • L. Then, restore the latest differential backup.

    Answer: F

    Explanation: The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as "suspect" because of an error that is encountered when accessing the page.
    Note: 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. Etc.
    References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server

    NEW QUESTION 5
    You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.
    You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. What should you create?

    • A. An Alert
    • B. A Resource Pool
    • C. An Extended Event session
    • D. A Server Audit Specification
    • E. A SQL Profiler Trace
    • F. A Database Audit Specification
    • G. A Policy
    • H. A Data Collector Set

    Answer: F

    NEW QUESTION 6
    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:
    70-764 dumps exhibit
    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:
    70-764 dumps exhibit
    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.
    70-764 dumps exhibit

      Answer:

      Explanation: 70-764 dumps exhibit

      NEW QUESTION 7
      You need to address the Sales Director's requirements regarding the customer classification. You need to recommend a solution for changing the classifications.
      What should you recommend?

      • A. Add each classification change to a new row in the Customers table.
      • B. Record each change to the classification of each customer in a new row in the Customers table.
      • C. Add a new row to the Customers table for each new classification.
      • D. Record each change to the classification of each customer in a new table in the Customers database.

      Answer: D

      NEW QUESTION 8
      You plan to deploy SQL Server 2014.
      You identify the following security requirements for the deployment:
      Users must be prevented from intercepting and reading the T-SQL statements sent from the clients to the database engine.
      All database files and log files must be encrypted if the files are moved to another disk on another server.
      You need to identify which feature meets each security requirement. The solution must minimize processor overhead.
      Which features should you identify? To answer, drag the appropriate feature to the correct requirement in the answer area.
      70-764 dumps exhibit

        Answer:

        Explanation: - Secure Sockets Layer (SSL) encryption enables transmitting encrypted data across the network between an instance of SQL Server and a client application.
        - Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files.

        NEW QUESTION 9
        Overview
        General Overview
        ADatum Corporation has offices in Miami and Montreal.
        The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has 5-ms latency. A. Datum standardizes its database platform by using SQL Server 2014 Enterprise edition.
        Databases
        Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev. Servers and databases are managed by a team of database administrators. Currently, all of the database
        administrators have the same level of permissions on all of the servers and all of the databases.
        The Customers database contains two tables named Customers and Classifications. The following graphic shows the relevant portions of the tables:
        Classifications (Customers)
        70-764 dumps exhibit
        The following table shows the current data in the Classifications table:
        70-764 dumps exhibit
        The Inventory database is updated frequently. The database is often used for reporting.
        A full backup of the database currently takes three hours to complete. Stored Procedures
        A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1.
        After Table1 is created, the reporting process reads data from Table1 sequentially several times. After the process is complete, Table1 is deleted.
        A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products grouped by category.
        USP_2 takes several minutes to run due to locks on the tables the procedure accesses. The locks are caused by USP_1 and USP_3.
        A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction.
        Currently, if one of the UPDATE statements fails, the stored procedure fails. A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases.
        The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
        All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5 calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored procedure.
        You suspect that the security checks are slowing down the performance of USP_5. All stored procedures accessed by user applications call nested stored procedures.
        The nested stored procedures are never called directly. Design Requirements
        Data Recovery
        You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Time Objective (RTO) of 5 minutes.
        You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.
        Classification Changes
        You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future. Management requests that historical data be maintained for the previous classifications. Security A group of junior database administrators must be able to manage security for the Sales database. The junior database administrators will not have any other administrative rights. A. Datum wants to track which users run each stored procedure.
        Storage
        ADatum has limited storage. Whenever possible, all storage space should be minimized for all databases and all backups.
        Error Handling
        There is currently no error handling code in any stored procedure.
        You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never called directly.
        You need to recommend a disaster recovery strategy for the Inventory database. What should you include in the recommendation?

        • A. Log shipping
        • B. SQL Server Failover Clustering
        • C. AlwaysOn availability groups
        • D. Peer-to-peer replication

        Answer: A

        Explanation: Scenario:
        - You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour.
        - A. Datum Corporation has offices in Miami and Montreal.
        - SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

        NEW QUESTION 10
        Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
        You are migrating a set of databases from an existing Microsoft SQL Server instance to a new instance. You need to complete the migration while minimizing administrative effort and downtime.
        Which should you implement?

        • A. log shipping
        • B. an Always On Availability Group with all replicas in synchronous-commit mode
        • C. a file share witness
        • D. a SQL Server failover cluster instance (FCI)
        • E. a Windows Cluster with a shared-nothing architecture
        • F. an Always On Availability Group with secondary replicas in asynchronous-commit mode

        Answer: A

        Explanation: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The
        transaction log backups are applied to each of the secondary databases individually.
        References:
        https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-serv

        NEW QUESTION 11
        You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.
        You need to restore the DB1 database to the point in time of May 25, 2016 at 02:23 and ensure that the database is functional and starts to accept connections.
        Which Transact-SQL statement should you run?
        70-764 dumps exhibit

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

        Answer: B

        NEW QUESTION 12
        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 are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition. The customer environments are shown in the following table.
        70-764 dumps exhibit
        End of repeated scenario.
        You need to configure auditing for the AdventureWorks environment. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
        NOTE: Each correct selection is worth one point.
        70-764 dumps exhibit

          Answer:

          Explanation: Scenario:
          70-764 dumps exhibit
          You must implement auditing for all objects in the ADVSchema schema. Box 1: CREATE SERVER AUDIT
          Create the server audit.
          Box 2: ALTER SERVER AUDIT
          Enable the server audit.
          Box 3: CREATE DATABASE AUDIT
          Create the database audit specification.
          Box 4: FOR SERVER AUDIT
          Example: The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table in the AdventureWorks2012 database.
          USE master ; GO
          -- Create the server audit.
          CREATE SERVER AUDIT Payrole_Security_Audit TO FILE ( FILEPATH =
          'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ; GO
          -- Enable the server audit.
          ALTER SERVER AUDIT Payrole_Security_Audit WITH (STATE = ON) ;
          GO
          -- Move to the target database. USE AdventureWorks2012 ; GO
          -- Create the database audit specification.
          CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
          FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT
          ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ;
          GO
          References:
          https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql?view=sql-s

          NEW QUESTION 13
          You need to provide a group of users from the IT and Manufacturing departments the minimum administrative rights to view database information and server state for the Manufacturing database on MainDB1.
          What should you do?

          • A. You should configure a Database Role.
          • B. You should configure a Server Role.
          • C. You should configure a Shared SQL Server Login.
          • D. You should configure a Local Security Group.

          Answer: B

          NEW QUESTION 14
          You install Microsoft SQL Server 2016 on a new server.
          After setup is complete, you attempt to start the SQL Server service.
          After being in a starting state for a few moments, the service goes back to a stopped state. You need to determine the cause of the failure. Which file should you use?

          • A. %programfiles%\Microsoft SQLServer\MSSQL11.MSSQLSERVER|MSSQL\Log\Errorlog
          • B. %programfiles%\Microsoft SQL Server\110\setupBootstrap\Log\Summary.txt
          • C. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.idf
          • D. %programfiles%\Microsoft SQLServer\110\Shared\ErrorDmpr[XXXX] .mdmp

          Answer: A

          NEW QUESTION 15
          You administer a Microsoft SQL Server 2016 failover cluster.
          You need to ensure that a failover occurs when the server diagnostics returns query_processing error. Which server configuration property should you set?

          • A. SqlOumperDumpFlags
          • B. FailureConditionLevel
          • C. HealthCheckTimeout
          • D. SqlDumperDumpPath

          Answer: B

          Explanation: The SQL Server Database Engine resource DLL determines whether the detected health status is a condition for failure using the FailureConditionLevel property. The FailureConditionLevel property defines which detected health statuses cause restarts or failovers. Multiple levels of options are available, ranging from no automatic restart or failover to all possible failure conditions resulting in an automatic restart or failover.
          References:
          https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-ins

          NEW QUESTION 16
          You plan to install two SQL Server 2014 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:
          Environment1 must have mirroring with automatic failover implemented.
          Environment2 must have Always On with automatic failover implemented.
          You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
          How many servers should you identify? To answer, drag the appropriate number to the correct environment in the answer area.
          70-764 dumps exhibit

            Answer:

            Explanation: Environment1 – 3; Environment2 - 2

            100% Valid and Newest Version 70-764 Questions & Answers shared by Certleader, Get Full Dumps HERE: https://www.certleader.com/70-764-dumps.html (New 427 Q&As)