Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
Background Corporate Information
Fabrikam, Inc. is a retailer that sells electronics products on the Internet. The company has a headquarters site and one satellite sales office. You have been hired as the database administrator, and the company wants you to change the architecture of the Fabrikam ecommerce site to optimize performance and reduce downtime while keeping capital expenditures to a minimum. To help with the solution, Fabrikam has decided to use cloud resources as well as on-premise servers.
Physical Locations
All of the corporate executives, product managers, and support staff are stationed at the headquarters office. Half of the sales force works at this location. There is also a satellite sales office. The other half of the sales force works at the satellite office in order to have sales people closer to clients in that area. Only sales people work at the satellite location.
Problem Statement
To be successful, Fabrikam needs a website that is fast and has a high degree of system uptime. The current system operates on a single server and the company is not happy with the single point of failure this presents. The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers. During your investigation, you discover that the sales force reports are causing significant contention.
Configuration
Windows Logins
The network administrators have set up Windows groups to make it easier to manage security. Users may belong to more than one group depending on their role. The groups have been set up as shown in the following table:

Server Configuration The IT department has configured two physical servers with Microsoft Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition and one Windows Azure Server. There are two tiers of storage available for use by database files only a fast tier and a slower tier. Currently the data and log files are stored on the fast tier of storage only. If a possible use case exists, management would like to utilize the slower tier storage for data files. The servers are configured as shown in the following table:

Database
Currently all information is stored in a single database called ProdDB, created with the following script:

The Product table is in the Production schema owned by the ProductionStaff Windows group. It is the main table in the system so access to information in the Product table should be as fast as possible. The columns in the Product table are defined as shown in the following table:

The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read. The columns in the SalesOrderDetail table are defined as shown in the following table:

Database Issues
The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the current system, you found that there are no automated maintenance procedures. The database is severely fragmented, and everyone has read and write access.
Requirements Database
The database should be configured to maximize uptime and to ensure that very little data is lost in the event of a server failure. To help with performance, the database needs to be modified so that it can support in-memory data, specifically for the Product table, which the CIO has indicated should be a memoryoptimized table. The auto-update statistics option is set off on this database. Only product managers are allowed to add products or to make changes to the name, description, price, cost, and supplier. The changes are made in an internal database and pushed to the Product table in ProdDB during system maintenance time. Product managers and others working at the headquarters location also should be able to generate reports that include supplier and cost information.
Customer data access
Customers access the company's website to order products, so they must be able to read product information such asname, description, and price from the Product table. When customers place orders, stored procedures calledby the website update product quantityon-hand values. This means the product table is constantly updated at randomtimes.
Customer support data access
Customer support representatives need to be able to view and not update or change product information. Management does not want the customer support representatives to be able to see the product cost or any supplier information.
Sales force data access
Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times and there can be no reporting downtime to refresh the data set except during the monthly maintenance window. The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
Historical Data
The system should keep historical information about customers who access the site so that sales people can see how frequently customers log in and how long they stay on the site.
The information should be stored in a table called Customer Access. Supporting this requirement should have minimal impact on production website performance.
Backups
The recovery strategy for Fabrikam needs to include the ability to do point in time restores and minimize the risk of data loss by performing transaction log backups every 15 minutes.
Database Maintenance
The company has defined a maintenance window every month when the server can be unavailable. Any maintenance functions that require exclusive access should be accomplished during that window.
Project milestones completed
Revoked all existing read and write access to the database, leaving the schema ownership in place.
Configured an Azure storage container secured with the storage account name MyStorageAccount with the primary access key StorageAccountKey on the cloud file server.
SQL Server 2014 has been configured on the satellite server and is ready for use.
On each database server, the fast storage has been assigned to drive letter F:, and the slow storage has been assigned to drive letter D:.
You need to create the CustomerAccess table to support the reporting and performance requirements. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not
need all of the code blocks.

Answer:

Explanation:

NEW QUESTION 2
You have the following Microsoft SQL Server instances:

You have a database named DB1 that is hosted on SQL1.
Users in the branch office must be able to access DB1 from SQL3.
You need to configure an AlwaysOn Availability Group and limit latency on the secondary server. Which settings should you configure?

