Printable 70-764 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 attempt to restore a database on a new SQL Server instance and receive the following error message:

You need to ensure that you can restore the database successfully. Solution: You generate a new certificate on the new instance.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: In order to successfully make the restore in a different server you will need to create a master certificate in the detonation and transfer the certificates and backups in that order.
References:
https://deibymarcos.wordpress.com/2021/11/15/how-to-restore-encrypted-databases-cannot-find-server-certifica

NEW QUESTION 2
You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

• A. The SQL Server error log
• B. The sys.dm_os_wait_stats dynamic management view
• C. The DBCC SQLPERF(WAITSTATS) command
• D. SQL Server Profiler

Answer: B

Explanation: sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
Columns include: waiting_tasks_count
Number of waits on this wait type.
This counter is incremented at the start of each wait.

NEW QUESTION 3
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 4
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 configure security on the Product table for customer support representatives.
Which two actions should you perform? Each correct answer presents part of the solution. (Choose two.)

• A. Create a view called CustProduct that includes columns ProductI
• B. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID.
• C. GRANT ALL on CustProduct TO OurDomainCustomerSupport
• D. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.
• E. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice.
• F. GRANT SELECT on CustProduct TO OurDomainCustomerSupport.
• G. GRANT SELECT on CustProduct TO public.

Answer: AE

Explanation: Give access to CustomerSupport through a view. The view must include all these columns (refer to scenario). GRANT Object Permissions (Transact-SQL)

NEW QUESTION 5
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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.
The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.
You need to ensure that the query optimizer always uses the query plan that you prefer. Solution: You create a copy of the plan guide for the query plan.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: KEEPFIXED PLAN should be used as it forces the query optimizer not to recompile a query due to changes in statistics.
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2021

NEW QUESTION 6
You have an application that queries a database. Users report that the application is slower than expected. You discover that several server process identifiers (SPIDs) have PAGELATCH_UP and PAGELATCH_EX
waits. The resource descriptions of the SPIDs contains 2:1:1.
You need to resolve the issue. What should you do?

• A. Allocate additional processor cores to the server.
• B. Add files to the file group of the application database.
• C. Reduce the fill factor of all clustered indexes.
• D. Add data files to tempdb.

Answer: D

Explanation: PAGELATCH contention in tempdb is typically on allocation bitmaps and occurs with workloads with many concurrent connections creating and dropping small temporary tables (which are stored in tempdb).
Assuming that the temporary tables are needed for performance, the trick is to have multiple data files for tempdb so that the allocations are done round-robin among the files, the contention is split over multiple PFS pages, and so the overall contention goes down.
References: https://sqlperformance.com/2015/10/sql-performance/knee-jerk-wait-statistics-pagelatch

NEW QUESTION 7
You manage a Microsoft SQL Server environment. A server fails and writes the following event to the application event log:
MSG_AUDIT_FORCED_SHUTDOWN
You configure the SQL Server startup parameters as shown in the following graphic:

Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: single-user
The startup option -m starts an instance of SQL Server in single-user mode. Box 2: sysadmin
Starting SQL Server in single-user mode enables anymember of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-option

NEW QUESTION 8
You plan to deploy a database by using SQL Server 2014. Your company identifies the following requirements for the database:
The name of all stored procedures must start with "usp_"always.
All distribution statistics must be updated daily
You need to identify which feature must be used to meet each database requirement.
Which features should you identify? To answer, drag the appropriate feature to the correct database requirement in the answer area.

Answer:

Explanation: - Policy-Based Management Each Stored Procedure that are created and that will be created has to have prefix "USP_".
- Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

NEW QUESTION 9
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 have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \SQLBackup. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.

You need to analyze the wait type and statistics for specific instanced in the environment.
Which object should you use to gather information about each instance? To answer, drag the appropriate
objects to the correct instances. Each object 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: Instance 1: sys.dm_exec_query_stats
From Scenario: Instance1 requirement: Aggregate statistics since last server restart. sys.dm_exec_query_stats returns aggregate performance statistics for cachedquery plans in SQL Server.
Instance 4: sys.dm_os_wait_stats
sys.dm_os_wait_statsreturns information about all the waits encountered by threads that executed. From Scenario: Instance4 requirement: Identify the most prominent wait types.

