Microsoft 70-765 Exam Questions 2019

Exam Code: 70-765 (), Exam Name: Provisioning SQL Databases (beta), Certification Provider: Microsoft Certifitcation, Free Today! Guaranteed Training- Pass 70-765 Exam.

Free demo questions for Microsoft 70-765 Exam Dumps Below:

NEW QUESTION 1
Database DB1 must use two CPU cores.
Queries that were running on database DB2 prior to migration do not complete. You need to configure the databases.
In the table below, identify the parameter that must be configured for each databases. Select one option for DB1, and one option for DB2. Select one option for each column.
70-765 dumps exhibit

    Answer:

    Explanation: Query_optimizer_hotfixes DB1: MAXDOP
    You can use the max degree of parallelism (MAXDOP) option to limit the number of processors to use in parallel plan execution.
    DB2: LEGACY_CARDINALITY_ESTIMATION
    The CE (Cardinality Estimation) predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.
    Legacy CE: For a SQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the ALTER DATABASE SCOPED CONFIGURATION.
    Example:
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO

    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. Determine whether the solution meets stated goals.
    You manage a Microsoft SQL Server environment with several databases.
    You need to ensure that queries use statistical data and do not initialize values for local variables.
    Solution: You enable the PARAMETER_SNIFFING option for the databases. Does the solution meet the goal?

    • A. Yes
    • B. No

    Answer: A

    Explanation: PARAMETER_SNIFFING = { ON | OFF | PRIMARY} enables or disables parameter sniffing. This is equivalent to Trace Flag 4136.
    SQL server uses a process called parameter sniffing when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.
    References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

    NEW QUESTION 3
    You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
    You need to write messages to the Application Log when users are added to or removed from a fixed server
    role in Server01.
    What should you create?

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

    Answer: G

    Explanation: The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events.
    Audits can have the following categories of actions:
    Server-level. These actions include server operations, such as management changes, such as in this question, and logon and logoff operations.
    Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.
    Audit-level. These actions include actions in the auditing process.
    References:
    http://technet.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

    NEW QUESTION 4
    You administer a single server that contains a Microsoft SQL Server 2014 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
    You need to ensure that the application login is unable to access other production databases. What should you do?

    • A. Use the SQL Server default instance and configure an affinity mask.
    • B. Install a new named SQL Server instance on the server.
    • C. Use the SQL Server default instance and enable Contained Databases.
    • D. Install a new default SQL Server instance on the server.

    Answer: B

    Explanation: References:
    https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server

    NEW QUESTION 5
    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 6
    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 7
    You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01.
    What should you create?

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

    Answer: C

    Explanation: SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don't refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.
    70-765 dumps exhibit
    References: https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/

    NEW QUESTION 8
    HOTSPOT
    You need to optimize SRV1.
    What configuration changes should you implement? To answer, select the appropriate option from each list in the answer area.
    70-765 dumps exhibit

      Answer:

      Explanation: From the scenario: SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application hasapproximately 30,000 concurrent users and relies heavily on the use of temporary tables.
      Box 1: Change the size of the tempdb log file.
      The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system- processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.
      Box 2: Add additional tempdb files.
      Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary.

      Topic 5, Contoso, Ltd Case Study 2Background
      You are the database administrator for Contoso, Ltd. The company has 200 offices around the world. The company has corporate executives that are located in offices in London, New York, Toronto, Sydney, and Tokyo.
      Contoso, Ltd. has a Microsoft Azure SQL Database environment. You plan to deploy a new
      Azure SQL Database to support a variety of mobile applications and public websites.
      The company is deploying a multi-tenant environment. The environment will host Azure SQL Database instances. The company plans to make the instances available to internal departments and partner companies. Contoso is in the final stages of setting up networking and communications for the environment.
      Existing Contoso and Customer instances need to be migrated to Azure virtual machines (VM) according to the following requirements:
      The company plans to deploy a new order entry application and a new business intelligence and analysis application. Each application will be supported by a new database. Contoso creates a new Azure SQL database named Reporting. The database will be used to support the company's financial reporting requirements. You associate the database with the Contoso Azure Active Directory domain.
      Each location database for the data entry application may have an unpredictable amount of activity. Data must be replicated to secondary databases in Azure datacenters in different regions.
      To support the application, you need to create a database named contosodb1 in the existing environment.
      Objects
      70-765 dumps exhibit
      Database
      The contosodb1 database must support the following requirements:
      Application
      For the business intelligence application, corporate executives must be able to view all data in near real-time with low network latency.
      Contoso has the following security, networking, and communications requirements:

      NEW QUESTION 9
      You administer a Microsoft SQL Server 2014 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
      An additional node named Node C has been added to the existing cluster.
      You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

      • A. Run the New SQL Server stand-alone installation Wizard on Node C.
      • B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
      • C. Use Node B to install SQL Server on Node C.
      • D. Use Node A to install SQL Server on Node C.

      Answer: B

      Explanation: To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.
      The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.
      References:
      http://technet.microsoft.com/en-us/library/ms191545.aspx

      NEW QUESTION 10
      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 have Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1. You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
      You need to reduce the WRITE_LOG wait time. Solution: Add additional data files to DB1. Does this meet the goal?

      • A. Yes
      • B. No

      Answer: B

      Explanation: In SQL Server, if we have a transactional based system and find a high WRITELOG wait type this is a performance bottleneck and can cause the transaction log file to grow rapidly and frequently.
      It is being recommended to SQL server users that they must archive the log files on a separate disk for getting better performance.
      References: https://atdhebuja.wordpress.com/2016/06/20/resolving-sql-server-transaction-log-waits/

      NEW QUESTION 11
      You plan to install a Microsoft SQL Server 2014 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

      Explanation: FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
      References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

      NEW QUESTION 12
      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 restore the Reporting database to SRV2. What should you do? To answer, drag the appropriate options to the correct locations. Each option 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:
      70-765 dumps exhibit

        Answer:

        Explanation: Step 2: Create: server certificate
        Recreate the server certificate by using the original server certificate backup file.
        Note: The password must be the same as the password that was used when the backup was created. Step 3: Restore: Reporting database .mdf file.
        -- Attach the database that is being moved.
        -- The path of the database files must be the location where you have stored the database files. Example:
        CREATE DATABASE [CustRecords] ON
        ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA
        \CustRecords.mdf' ),
        ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA
        \CustRecords_log.LDF' ) FOR ATTACH ;
        GO
        From scenario: 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.
        References:
        https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-a

        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. Determine whether the solution meets stated goals.
        You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
        Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
        You need to improve performance of the application while still allowing the report queries to finish.
        Solution: You configure the Resource Governor to set the MAXDOP parameter to 0 for all queries against the database.
        Does the solution meet the goal?

        • A. Yes
        • B. No

        Answer: B

        Explanation: SQL Server will consider parallel execution plans for queries, index data definition language (DDL) operations, and static and keyset-driven cursor population.
        You can override the max degree of parallelism value in queries by specifying the MAXDOP query hint in the query statement.
        References: https://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx

        NEW QUESTION 14
        Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
        Which Transact-SQL query should you use?

        • A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
        • B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
        • C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112)=CONVERT(VARCHAR, GETDATE(), 112)
        • D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

        Answer: D

        Explanation: To compare a time with date we must use >= and > operators, and not the = operator.

        NEW QUESTION 15
        You are the database administrator for your company. Your company has one main office and two branch offices. You plan to create three databases named DB1, DB2, and DB3 that will be hosted on one Azure SQL Database server. You have the following requirements:
        The main office must be able to connect to all three databases.
        The branch offices must be able to connect to DB2 and DB3.
        The branch offices must not be able to access DB1.
        You need to configure transparent data encryption (TDE) for DB1. Which two actions should you perform? Each correct answer presents part of the solution.

        • A. Run CREATE CERTIFICATE certl WITH Subject = TDE Cert1 on DB1.
        • B. Connect to DB1.
        • C. Run ALTER DATABASE DB1 SET ENCRYPTION ON;.
        • D. Connect to the master database.
        • E. Run CREATE MASTER KEY on the master database.

        Answer: BC

        Explanation: You should connect to DB1. To encrypt DB1, you connect directly to DB1. When you connect to DB1. You use your dbmanager or administrative credentials.
        You should run ALTER DATABASE DB1 SET ENCRYPTION ON.
        You use the ALTER DATABASE DB1 SET ENCRYPTION ON statement to encrypt the database. This is the statement that turns on TDE for Azure SQL Database.

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

        P.S. Easily pass 70-765 Exam with 209 Q&As 2passeasy Dumps & pdf Version, Welcome to Download the Newest 2passeasy 70-765 Dumps: https://www.2passeasy.com/dumps/70-765/ (209 New Questions)