Download 70-764 Software 2021

NEW QUESTION 1

You use SQL Azure to store data used by an e-commerce application. You develop a stored procedure named sp1.
Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing. Which transaction isolation level should you use in sp1?

• A. read committed
• B. repeatable read
• C. snapshot
• D. serializable

Answer: D

NEW QUESTION 2

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

Explanation:
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 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 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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.
You need to optimize query performance.
Solution: You run the DBCC CHECKDB command. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
DBCC CHECKDB only checks the logical and physical integrity of all the objects in the specified database. It does not update any indexes, and does not improve query performance.
References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

NEW QUESTION 4

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 the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
You plan to delegate encryption operations to a user.
You need to grant the user permission to implement cell-level encryption while following the principle of least privilege.
Which permission should you grant?

• A. DDLAdmin
• B. db_datawriter
• C. dbcreator
• D. dbo
• E. View Database State
• F. View ServerState
• G. View Definition
• H. sysadmin

Answer: G

Explanation:
The following permissions are necessary to perform column-level encryption, or cell-level encryption.
CONTROL permission on the database.
CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.
ALTER permission on the table.
Some permission on the key and must not have been denied VIEW DEFINITION permission. References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data

NEW QUESTION 5

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.
Start of repeated scenario.
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.

End of repeated scenario.
You need to create a backup plan for Instance4. Which backup plan should you create?

• A. Weekly full backups, nightly differential backups, transaction log backups every 30 minutes.
• B. Weekly full backups, nightly differentia
• C. No transaction log backups are necessary.
• D. Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
• E. Full backups every 60 minutes, transaction log backups every 30 minutes.

Answer: A

Explanation:
Scenario: Instance4 is engaged in heavy read-write I/O. The Recovery Point Objective of Instance4 is 60 minutes.

NEW QUESTION 6

You administer a Microsoft SQL Server 2021 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information.
No entries are found in the SQL Server log or Windows event logs related to the error. You need to identify the root cause of the issue by retrieving the error message.
What should you do?

• A. Create an Extended Events session by using the sqlserver.error_reported event.
• B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
• C. Flag all stored procedures for recompilation by using sp_recompile.
• D. Execute sp_who.

Answer: A

Explanation:
Trapping SQL Server Errors with Extended Events
One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.
To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported Etc.
References:
http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

NEW QUESTION 7

You administer a Microsoft SQL Server 2021 server. You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

• A. Query the sys.dm_db_index_usage_stats DMV.
• B. Query the sys.dm_db_missing_index_details DMV.
• C. Use the Database Engine Tuning Advisor.
• D. Query the sys.dm_db_missing_index_columns DMV.

Answer: C

Explanation:
The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server. Using the DTA, you can perform the following tasks.
Troubleshoot the performance of a specific problem query Tune a large set of queries across one or more databases
Perform an exploratory what-if analysis of potential physical design changes Manage storage space
References:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor

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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.
You need to optimize query performance. Solution: You reorganize all indexes. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. References: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

NEW QUESTION 9

You have a database named DB1 that contains two tables.
You need to encrypt one column in each table by using the Always Encrypted feature. The solution must support groupings on encrypted columns.
Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. Encrypt both columns by using deterministic encryption.
• B. Provision a symmetric key by using Transact-SQL.
• C. Encrypt both columns by using randomized encryption.
• D. Provision column master keys and column encryption keys by using Microsoft SQL Server Management Studio (SSMS).

Answer: AD

Explanation:
A: Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number.
Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns.
D: Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys.

NEW QUESTION 10

You create a Management Data Warehouse instance and enable data collection. You have an Active Directory Domain.......... DS) group named SQLDevelopers.
Members of the SQLDevelopers group must be able to run reports that show information about Microsoft SQL Server........ SQL Server activity.
You need to grant the developers permissions to run the reports by using the principle of least privilege. What should you do?

• A. In the msdb database, add SQLDevelopers to the mdwdmin role.
• B. In the msdb database, add SQLDevelopers to the mdw_reader role.
• C. In the MDW database, add SQLDevelopers to the db_datareader role.
• D. In the msdb database, add SQLDevelopers to the mdw_admin role.

