Approved 70-765 Braindumps 2021

NEW QUESTION 1
You have Microsoft SQL Server on a Microsoft Azure Virtual machine that has a 4-TB database.
You plan to configure daily backups for the database. A single full backup will be approximately 1.5 TB of compressed data.
You need to ensure that the last backups are retained. Where should you store the daily backups?

• A. Local storage
• B. Page blob storage
• C. Virtual disks
• D. Block blob storage.

Answer: D

Explanation: When backing up to Microsoft Azure blob storage, SQL Server 2021 supports backing up to multiple blobs to enable backing up large databases, up to a maximum of 12.8 TB. This is done through Block Blobs.
References:

NEW QUESTION 2
You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?

• A. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'RESTORE DATABASEOrders_ReportingFROM DISK = 'D:Orders.bak
• B. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'CREATE DATABASEOrders_ReportingFROM DISK = 'D:Orders.bak
• C. CREATE DATABASE Orders_Reporting AS COPY OF Orders
• D. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'MIRROR TO DISK = 'Orders_Reporting

Answer: C

Explanation: BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database

NEW QUESTION 3
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 4
You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on premises replicas.
You need to configure the network to support the availability group listener. Which cmdlet should you run first?

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

Explanation: An availability group listener is a virtual network name that clients connect to for database access. On Azure virtual machines, a load balancer holds the IP address for the listener. The load balancer routes traffic to the instance of SQL Server that is listening on the probe port. Usually, an availability group uses an internal load balancer.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowsportal-sql-ps-al

NEW QUESTION 5
HOTSPOT
You need to create the contosodb1 database.
How should you complete the Azure PowerShell command? To answer, select the appropriate Azure PowerShell segments in the answer area.

Answer:

Explanation: Box 1: New-AzureRmSqlDatabase
New-AzureRmSqlDatabase creates a database or an elastic database.
New-AzureRmSqlDatabase is a command with the Azure Resource Manager (AzureRM) module.Azure Resource Manager enables you to work with the resources in your solution as a group.

NEW QUESTION 6
You administer a Microsoft SQL Server 2014 database. The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.
You need to leave the primary database in a restoring state.
Which option of the BACKUP LOG command should you use?

• A. NO_TRUNCATE
• B. NORECOVERY
• C. STANDBY
• D. FORMAT

Answer: B

Explanation: It is recommended that you take a tail-log backup in the following scenarios:
* If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.
Note: A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

NEW QUESTION 7
You plan to deploy two new Microsoft Azure SQL Database instances. Once instance will support a data entry application. The other instance will support the company’s business intelligence efforts. The databases will be accessed by mobile applications from public IP addresses.
You need to ensure that the database instances meet the following requirements:
The database administration team must receive alerts for any suspicious activity in the data entry database, including potential SQL injection attacks.
Executives around the world must have access to the business intelligence application.
Sensitive data must never be transmitted. Sensitive data must not be stored in plain text in the database. In the table below, identify the feature that you must implement for each database.
NOTE: Make only one selection in each column. Each correct selection is work one point.

Answer:

Explanation: Data entry: Threat Detection
SQL Threat Detection provides a new layer of security, which enables customers to detect and respond to potential threats as they occur by providing security alerts on anomalous activities. Users receive an alert upon suspicious database activities, potential vulnerabilities, and SQL injection attacks, as well as anomalous database access patterns.
Business intelligence: Dynamic Data Masking
Dynamic data masking limits (DDM) sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

NEW QUESTION 8
You administer a Microsoft SQL Server 2014 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: B: FileTables extend the capabilities of the FILESTREAM feature of SQL Server. Therefore you have to enable FILESTREAM for file I/O access at the Windows level and on the instance of SQL Server before you can create and use FileTables.
D: Before you can create FileTables in a database, the database must have a FILESTREAM filegroup. F: Specifying a Directory for FileTables at the Database Level
When you enable non-transactional access to files at the database level, you can optionally provide a directory name at the same time by using the DIRECTORY_NAME option. If you do not provide a directory name when you enable non-transactional access, then you have to provide it later before you can create FileTables in the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable

NEW QUESTION 9
You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?

• A. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE
• B. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE
• C. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE
• D. ALTER INDEX IX OrderDateON CustomerOrder REBUILD

Answer: C

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 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

NEW QUESTION 11
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do?

• A. use SQL Profiler to trace all queries that are processing on the serve
• B. Filter queries that have a Duration value of more than 1,000.
• C. Use sp_configure to set a value for blocked process threshol
• D. Create an extended event session.
• E. Use the Job Activity monitor to review all processes that are actively runnin
• F. Review the Job History to find out the duration of each step.
• G. Run the sp_who command from a query window.
• H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Answer: A

NEW QUESTION 12
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 13
You have Microsoft SQL Server on a DS-series Microsoft Azure virtual machine. The virtual machine has 28 GB of memory.
You discover the following performance statistics on the server:
The average Page life expectancy is 30.
The server has excessive PAGELATCH_IO waits.
You need to decrease the PAGELATCH_IO waits. What should you do?

• A. Enable large-page support.
• B. Enable lock pages in memory.
• C. Configure buffer pool extensions.
• D. Add more tempdb files.

Answer: ACD

NEW QUESTION 14
You use Microsoft Azure Resource Manager to deploy two new Microsoft SQL Server instances in an Azure virtual machine (VM). VM has 28 gigabytes (GB) of memory. The instances are named Instance1 and Instance2, respectively.
The various databases on the instances have the following characteristics:

You run the following Transact-SQL statements:

You need to configure each SQL Server instance to correctly allocate memory. What should you do?

• A. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• B. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• C. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• D. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:

Answer: D

NEW QUESTION 15
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 16
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A. SQL user without login
• B. User mapped to an asymmetric key
• C. Domain user
• D. login mapped to a virtual account

Answer: C

Explanation: If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account.
References:https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-servic

NEW QUESTION 17
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 log files to tempdb. 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/2021/06/20/resolving-sql-server-transaction-log-waits/