Microsoft 70-765 Free Practice Questions 2021

We provide in two formats. Download PDF & Practice Tests. Pass Microsoft 70-765 Exam quickly & easily. The 70-765 PDF type is available for reading and printing. You can print more and practice many times. With the help of our product and material, you can easily pass the 70-765 exam.

Also have 70-765 free dumps questions for you:

NEW QUESTION 1
You have a Microsoft SQL Server that has a database named DB1. DB1 has a data files on drive E and transaction logs on drive L.
Drive L fails and is replaced.
You need to recover DB1. The solution must minimize data loss.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.
70-765 dumps exhibit

    Answer:

    Explanation: ALTER DATABASE '<your_database>' SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
    DBCC CHECKDB ('<your_database>', REPAIR_REBUILD) GO
    ALTER DATABASE '<your_database>' SET MULTI_USER GO

    NEW QUESTION 2
    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 have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
    You need to recommend a storage solution for the SQL Servers. The solution must minimize costs. Which storage option should you use?

    • A. Premium P10 disk storage
    • B. Premium P20 disk storage
    • C. Premium P30 disk storage
    • D. Standard locally redundant disk storage
    • E. Standard geo-redundant disk storage
    • F. Standard zone redundant blob storage
    • G. Standard locally redundant blob storage
    • H. Standard geo-redundant blob storage

    Answer: A

    Explanation: P10 has 500 IOPS per disk, which provides for more than 1 million IOPS per month.
    Note: 3600*30* 500 is 54 million IOPS/month.
    References: https://azure.microsoft.com/en-us/pricing/details/managed-disks/

    NEW QUESTION 3
    You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
    A network administrator upgrades the hardware firewalls on the network. You need to verify whether data migration still runs successfully.
    Which stored procedure should you run?

    • A. Sys_sp_testlinkedserver
    • B. Sys_sp_rda_test_connection
    • C. Sys_sp_rda_reauthorized_db
    • D. Sp_set_firewall_rule

    Answer: B

    Explanation: The Sys_sp_rda_test_connection cmdlet tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.
    References:
    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-rda-test-connection-tr

    NEW QUESTION 4
    You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
    The financial database has the following characteristics:
    The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
    The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
    These data load operations must occur in the minimum amount of time.
    A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
    You need to ensure that the backup size is as small as possible. Which backup should you perform every two hours?

    • A. NORECOVERY
    • B. FULL
    • C. NO_CHECKSUM
    • D. CHECKSUM
    • E. Differential
    • F. BULK_LOGGED
    • G. STANDBY
    • H. RESTART
    • I. SKIP
    • J. Transaction log
    • K. DBO ONLY
    • L. COPY_ONLY
    • M. SIMPLE
    • N. CONTINUE AFTER ERROR

    Answer: J

    Explanation: Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.
    References:
    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql-server

    NEW QUESTION 5
    You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a
    schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
    UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
    You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
    Which Transact-SQL statement should you use?

    • A. REVOKE SELECT ON Schema::Customers FROM UserA
    • B. DENY SELECT ON Object::Regions FROM UserA
    • C. EXEC sp_addrolemember 'Sales', 'UserA'
    • D. DENY SELECT ON Object::Regions FROM Sales
    • E. REVOKE SELECT ON Object::Regions FROM UserA
    • F. DENY SELECT ON Schema::Customers FROM Sales
    • G. DENY SELECT ON Schema::Customers FROM UserA
    • H. EXEC sp_droprolemember 'Sales', 'UserA'
    • I. REVOKE SELECT ON Object::Regions FROM Sales
    • J. REVOKE SELECT ON Schema::Customers FROM Sales

    Answer: F

    Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
    References:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-

    NEW QUESTION 6
    You administer a Microsoft SQL Server 2021 instance.
    You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

    • A. Disable FILESTREAM on the Database.
    • B. Enable FILESTREAM on the Server Instance.
    • C. Configure the Database for Partial Containment.
    • D. Create a non-empty FILESTREAM file group.
    • E. Enable Contained Databases on the Server Instance.
    • F. Set the FILESTREAM directory name on the Database.

    Answer: BDF

    Explanation: References:
    https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable

    NEW QUESTION 7
    You administer a Microsoft SQL Server 2014 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: Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.
    The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.
    Note: The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread.
    The system, resource, and query process components are used for failure detection. The io_subsytem and events components are used for diagnostic purposes only.
    References:https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure-failurecondition

    NEW QUESTION 8
    A company uses several Microsoft Azure elastic pools with Azure SQL Database instances.
    You have two pools named Pool1 and Pool2. Pool2 is near maximum capacity and cannot accommodate the database move.
    You need to move the database from Pool1 to Pool2.
    Which PowerShell cmdlets should you run? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
    70-765 dumps exhibit

      Answer:

      Explanation: References:
      https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlelasticpool?view=azurermps-6.1 https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabase?view=azurermps-6.12.

      NEW QUESTION 9
      Settings Value VM size D3
      Storage Location Drive E Storage type Standard Tempdb location Drive C
      The workload on this instance has of the tembdb load.
      You need to maximize the performance of the tempdb database.
      Solution: You use a D- Series VM and store the tempdb database on drive D. Does this meet the goal?

      • A. Yes
      • B. No

      Answer: A

      Explanation: For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
      References:
      https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

      NEW QUESTION 10
      You administer a Microsoft SQL Server 2014 database.
      The database contains a Product table created by using the following definition:
      70-765 dumps exhibit
      You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

      • A. Convert all indexes to Column Store indexes.
      • B. Implement Unicode Compression.
      • C. Implement row-level compression.
      • D. Implement page-level compression.

      Answer: D

      NEW QUESTION 11
      You have a database named DB1 that uses simple recovery mode.
      Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup. There was no corruption when the last backup was complete.
      You run the sys.columns catalog view and discover corrupt pages.
      You need to recover the database. The solution must minimize data loss. What should you do?

      • A. Run RESTORE DATABASE WITH RECOVERY.
      • B. Run RESTORE DATABASE WITH PAGE.
      • C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
      • D. Run DBCC CHECKDB and specify the REPAIT_REBUILD parameter.

      Answer: B

      Explanation: A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
      RESTORE DATABASE WITH PAGE
      Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

      NEW QUESTION 12
      Your company has many Microsoft SQL Server instances hosted in a data center. You also manage five Microsoft Azure SQL Database instances that are hosted on a single server in Azure.
      You need to minimize costs associated with Azure resources while maintaining the current performance levels of each Azure SQL Database instance.
      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-765 dumps exhibit

        Answer:

        Explanation: SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price.
        References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

        NEW QUESTION 13
        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 user
        • B. Grant VIEW DEFINITION to all users.
        • C. Grant EXECUTE permission on p_AddInventory to all user
        • D. Add all users to the db_datawriter role.
        • E. Add all users to the db_owner role.
        • F. Grant EXECUTE permission on p_AddInventory to all user
        • G. Add all users to the db_datareader role.

        Answer: D

        Explanation: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
        References:https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

        NEW QUESTION 14
        Background
        You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
        The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
        The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
        You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
        After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
        You review the properties of the instance (Click the Exhibit button). Exhibit:
        70-765 dumps exhibit
        You need to set the size of the log files for the tempdb database on SRV1.
        How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
        Hot Area:
        70-765 dumps exhibit

          Answer:

          Explanation: The ALTER DATABASE with MODIFY FILE command can make a file size bigger (but not smaller). Example:
          ALTER DATABASE AdventureWorks2012 MODIFY FILE
          (NAME = test1dat3, SIZE = 200MB); Note: MODIFY FILE
          Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
          References:
          https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-a

          NEW QUESTION 15
          You administer a Microsoft SQL Server 2014 database instance.
          You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
          You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
          What should you do?

          • A. Use the copy database wizard
          • B. Use the Database Transfer wizard
          • C. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database
          • D. Backup the database from the local server and restore it to Windows Azure SQL Database

          Answer: C

          Explanation: You would need to use either the SQL Server Management Studio or Transact-SQL.
          References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate

          NEW QUESTION 16
          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 have deployed a GS-series virtual machine (VM) in Microsoft Azure. You plan to deploy Microsoft SQL Server.
          You need to deploy a 30 megabyte (MB) database that requires 100 IOPS to be guaranteed while minimizing costs.
          Which storage option should you use?

          • A. Premium P10 disk storage
          • B. Premium P20 disk storage
          • C. Premium P30 disk storage
          • D. Standard locally redundant disk storage
          • E. Standard geo-redundant disk storage
          • F. Standard zone redundant blob storage
          • G. Standard locally redundant blob storage
          • H. Standard geo-redundant blob storage

          Answer: A

          Explanation: Premium Storage Disks Limits
          When you provision a disk against a Premium Storage account, how much input/output operations per second (IOPS) and throughput (bandwidth) it can get depends on the size of the disk. Currently, there are three types of Premium Storage disks: P10, P20, and P30. Each one has specific limits for IOPS and throughput as specified in the following table:
          70-765 dumps exhibit
          References:https://docs.microsoft.com/en-us/azure/storage/storage-premium-storage

          NEW QUESTION 17
          Settings Value VM size D3
          Storage Location Drive E Storage type Standard Tempdb location Drive C
          The workload on this instance has of the tembdb load.
          You need to maximize the performance of the tempdb database.
          Solution: You use an AB compute-intensive instance and store the tempdb database in Standard storage. Does this meet the goal?

          • A. Yes
          • B. No

          Answer: B

          Explanation: For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
          References:
          https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

          NEW QUESTION 18
          You administer a Microsoft SQL Server 2014 database. You want to make a full backup of the database to a file on disk.
          In doing so, you need to output the progress of the backup. Which backup option should you use?

          • A. STATS
          • B. COMPRESSION
          • C. CHECKSUM
          • D. IN IT

          Answer: A

          Explanation: STATS is a monitoring option of the BACKUP command. STATS [ =percentage ]
          Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
          The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
          References: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

          NEW QUESTION 19
          You administer a Microsoft SQL Server 2014 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.

          Answer: BCDE

          Explanation: The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time
          recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.
          References:https://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx

          Recommend!! Get the Full 70-765 dumps in VCE and PDF From Certleader, Welcome to Download: https://www.certleader.com/70-765-dumps.html (New 209 Q&As Version)