Answer: B

NEW QUESTION 11

You need to validate rows before they are added to a table every time a row is added using a user-defined function.
What should you use? More than one answer may achieve the goal. Select the BEST answer.

• A. DML Trigger
• B. Default constraint
• C. Foreign key
• D. CHECK constraint

Answer: D

NEW QUESTION 12

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: “Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ‘0x7315277C70764B1F252DC7A5101F6F66EFB1069D.” You need to ensure that you can restore the database successfully.
Solution: You add the backup set password to the restore command. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
The error is related to the certificate.
References: https://dba.stackexchange.com/questions/3388/restore-encrypted-database-to-another-server?rq=1

NEW QUESTION 13

You administer a Microsoft SQL Server 2021 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?

• A. EXEC sp_addrolemember'db_procexecutor', 'BillingUsers'
• B. CREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
• C. GRANT EXECUTE ON Schema::dbo TO BillingUsers
• D. GRANT EXECUTE ON Contoso::dbo TOBillingUsers

Answer: D

NEW QUESTION 14

You are a database administrator for Contoso, LTD, You configure a Microsoft SQL Server failover with four nodes by using , Windows Server 2012 R2 Datacenter Edition and SOL Server 2021 Enterprise edition.
A server in the datacenter needs to be replaced. The server is part of the SQL Server Failover Instance (FCI). You need to remove the FCI node for the server that will be replaced.
What should you do?

• A. Evict the node from Failover Cluster Manager.
• B. Run the Remove-cluster Windows PowterShell cmdlet.
• C. Run the Remove-ClusterResource Windows PowerShell cmdlet.
• D. Remove the shared storage from Failover Cluster Manager.

Answer: A

Explanation:
Recover from an irreparable failure
Use the following steps to recover from an irreparable failure. The failure could be caused, for example, by the failure of a disk controller or the operating system. In this case, failure is caused by hardware failure in Node 1 of a two-node cluster.
After Node 1 fails, the SQL Server FCI fails over to Node 2.
Evict Node 1 from the FCI. To do this, from Node 2, open the Failover Cluster Manager snap-in, right-click Node1, click Move Actions, and then click Evict Node.
Verify that Node 1 has been evicted from the cluster definition.
Install new hardware to replace the failed hardware in Node 1.
Using the Failover Cluster Manager snap-in, add Node 1 to the existing cluster. For more information, see Before Installing Failover Clustering.
Ensure that the administrator accounts are the same on all cluster nodes.
Run SQL Server Setup to add Node 1 to the FCI. For more information, see Add or Remove Nodes in a SQL Server Failover Cluster (Setup).
References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/recover-from-failover-cluster-instance

NEW QUESTION 15

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL database that has Blob Auditing configured. You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

Does this meet the goal?

• A. YES
• B. NO

Answer: A

Explanation:
fn_get_audit_file returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-
Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

NEW QUESTION 16

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 17

You need to move a database in between servers.
You need to guarantee database users will be able to login in the database with a minimum of management effort.

• A. Application role
• B. Database user
• C. Server login
• D. Server role

Answer: B

NEW QUESTION 18

You administer all the deployments of Microsoft SQL Server 2021 in your company.
You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.
You also need to minimize the amount of storage used by the database. Which configuration should you use?

• A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
• B. SQL Server that includes an application database configured to perform transactional replication
• C. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
• D. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
• E. Two servers configured in the same data center A primary server configured to perform log-shippingevery 10 minutes A backup server configured as a warm standby
• F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• G. SQL Server that includes an application database configured to perform snapshot replication
• H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

Answer: H

Explanation:
A Windows Server Failover Cluster (WSFC) is a group of independent servers that work together to increase the availability of applications and services. SQL Server takes advantage of WSFC services and capabilities to support Always On availability groups and SQL Server Failover Cluster Instances.
References:
https://docs.micosoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-ws

NEW QUESTION 19

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

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
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 20