Instance 5:sys.dm_exec_session_wait_stats
From Scenario: Instance5 requirement: Identify all wait types for queries currently running on the server. sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed
for each session.

NEW QUESTION 10
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 ensure that the account used to generate reports can only connect during certain hours. What should you configure?

• A. A CHECK constraint.
• B. Windows Server Resource Manager (WSRM).
• C. Logon Triggers.
• D. Login Auditing.

Answer: C

NEW QUESTION 11
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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.
A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT operations or dynamic SQL commands.
The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.
You need to create the required security principals and grant the appropriate permissions. Solution: You run the following Transact-SQL statement in the master database:
CREATE LOGIN BI_User WITH PASSWORD = 'Pa$$wørd'
You run the following Transact-SQL statement in the business intelligence database:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: We create a login, create a user associated with this login, and grant execute permission to the procedure to this user. This is enough. The user does not need further permissions.
Note:
One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.
Best Practices
Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.
Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedur

NEW QUESTION 12
Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.
The new version will use SQL Server 2014.
The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.
You receive several support calls reporting unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.
Tables
The current database schema contains a table named OrderDetails.
The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.
The product price is stored in a table named Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.
A column named ProductName was created by using the varchar data type. The database contains a table named Orders.
Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security. Stored Procedures
The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

Customer Problems Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:

Backup Issues
Customers who have large amounts of historical purchase order data report that backup time is unacceptable. Search Issues
Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.
Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.
Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.
Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.
Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately. Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.
Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute. You need to recommend a solution that resolves the missing data issue.
The solution must minimize the amount of development effort. What should you recommend?

• A. Denormalize the Products table.
• B. Denormalize the OrderDetails table.
• C. Normalize the OrderDetails table.
• D. Normalize the Products table.

Answer: D

Explanation: - Scenario:
- Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the price that
the item was sold for in previous orders.
- The current database schema contains a table named OrderDetails. The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.
The product price is stored in a table named Products.

NEW QUESTION 13
You work as a Developer at ABC.com.
All databases are hosted on Windows Server 2012 servers running SQL Server 2012. The company has a database named Products.
Tables in the Products database contain data including part numbers, product name, color, type and size. Users in the Marketing department have created brochures for each product.
The brochures have been created in the XML Paper Specification (XPS) format.
You have been asked to add a table to the Products database to store the product brochures. The brochures need to be stored in a folder structure.
Company users will also need to access the brochures from Windows applications using UNC paths. How can you meet these requirements?

• A. By implementing the XMLNAMESPACES feature.
• B. By implementing the FILEGROUP feature.
• C. By implementing the FILETABLE feature.
• D. By implementing the FILESTREAM feature.

Answer: C

NEW QUESTION 14
Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.
The new version will use SQL Server 2014.
The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.
You receive several support calls reporting unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.
Tables
The current database schema contains a table named OrderDetails.
The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.
The product price is stored in a table named Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.
A column named ProductName was created by using the varchar data type. The database contains a table named Orders.
Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security. Stored Procedures
The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

Customer Problems Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:

Backup Issues
Customers who have large amounts of historical purchase order data report that backup time is unacceptable. Search Issues
Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.
Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.
Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.
Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.
Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately. Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.
Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.
You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

• A. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
• B. Modify the stored procedures to update tables in the same order for all of the stored procedures.
• C. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
• D. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.

Answer: B

Explanation: - Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Proc1 and Sales.Proc2 execute.

NEW QUESTION 15
You work as a Database Administrator (DBA) for a company named ABC.com.
The company has a Windows Azure subscription.
The company uses a cloud based SQL Server environment hosted on SQL Azure. Developers in your company are creating an ecommerce website.
You are designing a database for the website. The database will be hosted on SQL Azure.
The database will store and reuse web site login details and customer credit card numbers.
You need to ensure the username, passwords and credit card details are securely stored in the database. Which of the following would be the most suitable secure storage solution?

• A. Secure Sockets Layer (SSL)
• B. IPSec
• C. Data encryption
• D. Transparent Data Encryption (TDE)
• E. Encrypting File System (EFS)

Answer: C

NEW QUESTION 16
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.
Both SalesDb1 and SalesDb2 experience blocking and deadlock issues. You create an Extended Events session to monitor the databases. (Click the Exhibit button.)
You need to create and configure the Extended Events session.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

Answer:

Explanation: Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
References:
https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx https://blobeater.blog/2021/02/06/using-extended-events-in-azure/