Microsoft 70-764 Dumps Questions 2019

70-462 exam preparation are updated and 70-462 exam dumps are verified by experts. Once you have completely prepared with our 70-462 exam preparation you will be ready for the real 70-764 exam without a problem. We have 70-462 questions and answers. PASSED 70-462 questions and answers First attempt! Here What I Did.

Also have 70-764 free dumps questions for you:

NEW QUESTION 1
You are the lead database administrator (DBA) of a Microsoft SQL Server 2016 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group.
You grant DOMAIN\JrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions:
View all databases.
View the server state.
Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role.
You also need to provide the least level of privileges necessary.
Which SQL statement or statements should you use? Choose all that apply.

  • A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
  • B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  • C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  • D. GRANT VIEW DEFINITION TO [SpecialDBARole];
  • E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
  • F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

Answer: BCF

NEW QUESTION 2
You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID column. New rows are inserted into the table every day.
Performance of queries that filter the Product ID column have degraded significantly. You need to improve the performance of the queries.
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.
70-764 dumps exhibit

    Answer:

    Explanation: Step 1: Drop the clustered columnstore index
    Step 2: Create a clustered rowstore index on ProductID.
    Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.
    Step 3: Create a nonclustered index on ProductID

    Topic 2, Exam Pool B

    NEW QUESTION 3
    You plan to install two SQL Server 2012 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:
    Environment1 must have mirroring with manual failover implemented.
    Environment2 must have AlwaysOn with automatic failover implemented.
    You need to identify the minimum number of SQL Server 2012 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 – 2; Environment2 - 2 References:
      http://msdn.microsoft.com/en-us/library/ms189852.aspx
      http://msdn.microsoft.com/en-us/library/hh510230.aspx

      NEW QUESTION 4
      You plan to install a Microsoft SQL Server 2016 instance.
      The instance will support a database that has the following requirements:
      Store Excel workbooks on the file system.
      Access the workbooks through Transact-SQL.
      Include the workbooks in database backups.
      During installation, you need to ensure that the requirements will be met.
      Which feature should you use?

      • A. Excel Services
      • B. FILESTREAM
      • C. SQL Server Integration Services (SSIS)
      • D. OpenXML

      Answer: B

      NEW QUESTION 5
      You are designing a high availability (HA) environment for a company that has three office locations. Details of the services deployed at each office are shown in the table below:
      70-764 dumps exhibit
      You need to maximize availability, minimize data loss, and minimize downtime in the event of a failure. Which solution should you implement for each location? To answer, drag the appropriate solutions to the
      correct locations. Each solution 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: The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.
        References:
        https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-group

        NEW QUESTION 6
        You administer a Microsoft SQL Server 2016 instance that has multiple databases. You have a two-node SQL Server failover cluster.
        The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
        You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?

        • A. Move user databases to a local disk.
        • B. Expand the tempdb data and log files.
        • C. Modify application code to use table variables.
        • D. Move the tempdb files to a local disk.

        Answer: D

        Explanation: You can configure TempDB on a local disk when you, for example, installing your SQL Server cluster. References: https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

        NEW QUESTION 7
        You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2016. You attempt to add the [Corpnet\User1] login to the database.
        However, you receive the following error message: "User already exists in current database."
        You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions.
        You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

        • A. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];
        • B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
        • C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
        • D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];

        Answer: C

        NEW QUESTION 8
        General Overview
        You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.
        Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is currently using. The current application will remain in use while the users are trained to use the new webbased application.
        You need to design the SQL Server and database infrastructure for the web-based application. Databases
        You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting. The Sales database contains a table named OrderTotals and a table named SalesInfo.
        A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.
        The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table.
        The Current_Inventory database contains a large table named Inv_Current. The Inv_Current table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property.
        The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed by multiple queries in the Sales database.
        Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2.
        An external application named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo.
        A stored procedure named SPUPdateGenInfo combines data from multiple databases and generates millions of rows of data in the GenInfo table.
        The GenInfo table is used for reports.
        When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data.
        The GenInfo table is deleted after the reporting process completes. The Products database contains tables named ProductNames and ProductTypes.
        Current System
        The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB.
        This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the Current_Inventory database.
        SQL Servers
        A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.
        Design Requirements
        Your SQL Server infrastructure and database design must meet the following requirements:
        Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.
        Direct access to database tables by developers or applications must be denied.
        The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.
        Deadlocks must be analyzed with the use of Deadlock Graphs.
        In the event of a SQL Server failure, the databases must remain available.
        Software licensing and database storage costs must be minimized.
        Development effort must be minimized.
        The Tempdb databases must be monitored for insufficient free space.
        Failed authentication requests must be logged.
        Every time a new row is added to the ProductTypes table in the Products database, a user defined function that validates the row must be called before the row is added to the table.
        When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.
        You need to enable users to modify data in the database tables using UPDATE operations. You need to implement a solution that meets the design requirements.
        What should you configure?

        • A. You should configure a server role.
        • B. You should configure a database role.
        • C. You should configure functions that use the EXECUTE AS statement.
        • D. You should configure stored procedures that use the EXECUTE AS statement.

        Answer: D

        NEW QUESTION 9
        You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
        You have multiple queries that read data and produce reports from the database.
        You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?

        • A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
        • B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
        • C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
        • D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

        Answer: B

        Explanation: References: https://msdn.microsoft.com/en-us/library/jj542414.aspx

        NEW QUESTION 10
        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.
        70-764 dumps exhibit
        You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
        70-764 dumps exhibit
        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.
        70-764 dumps exhibit
        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.
        70-764 dumps exhibit
        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.
        70-764 dumps exhibit

          Answer:

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

          NEW QUESTION 11
          You use Microsoft SQL Server 2016 to write code for a transaction that contains several statements.
          There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.
          You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

          • A. SERIALIZABLE
          • B. SNAPSHOT
          • C. READ COMMITTED SNAPSHOT
          • D. REPEATABLE READ

          Answer: C

          Explanation: For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:
          It consumes less tempdb space than snapshot isolation. Etc.
          References: https://msdn.microsoft.com/en-us/library/ms188277.aspx

          NEW QUESTION 12
          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 a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.
          You need to ensure that you can perform auditing at the database level for DB1.
          Solution: You migrate DB1 to the default instance on a server that runs Microsoft SQL Server 2016 Standard edition.
          Does the solution meet the goal?

          • A. Yes
          • B. No

          Answer: B

          Explanation: All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
          References:
          https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

          NEW QUESTION 13
          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 support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.
          The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.
          You need to ensure that the query optimizer always uses the query plan that you prefer. Solution: You create a copy of the plan guide for the query plan.
          Does the solution meet the goal?

          • A. Yes
          • B. No

          Answer: B

          Explanation: KEEPFIXED PLAN should be used as it forces the query optimizer not to recompile a query due to changes in statistics.
          References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

          NEW QUESTION 14
          You plan to implement a fault tolerance solution for a Microsoft SQL Server database. The solution must provide failover storage on the local network.
          You need to ensure that the solution can route traffic to failover storage by using SMB 3.0. Which storage option should you use?

          • A. Cluster Shared Volumes
          • B. Microsoft Azure Blob storage
          • C. Always On availability group
          • D. Stretch Database

          Answer: A

          Explanation: Clustered Shared Volumes (CSV) is a new clustered file system in Windows Server that is a layer of abstraction above the NTFS file system in a WSFC environment. It allows all Nodes in the failover cluster to read and write to the CSV volume. CSV leverages the investments Microsoft have made in SMB 3.0, such as SMB Direct and SMB Multichannel.
          SQL Server 2014 was the first version of SQL Server to support CSVs.
          References: https://www.microsoftpressstore.com/articles/article.aspx?p=2832586&seqNum=5

          NEW QUESTION 15
          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. an SHA-256 hash
          • D. an AES 256-bit key

          Answer: D

          Explanation: To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key).
          Example: in order to create an encrypted backup we must create a Database Master Key. USE SampleDB
          GO
          CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MSSQLTips.com'; GO
          We must create a certificate to act as an encryptor. Use SampleDB
          GO
          CREATE CERTIFICATE SampleDB_Backup_Certificate WITH SUBJECT = 'SQL Server 2014';
          GO
          Now we are ready to create encrypted backups.
          References: https://www.mssqltips.com/sqlservertip/3145/sql-server-2014-backup-encryption/

          NEW QUESTION 16
          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 reorganize 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

          Thanks for reading the newest 70-764 exam dumps! We recommend you to try the PREMIUM Surepassexam 70-764 dumps in VCE and PDF here: https://www.surepassexam.com/70-764-exam-dumps.html (427 Q&As Dumps)