• A. FAILOVER_MODE = AUTOMATIC
• B. AVAILABILITY MODE = SYNCHRONOUS_COMMIT
• C. FAILOVER_MODE = MANUAL
• D. AVAILABILITY MODE = ASYNCHRONOUS_COMMIT

Answer: D

Explanation: Asynchronous-commit mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances. If every secondary replica is running under asynchronous-commit mode, the primary replica does not wait for any of the secondary replicas to harden the log. Rather, immediately after writing the log record to the local log file, the primary replica sends the transaction confirmation to the client. The primary replica runs with minimum transaction latency in relation to a secondary replica that is configured for asynchronous-commit mode. If the current primary is configured for asynchronous commit availability mode, it will commit transactions asynchronously for all secondary replicas regardless of their individual availability mode settings.

NEW QUESTION 3
General Overview
You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.
Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is currently using. The current application will remain in use while the users are trained to use the new webbased application.
You need to design the SQL Server and database infrastructure for the web-based application. Databases
You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting. The Sales database contains a table named OrderTotals and a table named SalesInfo.
A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.
The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table.
The Current_Inventory database contains a large table named Inv_Current. The Inv_Current table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property.
The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed by multiple queries in the Sales database.
Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2.
An external application named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo.
A stored procedure named SPUPdateGenInfo combines data from multiple databases and generates millions of rows of data in the GenInfo table.
The GenInfo table is used for reports.
When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data.
The GenInfo table is deleted after the reporting process completes. The Products database contains tables named ProductNames and ProductTypes.
Current System
The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB. This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the Current_Inventory database.
SQL Servers
A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.
Design Requirements
Your SQL Server infrastructure and database design must meet the following requirements:
Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.
Direct access to database tables by developers or applications must be denied.
The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.
Deadlocks must be analyzed with the use of Deadlock Graphs.
In the event of a SQL Server failure, the databases must remain available.
Software licensing and database storage costs must be minimized.
Development effort must be minimized.
The Tempdb databases must be monitored for insufficient free space.
Failed authentication requests must be logged.
Every time a new row is added to the ProductTypes table in the Products database, a user defined function that validates the row must be called before the row is added to the table.
When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.
You need to recommend a solution to meet the design requirement of logging all failed authentication requests.
What should you recommend?

• A. Object Access Auditing
• B. C2 Audit Mode
• C. Logon Triggers.
• D. Login Auditing.

Answer: D

NEW QUESTION 4
You have a database named DB1.
You need to encrypt two columns in DB1 by using column-level encryption.
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.

Answer:

Explanation: Step 1: You must have a database master key. If your database does not already have a database master key, create one.
Step 2: Create a certificate. Step 3: Create a symmetric key. References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data

NEW QUESTION 5
You plan to deploy a database to SQL Azure. You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
Prevent data read by USP_1 from being modified by other active processes.
Allow USP_2 to perform dirty reads.
You need to recommend the isolation level for the stored procedures. The solution must maximize concurrency.
Which isolation levels should you recommend? To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

Answer:

Explanation: SP1 – repeatable read; SP2 – read uncommited Note:
- SP1: repeatable read a repeatable read scan retains locks on every row it touches until the end of the transaction. Even rows that do not qualify for the query result remain locked. These locks ensure that the rows touched by the query cannot be updated or deleted by a concurrent session until the current transaction completes (whether it is committed or rolled back).
- SP2: read uncommitted permits repeatable reads

NEW QUESTION 6
Your company hosts Microsoft SQL Server instances for customers. One customer requires that the SQL Server instance send an email when an alert is generated in the Sales database.
You need to create the alert.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

Answer:

Explanation: The following example adds an e-mail notification for the specified alert (Test Alert).
NOTE: 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/relational-databases/system-stored-procedures/sp-add-notification-transact-

NEW QUESTION 7
You plan to create a database.
The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly available.
After the event, the database will be deleted.
You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

• A. SQL Server 2014 Enterprise
• B. SQL Server 2014 Standard
• C. SQL Azure
• D. SQL Server 2014 Express with Advanced Services

Answer: B

Explanation: Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterprise editions only. References: Features Supported by the Editions of SQL Server 2014.

