Microsoft 70-765 Dumps Questions 2021

NEW QUESTION 1
You administer a Microsoft SQL Server 2014 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?

• A. Perform a differential backup at 08:59 hours.
• B. Record the LSN of the transaction log at 08:59 hour
• C. Perform a transaction log backup at17:01 hours.
• D. Create a database snapshot at 08:59 hours.
• E. Record the LSN of the transaction log at 08:59 hour
• F. Perform a transaction log backup at08:59 hours.
• G. Create a marked transaction in the transaction log at 08:59 hour
• H. Perform a transaction log backup at 17:01 hours.
• I. Create a marked transaction in the transaction log at 08:59 hour
• J. Perform a transaction log backup at 08:59 hours.

Answer: C

Explanation: References:https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

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 are tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores.
The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.
Several reports use the PIVOT statement and access more than 100 million rows in table1.
You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database.
You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more files to db1.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: From SQL Server’s perspective, you can measure the I/O latency from sys.dm_os_wait_stats. If you consistently see high waiting for PAGELATCH_IO, you can benefit from a faster I/O subsystem for SQL Server.
A cause can be poor design of your database - you may wish to split out data located on 'hot pages', which are accessed frequently and which you might identify as the causes of your latch contention. For example, if you have a currency table with a data page containing 100 rows, of which 1 is updated per transaction and you have a transaction rate of 200/sec, you could see page latch queues of 100 or more. If each page latch wait costs just 5ms before clearing, this represents a full half-second delay for each update. In this case, splitting out the currency rows into different tables might prove more performant (if less normalized and logically structured).
References: https://www.mssqltips.com/sqlservertip/3088/Explanation:-of-sql-server-io-and-latches/

NEW QUESTION 3
HOTSPOT
You need to set up the service accounts that the database engine and SQL Server Agent services will use.
How should you design the solution? To answer, select the appropriate configuration options in the answer area.

Answer:

Explanation: Box 1: Domain Account
The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose fromthe following options:
* Built-in account. You can choose from a list of the following built-in Windows service accounts: Local System account.
* This account. Lets you specify the Windows domain account in which the SQL Server Agent service runs.
Box2: Domain users
Microsoft recommends choosing a Windows user account that is not a member of the Windows Administrators group.
Box 3: Managed Service Accounts
When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary.
Note: A Managed Service Account (MSA) can run services on a computer in a secure and easy to maintain manner, while maintaining the capability to connect to network resources as a specific user principal.
References:https://msdn.microsoft.com/en-us/library/ms191543.aspx

Topic 7, Mix Questions Set
Topic 7, Mix Questions Set

NEW QUESTION 4
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.

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 5
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 XAML template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Azure ResourceManager 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 6
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 7
You use Microsoft SQL Server 2014 to develop a database application. You need to create an object that meets the following requirements:
Which object should you use?

• A. Scalar-valued function
• B. Inline function
• C. User-defined data type
• D. Stored procedure

Answer: D

Explanation: Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.
References:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-datab

NEW QUESTION 8
A company has an on-premises Microsoft SQL Server 2021 environment. All futures databases must meet the following requirements:
The recovery model must be set to simple.
The compatibility level must be set to SQL server 2014 (120).
Your need to configure the SQL server 2021 environment.
In the table below, identify the database you must modify for each requirement.

Answer:

Explanation: Model: Change from full to simple Recovery Model
Newly created user databases use the same recovery model as the model database.
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database.
Model: Set compatibility level to 120
For all installations of SQL Server, the default compatibility level is set to the version of the Database Engine. Databases are set to this level unless the model database has a lower compatibility level.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2021 https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-se

NEW QUESTION 9
You manage an on-premises Microsoft SQL server that has a database named DB1. An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records. You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
A)

B)

C)

D)

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

Answer: B

Explanation: Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

NEW QUESTION 10
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 11
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 12
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 GS- Series VM and store the tempdb database on attached Premium storage. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 13
You administer two instances of Microsoft SQL Server 2014. You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

• A. Configure the application as data-tiered.
• B. Open port 1433 on the Windows firewall on the server.
• C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
• D. Start the SQL Server Browser Service.

Answer: D

Explanation: The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
References: https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

NEW QUESTION 14
You plan to migrate on-premises Microsoft SQL Server to SQL Server on a Microsoft Azure virtual machine. You need to ensure that the Azure virtual machine can handle the workload.
Which tool should you use for each environment? To answer, drag the appropriate tools to the correct options. Each tool may be used once. More than once, or not at all.

Answer:

Explanation:

NEW QUESTION 15
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 16
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account.
You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes.
Solution: You run the Add-AzureRmMetricAlertRule cmdlet and specify the –MetricName ‘Network Out’ parameter.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

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