Most Up-to-date 70-765 Preparation Labs 2021

NEW QUESTION 1

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 2

You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

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.

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 OrdersINNER 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, Orderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS
• 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', CountryAS `@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: E

NEW QUESTION 3

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.

• A. Mastered
• B. Not Mastered

Answer: A

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 4

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 5

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 6
You have a database named DB1. You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?

• A. Run DBCC CHECKDB (‘db1’, REPAIR_ALLOW_DATA_LOSS).
• B. Run DBCC CHECKDB (‘db1’, REPAIR_FAST).
• C. Delete the transaction logs and restart the Microsoft SQL Server instance.
• D. Run DBCC CHECKDB (‘db1’, REPAIR_REBUILD).
• E. Restore the database from a backup.

Answer: C

Explanation:

REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

NEW QUESTION 7

You administer a Microsoft SQL Server 2014 database.
The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

• A. Convert all indexes to Column Store indexes.
• B. Implement Unicode Compression.
• C. Implement row-level compression.
• D. Implement page-level compression.

Answer: D

NEW QUESTION 8

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 migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space.
The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create 30 storage accounts that each has one container. You create a VHD in each container. Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References: https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/

NEW QUESTION 9
DRAG DROP
A new Azure Active Directory security principal named ReportUser@contoso.onmicrosoft.com should have access to select all current and future objects in the Reporting database. You should not grant the principal any other
permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad- authentication

NEW QUESTION 10

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 11

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 12

You administer a Microsoft SQL Server 2014 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?

• A. Move user databases to a local disk.
• B. Expand the tempdb data and log files
• C. Modify application code to use table variables
• D. Move the tempdb files to a local disk

Answer: D

Explanation:
The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks.
References:https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

NEW QUESTION 13

You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You use drive D on the virtual machine to store the database files. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
The D drive should only be used for temporary data.

NEW QUESTION 14

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 manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database. 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: D

Explanation:
To encrypt columns you can configure Always Encrypted.
SQL Server Management Studio (SSMS) provides a wizard that helps you easilyconfigure Always Encrypted by setting up the column master key, column encryption key, and encrypted columns for you.
Always Encrypted allows client applications to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server or Azure SQL Database. An Always Encrypted enabled driver, such as the ODBC Driver 13.1 for SQL Server, achieves this by transparently encrypting and decrypting sensitive data in the client application.
Note: The ODBC driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the data to SQL Server or Azure SQL Database. Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault#encrypt-columns-configure-always-encrypted https://msdn.microsoft.com/en-us/library/mt637351(v=sql.110).aspx

NEW QUESTION 15

You administer a SQL Server 2014 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?

• A. A domain user
• B. The BUILTINSYSTEM account
• C. A local user with Run as Service permissions
• D. The SQLBrowser account

Answer: A

Explanation:
References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-s

NEW QUESTION 16

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 create the desired SQL Server configuration in an Azure Resource Group, then export the Resource Group template and save it to the Templates Library.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
Azure Resource Manager template consists of JSON, and expressions that you can use to construct values for your deployment.
A good JSON editor, not a Resource Group template, can simplify the task of creating templates.
Note: In its simplest structure, a 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 17

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 18

Note: This question 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 have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs. Which storage option should you use?

• A. Premium P10 disk storage
• B. Premium P20 disk storage
• C. Premium P30 disk storage
• D. Standard locally redundant disk storage
• E. Standard geo-redundant disk storage
• F. Standard zone redundant blob storage
• G. Standard locally redundant blob storage
• H. Standard geo-redundant blob storage

Answer: A

Explanation:
P10 has 500 IOPS per disk, which provides for more than 1 million IOPS per month.
Note: 3600*30* 500 is 54 million IOPS/month.
References: https://azure.microsoft.com/en-us/pricing/details/managed-disks/

NEW QUESTION 19

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 20

You create a new Microsoft Azure subscription.
You need to create a group of Azure SQL databases that share resources. 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: D

Explanation:
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 (elastic Database Transaction Units (eDTUs)) 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.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 21
......