Realistic 70-765 Study Guides 2021

NEW QUESTION 1
You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: G

Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-

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

You write the following Transact-SQL query:

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 database named Orders. Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL. Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?

• A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
• B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCodeIS NOT NULL
• C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
• D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Answer: B

Explanation: Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

NEW QUESTION 4
You plan to deploy 20 Microsoft Azure SQL Database instances to an elastic pool in Azure to support a batch processing application.
Two of the databases in the pool reach their peak workload threshold at the same time every day. This leads to inconsistent performance for batch completion.
You need to ensure that all batches perform consistently. What should you do?

• A. Create an In-Memory table.
• B. Increase the storage limit in the pool.
• C. Implement a readable secondary database.
• D. Increase the total number of elastic Database Transaction Units (eDTUs) in the pool.

Answer: D

Explanation: In SQL Database, the relative measure of a database's ability tohandle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for databases in an elastic pool.
A pool is given a set number of eDTUs, for a set price. Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more eDTUs to meet demand.
Additional eDTUs can be added to an existing pool with no database downtime. References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 5
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 a development environment. Each VM has a dedicated disk for backups.
You need to backup a database to the local disk on a VM. The backup must be replicated to another region.
Which storage option should you use?

• A. Premium P10 disk storage
• B. Premium P20 diskstorage
• 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: E

Explanation: Note: SQL Database automatically creates a database backups and uses Azure read- access geo-redundant storage (RA-GRS) to provide geo-redundancy. These backups are created automatically and at no additional charge. You don't need to do anything to make them happen. Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated- backups

NEW QUESTION 6
Your company has several Microsoft Azure SQL Database instances used within an elastic pool. You need to obtain a list of databases in the pool.
How should you complete the commands? To answer, drag the appropriate segments to the correct targets. Each segment 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.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/cli/azure/sql/elastic-pool?view=azure-cli-latest#az-sql-elastic-pool-list-dbs

NEW QUESTION 7
You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?

• A. min memory per query to 4 GB
• B. index create memory to 16 GB
• C. max worker threads to 1
• D. max server memory to 16 GB

Answer: B

Explanation: The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-indexcreate-memory-ser

NEW QUESTION 8
You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?

• A. VIEW ANY DEFINITION
• B. VIEW SERVER STATE
• C. VIEW DEFINITION
• D. CONTROL SERVER

Answer: B

Explanation: To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-

NEW QUESTION 9
HOTSPOT
You need to configure the data entry and business intelligence databases. In the table below, identify the option that you must use for each database. NOTE: Make only one selection in each column.

Answer:

Explanation: Data Entry: Geo-replicated database only
From Contoso scenario: 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.
Business intelligence: Elastic database pools only
From Contoso scenario: For the business intelligence application, corporate executives must be able to view all data in near real-time with low network latency.
SQL DB elastic pools provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage patterns.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool
Topic 6, SQL Server ReportingBackground
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2, Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2021. Thousands of records are inserted into DB1 or updated each second. Inserts are made by many different external applications that your company's developers do not control. You observe that transaction log write latency is a bottleneck in performance. Because of the transient nature of all the data in this database, the business can tolerate some data loss in the event of a server shutdown.
Database Name: DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2021. Thousands of records are updated or inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most writes must have no tolerance for data loss in the event of a server shutdown. The business has identified certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name: Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.

NEW QUESTION 10
You administer a single server that contains a Microsoft SQL Server 2014 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Use the SQL Server default instance and configure an affinity mask.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Install a new default SQL Server instance on the server.

Answer: B

Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server

NEW QUESTION 11
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 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
DRAG DROP
You are building a new Always On Availability Group in Microsoft Azure. The corporate domain controllers (DCs) are attached to a virtual network named ProductionNetwork. The DCs are part of an availability set named ProductionServers1.
You create the first node of the availability group and add it to an availability set named ProductionServers2. The availability group node is a virtual machine (VM) that runs Microsoft SQL Server. You attach the node to ProductionNetwork.
The servers in the availability group must be directly accessible only by other company VMs in Azure.
You need to configure the second SQL Server VM for the availability group.
How should you configure the VM? To answer, drag the appropriate configuration settings to the correct target locations. Each configuration setting 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.

Answer:

Explanation: ;
Box 1: ProductionNetwork
The virtual network is named ProductionNetwork.
Box 2: None /Not Assigned
As the servers in the availability group must be directly accessible only by other company VMs in Azure, there should be no Public IP address.
Box 3: ProductionServer2
You create the first node of the availability group and add it to an availability set named ProductionServers2. The availability group node is a virtual machine (VM) that runs Microsoft SQL Server.

NEW QUESTION 14
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 DB1.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: This problem is related to the disk response time, not to the number of log files.
References:
https://www.mssqltips.com/sqlservertip/4131/troubleshooting-sql-server-transaction-log-related-wait-types/

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 instance. After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server. What should you do?

• A. Modify tempdb location in startup parameters.
• B. Start SQL Server in minimal configuration mode.
• C. Start SQL Server in single-user mode.
• D. Configure SQL Server to bypass Windows application logging.

Answer: B

Explanation: If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option.
When you start an instance of SQL Server in minimal configuration mode, note the following: Only a single user can connect, and the CHECKPOINT process is not executed.
Remote access and read-ahead are disabled. Startup stored procedures do not run.
tempdb is configured at the smallest possible size.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-with-minimal-configur

NEW QUESTION 16
HOTSPOT
You use Resource Manager to deploy a new Microsoft SQL Server instance in a Microsoft Azure virtual machine (VM) that uses Premium storage. The combined initial size of the SQL Server user database files is expected to be over 200 gigabytes (GB). You must maximize performance for the database files and the log file.
You add the following additional drive volumes to the VM:

You have the following requirements:
You need to deploy the SQL instance.
In the table below, identify the drive where you must store each SQL Server file type. NOTE: Make only one selection in each column. Each correct selection is worth one point.

Answer:

Explanation: Enable read caching on the disk(s) hosting the data files and TempDB.
Do not enable caching on disk(s) hosting the log file. Host caching is not used for log files.

Topic 2, Manage databases and instances

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