NEW QUESTION 8
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
A customer that uses an on-premises instance reports that queries take a long time to complete.
You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.
Which Transact-SQL segment should you use?

• A. sp_autostats
• B. AUTO_UPDATE_STATISTICS_ASYNC
• C. SET AUTO_UPDATE_STATISTICS ON
• D. CREATE STATISTICS

Answer: C

Explanation: You can turn on automatic statistics update by running this SQL statement: SET AUTO_UPDATE_STATISTICS ON

NEW QUESTION 9
You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1. SProc1 will query a table named Table2 in DB2. You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Contained databases
• B. Application roles
• C. Cross-database ownership chaining
• D. Digital certificates

Answer: B

Explanation: An application role is a database principal that enables an application to run with its own, userlike permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default.

NEW QUESTION 10
You manage a Microsoft SQL Server environment with a database named obi. You notice that queries take longer to complete than they did a year ago.
You increase the file size for the database named tempdb, but it does not resolve the problem. You need to capture runtime statistics for all queries.
How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Use the ALTER DATABASE statement to enable the query store. For example: ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-qu

NEW QUESTION 11
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 are migrating a set of databases from an existing Microsoft SQL Server instance to a new instance. You need to complete the migration while minimizing administrative effort and downtime.
Which should you implement?

• A. log shipping
• B. an Always On Availability Group with all replicas in synchronous-commit mode
• C. a file share witness
• D. a SQL Server failover cluster instance (FCI)
• E. a Windows Cluster with a shared-nothing architecture
• F. an Always On Availability Group with secondary replicas in asynchronous-commit mode

Answer: A

Explanation: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The
transaction log backups are applied to each of the secondary databases individually.
References:
https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-serv

NEW QUESTION 12
You need to recommend a backup process for an Online Transaction Processing (OLTP) database. The process must meet the following requirements:
Ensure that if a hardware failure occurs, you can bring the database online with a minimum amount of data loss.
Minimize the amount of administrative effort required to restore any lost data.
What should you include in the recommendation? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation:

NEW QUESTION 13
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 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 section, you will NOT be able to return to it As a result these questions will not appear in the review screen.
You have a database named DB1 that is 640 GB and is updated frequently.
You enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes. You discover that the disks on the data server are almost full.
You need to reduce the amount of disk space used by the log shipping process. Solution: You enable compression for the transaction log backups:
Does this meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 14
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
A customer that uses an on-premises instance reports that queries take a long time to complete.
You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.
Which Transact-SQL segment should you use?

• A. sys.index_columns
• B. UPDATE STATISTICS
• C. CREATE STATISTICS
• D. SET AUTO_CREATE_STATISTICS ON

Answer: D

Explanation: AUTO_UPDATE_STATISTICS { ON | OFF }
ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-20

NEW QUESTION 15
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You
observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
database name
impacted file id
impacted file physical name
impacted page id
event type that identifies the error type
error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
Capture queries based on resource consumption.
Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to write the query the maintenance plan will use to update the statistics.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1: Use SalesDB2…
We specify the specific database SalesDB2, not the master. Step 2:
Step 3:
From scenario: The plan should only update statistics that were automatically created and have not been updated for 30 days.
Step 4:
Declare the curser using WITH FULLSCAN. References:
https://solutioncenter.apexsql.com/how-to-automate-and-schedule-sql-server-index-defragmentation/

NEW QUESTION 16
You plan to implement a fault tolerance solution for a Microsoft SQL Server database. The solution must provide failover storage on the local network.
You need to ensure that the solution can route traffic to failover storage by using SMB 3.0. Which storage option should you use?

• A. Cluster Shared Volumes
• B. Microsoft Azure Blob storage
• C. Always On availability group
• D. Stretch Database

Answer: A

Explanation: Clustered Shared Volumes (CSV) is a new clustered file system in Windows Server that is a layer of abstraction above the NTFS file system in a WSFC environment. It allows all Nodes in the failover cluster to read and write to the CSV volume. CSV leverages the investments Microsoft have made in SMB 3.0, such as SMB Direct and SMB Multichannel.
SQL Server 2014 was the first version of SQL Server to support CSVs.
References: https://www.microsoftpressstore.com/articles/article.aspx?p=2832586&seqNum=5