Printable 70-765 Dumps 2021

for Microsoft certification, Real Success Guaranteed with Updated . 100% PASS 70-765 Provisioning SQL Databases (beta) exam Today!

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

NEW QUESTION 1
You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.
You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers.
You need to achieve these goals without manual intervention. Which configuration should you use?

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

Answer: H

Explanation: Always On availability groups supports two availability modes—asynchronous-commit mode and synchronous-commit mode
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-mode

NEW QUESTION 2
You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)
70-765 dumps exhibit
You write the following Transact-SQL query:
70-765 dumps exhibit
You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

  • A. Use a FORCESCAN hint in the query.
  • B. Add a clustered index on SalesOrderId in SalesOrderHeader.
  • C. Use a FORCESEEK hint in the query.
  • D. Update statistics on SalesOrderId on both tables.

Answer: D

Explanation: New statistics would be useful.
The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
http://msdn.microsoft.com/en-us/library/ms187348.aspx

NEW QUESTION 3
You administer a Microsoft SQL Server 2014.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

  • A. EXEC sp_who 60
  • B. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
  • C. EXEC sp_helpdb 60
  • D. DBCC INPUTBUFFER (60)

Answer: A

Explanation: sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
Example: Displaying a specific process identified by a session ID EXEC sp_who '10' --specifies the process_id;
References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-

NEW QUESTION 4
Note: This questions 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 deploy Microsoft SQL Server to a virtual machine in Azure. You distribute the database files and filegroups across multiple Azure storage disks.
You must be able to manage the databases as individual entities by using SQL Server Management Studio. All data in the databases must be stored encrypted. Backups must be encrypted by using the same key as the live copy of the database.
You need to secure the data. What should you implement?

  • A. transport-level encryption
  • B. cell-level encryption
  • C. Transparent Data Encryption
  • D. Always Encrypted
  • E. Encrypting File System
  • F. BitLocker
  • G. dynamic data masking

Answer: C

Explanation: Transparent data encryption (TDE) encrypts your databases, associated backups, and transaction log files at rest without requiring changes to your applications.
TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. In SQL Database the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each SQL Database server.
References:https://msdn.microsoft.com/en-us/library/dn948096.aspx

Topic 3, Deploy and migrate applications

NEW QUESTION 5
A company runs Microsoft SQL Server 2021 in an on-premises environment. The databases are memory-optimized.
An integrity check of a database has failed.
You need to ensure that the data is healthy and passes an integrity check. What should you do?

  • A. Run the checktable Transact-SQL statement.
  • B. Clear the buffer of the database.
  • C. Restore from a verified backup.
  • D. Run the cleantable Transact-SQL statement.

Answer: C

Explanation: To verify the integrity of the on-disk checkpoint files, perform a backup of the MEMORY_OPTIMIZED_DATA filegroup.

NEW QUESTION 6
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 7
Note: This questions 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.
Your company has several Microsoft Azure SQL Database instances.
Data encryption should be allowed to be implemented by the client applications that access the data. Encryption keys should not be made available to the database engine.
You need to configure the database. What should you implement?

  • A. transport-level encryption
  • B. cell-level encryption
  • C. Transparent Data Encryption
  • D. Always Encrypted
  • E. Encrypting FileSystem
  • F. BitLocker
  • G. dynamic data masking

Answer: A

Explanation: Using encryption during transit with Azure File Shares
Azure File Storage supports HTTPS when using the REST API, but is more commonly used as an SMB file share attached to a VM.
HTTPS is a transport-level security protocol.

NEW QUESTION 8
You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?

  • A. Processor affinity
  • B. Lightweight pooling
  • C. Max worker threads
  • D. I/O affinity

Answer: D

Explanation: The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References:
http://msdn.microsoft.com/en-us/library/ms189629.aspx

