Microsoft 70-765 Exam Dumps 2021

NEW QUESTION 1
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 tempdb databases.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

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 2
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.
You need to implement real-time encryption of the database and all backups.
Solution: You password protect all azure SQL backups and enable azure active directory authentication for all azure SQL server instances.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Password protection does not encrypt the data.
Transparent Data Encryption (TDE) would provide a solution. References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/

NEW QUESTION 3
User report that a query takes a long time to execute. The query has the following wait statistics.

Which resource causes the issue?

• A. processor
• B. disk
• C. blocking
• D. network

Answer: B

Explanation: PAGEIOLATCH Wait time and WaitCount are both high.
One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions
The Microsoft definition of this wait type is:
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
References: https://www.sqlshack.com/handling-excessive-sql-server-pageiolatch_sh-wait-types/

NEW QUESTION 4
You have an on-premises server that runs Windows Server 2012 R2. The server has a Microsoft SQL Server 2021 instance that has one user database. The database is 2 TB.
Your company has a Win32 application installed on 1,000 computers. The application connects to the database by using a network name of server1.contoso.local.
You need to migrate the database to SQL Server 2021 on a Microsoft Azure virtual machine that runs Windows Server 2021. The solution must minimize outages to the application.
What should you do?

• A. Copy the database files and update the records in DNS.
• B. Implement an availability group and update the records in DNS.
• C. Implement database mirroring and update the records in DNS.
• D. Implement database mirroring and change the connection string.

Answer: B

Explanation: SQL Server high availability and disaster recovery (HADR) technologies that are supported in Azure include: References:

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

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

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 6
You plan to deploy Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine will have a 30-TB database and will have 10 1-TB VHDs for the database.
You need to configure the storage to meet the following requirements:
Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?

• A. a parity storage pool
• B. a simple storage pool
• C. a mirrored storage pool
• D. a striped volume
• E. a RAID-5 volume

Answer: D

Explanation: Data that is written to a striped volume is interleaved to all disks at the same time instead of sequentially. Therefore, disk performance is the fastest on a RAID 0 volume as compared to any other type of disk
configuration.
Reference:
https://support.microsoft.com/en-us/help/323433/how-to-establish-a-striped-volume-raid-0-inwindows-server-20

NEW QUESTION 7
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You create a SQL Server Agent job by using the following statement.

You need to send an email message if the job fails. Which stored procedure should you use?

• A. msd
• B. db
• C. sp_updace_alerc
• D. msdb.dbo.sp_add_jobstep
• E. msdb.dbo.sp_add_notification
• F. msdb.dbo.sp_help_alert

Answer: C

Explanation: To notify an operator of job status through Transact-SQL.
In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query.
-- adds an e-mail notification for the specified alert (Test Alert).
-- This example assumes that Test Alert already exists
-- and that François Ajenstat is a valid operator name. USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N'Test Alert',
@operator_name = N'François Ajenstat',
@notification_method = 1 ; GO
References:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-2021

NEW QUESTION 8
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 9
You administer a Microsoft SQL Server 2014 database that contains a table named AccountTransaction. You discover that query performance on the table is poor due to fragmentation on the
IDX_AccountTransaction_AccountCode non-clustered index. You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.
Which Transact-SQL batch should you use?

• A. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode REORGANIZE
• B. ALTER INDEX ALL ON AccountTransaction REBUILD
• C. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode REBUILD
• D. CREATE INDEX IDXAccountTransactionAccountCode ONAccountTransaction.AccountCode WITH DROP EXISTING

Answer: A

Explanation: Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).
References: https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

NEW QUESTION 10
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. What should you create?

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

Answer: F

Explanation: To audit users in a role use a Database Audit Specification.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql

NEW QUESTION 11
You have an on-premises SQL Server database named DB1 that contains a table named TB1. TB1 is stretched to Microsoft Azure.
A catastrophic hardware failure occurs on the on-premises SQL server.
You deploy a new on-premises server and restore all databases to the new server. You need to resume Stretch Database operations to Azure.
Which statements should you execute?

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

Answer: B

Explanation: Use ALTER TABLE, not CREATE TABLE.
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 12
You have an on-premises database.
You plan to migrate the database to Microsoft SQL Server on a Microsoft Azure virtual machine.
You move the database files to Azure.
You need to attach the database files to the SQL Server instance on the virtual machine. The solution must ensure that you can run file snapshot backups.
How should you complete the statement? 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/sql/t-sql/statements/create-database-sql-server-transact-sql

NEW QUESTION 13
You have a server named server1-contoso.database.windows.net that has a Microsoft Azure SQL database.
You need to create a group of Azure SQL databases that share the resources on server1-contoso.database.windows.net.
Which cmdlet should you run before you create the database?

• A. New-AzureRmAvailabilitySet
• B. New-AzureRmLoadBalancer
• C. New-AzureRmSqlDatabaseSecondary
• D. New-AzureRmSqlElasticPool
• E. New-AzureRmVM
• F. New-AzureRmSqlServer
• G. New-AzureRmSqlDatabaseCopy
• H. New-AzureRmSqlServerCommunicationLink

Answer: D

Explanation: The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database.
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. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

NEW QUESTION 14
You administer a Microsoft SQL Server 2014 database that contains a table named OrderDetail.
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?

• A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
• B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
• C. ALTER INDEX ALL ON OrderDetail REBUILD
• D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B

Explanation: REORGANIZE specifies to reorganize the index leaf level. The REORGANIZE operation is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

NEW QUESTION 15
You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012. You create an Availability Group defined by the following schema. (Line numbers are included for reference
only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?

• A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,READ_ONLY_ROUTING_LIST = NONE)
• B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
• C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
• D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =YES,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

Answer: C

Explanation: As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.

NEW QUESTION 16
You have a Microsoft SQL Server 2014 named SRV2014 that has a single tempdb database file. The tempdb database file is eight gigabytes (GB) in size.
You install a SQL Server 2021 instance named SQL Server 2021 by using default settings. The new instance has eight logical processor cores.
You plan to migrate the databases from SRV2014 to SRV2021.
You need to configure the tempdb database on SRV2021. The solution must minimize the number of future tempdb autogrowth events.
What should you do?

• A. Increase the size of the tempdb datafile to 8 G
• B. In the tempdb database, set the value of the MAXDOP property to8.
• C. Increase the size of the tempdb data files to1 GB.
• D. Add seven additional tempdb data file
• E. In the tempdb database, set the value of the MAXDOP property to8.
• F. Setthe value for the autogrowth setting for the tempdb data file to128megabytes (MB). Add seven additional tempdb data files and set the autogrowth value to128 MB.

Answer: B

Explanation: In an effort to simplify the tempdb configuration experience, SQL Server 2021 setup has been extended to configure various properties for tempdb for multi-processor environments.
1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
2. Configuration options: Data Files
* Number offiles – this will default to the lower value of 8 or number of logical cores as detected by setup.
* Initial size – is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size) that will be created.
* Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MBwas chosen to cover one PFS interval.
Figure:

References:https://blogs.msdn.microsoft.com/psssql/2021/03/17/sql-2021-it-just-runs-faster-automatic-tempdb-configuration/

NEW QUESTION 17
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 18
You administer a Microsoft SQL Server 2014 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique. You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?

• A. Back up the master database on the SQL2008 instanc
• B. Restore the master database on the SQL2012 instance
• C. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package
• D. Use sp_grantlogin
• E. Use xp_logininfo.

Answer: C

Explanation: sp_grantlogin creates a SQL Server login.

NEW QUESTION 19
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 master database.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The master database does not handle alerts and jobs. It records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
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