You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data. The database is used 24 hours each day. You implement indexes and set the value of the Auto Update Statistics option set to True.
Users report that queries take a long time to complete.
You need to identify statistics that have not been updated for a week for tables where more than 1,000 rows changed.
How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: stats_date See example below. Box 2: rowmodctr See examplebelow. Box 3: stats_date
You need to identify statistics that have not been updated for a week. Box 4: rowmodctr
You need to identify that more than 1,000 rows changed.
Rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
Example: We will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.
--Get the list of outdated statistics
SELECTOBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes
WHERE STATS_DATE (id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>0
AND id IN (SELECT object_id FROM sys.tables) GO
After collecting this information, we can decide which statistics require an update.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysindexes-transact-sq
https://www.mssqltips.com/sqlservertip/2628/how-to-find-outdated-statistics-in-sql-server-2008/

NEW QUESTION 21

You manage a Microsoft SQL Server that has a database named salesOrders. Users connect to the database by using a client application.
Users report that the application cannot connect to the database. You observe that the database storage has experienced a failure.
You need to repair the database and ensure that applications can connect to the database.
Which three action 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:
The specified database must be in single-user mode to use one of the following repair options. 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 22

You have two SQL Server 2012 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev if they do not have a login on the SQLDev instance.
What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

• A. SQL Server Integration Services (SSIS) scripts
• B. Extended Events
• C. Triggers
• D. SQL Server Analysis Services (SSAS) scripts
• E. Contained database

Answer: E

Explanation:
A fully contained database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.

NEW QUESTION 23

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stored sales data. One fact table has 100 million rows. You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You generate a new certificate on new instance. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
Certificates are of no use in this scenario.

NEW QUESTION 24

You work as a Database Administrator (DBA) at ABC.com.
You are in the process of deploying new servers running SQL Server 2012.
You need to deploy a SQL Server 2012 server to host databases used to host databases used by Research and Development department.
The databases used by the Research and Development department will store sensitive data.
A company security policy states that if Research and Development department database files are moved to another server, the files must be encrypted.
Which of the following solutions would meet the encryption requirement?

• A. Encrypting File System (EFS).
• B. Transparent Data Encryption (TDE).
• C. Windows Bitlocker Drive Encryption.
• D. Secure Sockets Layer (SSL)

Answer: B

NEW QUESTION 25

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 file storage requirements.
What should you include in the recommendation?

• A. FileStream
• B. FileTable
• C. The varbinary data type
• D. The image data type

Answer: B

Explanation:
- Scenario: 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.
- FileTables remove a significant barrier to the use of SQL Server for the storage and management of unstructured data that is currently residing as files on file servers.
Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

NEW QUESTION 26

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 add the KEEPFIXED PLAN query hint to the query.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
KEEPFIXED PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2021

NEW QUESTION 27

You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.
TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.
Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.
You need to address the performance issues with each table.
In the table below, identify the action that will resolve the issues for each table. NOTE: Make only one selection in each column.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Table A: Auto_update statistics off
Table A does not change much. There is no need to update the statistics on this table. Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON
You can set the database to update statistics asynchronously: ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS_ASYNC ON
If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments
References:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

NEW QUESTION 28

You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure.
You have a database named CorpDB. CorpDB contains a table named SalesInfo.
You discover that some table has been deleted from the SalesInfo table. You are unable to find out who deleted the information.
You need to implement a solution to monitor the deletion of any further information from the SalesInfo table. You want to minimize the development effort required for the solution.
What should you configure?

• A. You should configure table permissions.
• B. You should configure a user role.
• C. You should configure change data capture.
• D. You should configure a trigger.

Answer: C

NEW QUESTION 29

You are designing an authentication strategy for a new server that has SQL Server 2014 installed. The strategy must meet the following business requirements:
The account used to generate reports must be allowed to make a connection during certain hours only.
Failed authentication requests must be logged.
You need to recommend a technology that meets each business requirement. The solution must minimize the amount of events that are logged.
Which technologies should you recommend? To answer, drag the appropriate solution to the correct business requirement in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1. Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.
2. Login auditing can be configured to write to the error log on the following events.
- Failed logins
- Successful logins
- Both failed and successful logins

NEW QUESTION 30
......