NEW QUESTION 9
You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
70-765 dumps exhibit
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
70-765 dumps exhibit
Which Transact-SQL query should you use?

  • A. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW
  • B. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
  • C. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO
  • D. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS
  • E. SELECT Name, Country, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO
  • F. SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
  • G. SELECT Name AS `@Name', Country AS `@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH (`Customers')
  • H. SELECT Name AS `Customers/Name', CountryAS `Customers/Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers'

Answer: G

NEW QUESTION 10
You plan to migrate a Microsoft SQL server instance between physical servers.
You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the msdb database.
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: A

Explanation: The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2021

NEW QUESTION 11
You administer two Microsoft SQL Server 2014 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers. What should you do?

  • A. Configure the SQL Server service account to use Network Service.
  • B. Use a server certificate.
  • C. Use a database certificate.
  • D. Configure the SQL Server service account to use Local System.

Answer: B

Explanation: To enable certificate authentication for database mirroring on a given server instance, the system administrator must configure each server instance to use certificates on both outbound and inbound connections.
References:
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirrorin

NEW QUESTION 12
You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.
70-765 dumps exhibit
You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time. What should you do?

  • A. Perform a file restore.
  • B. Perform a transaction log restore.
  • C. Perform a restore from a full backup.
  • D. Perform a filegroup restore.

Answer: A

Explanation: In a file restore, the goal is to restore one or more damaged files without restoring the whole database. References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-restores-simple-recovery-model

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.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You use Visual Studio to create a JSON template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: A

Explanation: Azure Resource Manager template consists of JSON, not XAML, and expressions that you can use to construct values for your deployment.
A good JSON editor can simplify the task of creating templates.
Note: In its simplest structure, an Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01- 01/deploymentTemplate.json#",
"contentVersion": "", "parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References:https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring-templates

NEW QUESTION 14
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine. You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?

  • A. Manually specify the column master key.
  • B. Modify the connection string for applications.
  • C. Auto-generate a column master key.
  • D. Use theWindows certificate store.

Answer: D

Explanation: Always Encrypted supports multiple key stores for storing Always Encrypted column master keys. A column master key can be a certificate stored in Windows Certificate Store.
References:https://msdn.microsoft.com/en-us/library/mt723359.aspx

NEW QUESTION 15
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases. Which powershell cmdlet should you run?

  • A. New-AzureSqlJob
  • B. New-AzureWebsiteJob
  • C. New-AzureBatchJob
  • D. New-ScheduledJobOption
  • E. New-JobTrigger

Answer: A

Explanation: The New-AzureSqlJob cmdlet, in the ElasticDatabaseJobs module, creates a job definition to be used for subsequent job runs.
References:
https://docs.microsoft.com/en-us/powershell/module/elasticdatabasejobs/new-azuresqljob?view=azureelasticdbj

NEW QUESTION 16
You plan to enable Stretch Database for a table named Table1.
You need to configure Table1 to move the data to Microsoft Azure. The data must be moved at a later time. How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value 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.
NOTE: Each correct selection is worth one point.
70-765 dumps exhibit

    Answer:

    Explanation: When you enable Stretch for a table by specifying ON, you also have to specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration.
    Syntax:
    <stretch_configuration> ::=
    { SET (
    REMOTE_DATA_ARCHIVE
    {
    = ON ( <table_stretch_options> )
    | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
    | ( <table_stretch_options> [, ...n] )
    }
    )
    }
    References:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2021

    NEW QUESTION 17
    You administer a Microsoft SQL Server 2014 database.
    You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
    You need to ensure that the job step can run using a different user account. What should you use?

    • A. a schedule
    • B. an alert
    • C. an operator
    • D. a proxy

    Answer: D

    Explanation: A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
    References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

    NEW QUESTION 18
    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.
    On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.
    You need to perform a full database backup that will be restored on the development server. Which backup option should you use?

    • 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: L

    Explanation: COPY_ONLY specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.
    References:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

    NEW QUESTION 19
    You plan to migrate a database To Microsoft Azure SQL Database. The database requires 500 gigabytes (GB) of storage.
    The database must support 50 concurrent logins. You must minimize the cost associated with hosting the database.
    You need to create the database. Which pricing tier should you use?

    • A. Standard S3 pricing tier
    • B. Premium P2tier
    • C. Standard S2 pricing tier
    • D. Premium P1 tier

    Answer: D

    Explanation: For a database size of 500 GB the Premium tier is required. Both P1 and P2 are adequate. P1 is preferred as it is cheaper.
    Note:
    70-765 dumps exhibit

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