Realistic 70-764 Braindumps 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 have a server named Server1 that has Microsoft SQL Server installed. Server1 has SQL Server Audit configured to send audit even records to a file. You need to ensure that a database user named User1 can review the audit data. Solution: You grant the VIEW SERVER STATE permission to User1.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: Each feature and command for SQL Server Audit has individual permission requirements.
Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
The VIEW SERVER STATE permission.
The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).
Membership in the sysadmin fixed server role.
The CONTROL SERVER permission.
The ALTER ANY AUDIT permission.
A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views.
References: https://technet.microsoft.com/en-us/library/cc280665(v=sql.105).aspx

NEW QUESTION 2
You have a database named DB1.
Users report that a database application that updates the data in DB1 is unresponsive. You need to identify which process prevents the application from responding.
What should you do?

• A. Run DBCC INPUTBUFFER.
• B. Query sys.dm_exec_session_wait_stats.
• C. Run sp_autostats.
• D. Run sp_who.

Answer: B

Explanation: Sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sess

NEW QUESTION 3
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environments host several customer databases.
You configure an Always On Availability Group for a customer. You must create log reports for the customer that detail when the log is flushed to disk on the primary and secondary replica.
You need to develop a report containing the requested information.
In the table below, identify the log type that you should use for each replica.
NOTE: Make only one selection in each column. Each correct selection is worth one point.

Answer:

Explanation: Flush on primary: Log flush
Log flush. Log data is generated and flushed to disk on the primary replica in preparation for replication to the secondary replica. It then enters the send queue.
Flush on secondary: Log hardened
The log is flushed on the secondary replica, and then a notification is sent to the primary replica to acknowledge completion of the transaction.

NEW QUESTION 4
You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1.
Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the Date field. The schema of Table1 is configured as shown in the following table.

Table1 contains the indexes shown in the following table.

You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend? To answer, drag the appropriate index storage type to the correct index in the answer area.

Answer:

Explanation: Index Storage Type
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
- The base table has not been partitioned.
- The index key is unique and it does not contain the partitioning column of the table.
- You want the base table to participate in collocated joins with more tables using different join columns.

NEW QUESTION 5
You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups. You need to configure the encryption options for backups.
What should you configure?

• A. a certificate
• B. an MD5 hash
• C. a DES key
• D. an AES 256-bit key

Answer: D

Explanation: To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The following are the supported encryption options:
Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

NEW QUESTION 6
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 7
Your organization is developing a web application. The application will access data from a Microsoft SQL Server database.
You must implement a security solution that meets the following requirements:
• All user logins must be associated with an Active Directory
• Service accounts are not permitted.
• Constrained database are not permitted.
• Users must not be able to log on to SQL Server as the web application and access the database.
• The web application must be permitted to display records to the and add the database.
You need 10 implement the required security and permitted structure for the web application while the principle of least privilege.
Which settings should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: Use Windows authentication
Windows authentication is usually the best choice in the following situations: There is a domain controller.
The application and the database are on the same computer. You are using an instance of SQL Server Express or LocalDB. Box 2: Database application role
An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.
Box 3: Select/Insert
The web application must be permitted to display records and add records to the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/application-roles

NEW QUESTION 8
You need to provide a group of users from the IT and Manufacturing departments the minimum administrative rights to view database information and server state for the Manufacturing database on MainDB1.
What should you do?

• A. You should configure a Database Role.
• B. You should configure a Server Role.
• C. You should configure a Shared SQL Server Login.
• D. You should configure a Local Security Group.

Answer: B

NEW QUESTION 9
You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization's SQL Server 2021 deployment.
Which of the following tools would you use to accomplish this goal?

• A. SQLIO
• B. SQLIOSim
• C. SQLIOStress
• D. chkdsk

Answer: B

Explanation: The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server.
References:
https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-

NEW QUESTION 10
You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistics updates are disabled.
Users report that some Microsoft SQL Server Reporting Service (SSRS) queries take a long time to run. You determine that the issue is caused by stale statistics for Index1.
You need to update the statistics for the index. What should you do?

• A. Set the value of the Database Read-Only property to True.
• B. Run the system stored procedure sp_updatestats.
• C. Set the value of the Auto Create Incremental Statistics property to True.
• D. Run the UPDATE STATISTICS command with the COLUMNS option.

Answer: D

Explanation: UPDATE STATISTICS updates query optimization statistics on a table or indexed view.
The ALL | COLUMNS | INDEX options update all existing statistics, statistics created on one or more columns, or statistics created for indexes. If none of the options are specified, the UPDATE STATISTICS statement updates all statistics on the table or indexed view.
Note: You can update query optimization statistics on a table or indexed view in SQL Server by using SQL Server Management Studio or Transact-SQL. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2021

NEW QUESTION 11
You administer all the deployments of Microsoft SQL Server 2021 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time. You also need to ensure that data on the primary server is unaffected.
Which configuration should you use?

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

Answer: A

Explanation: SQL Server supports the following heterogeneous scenarios for transactional and snapshot replication:
Publishing data from SQL Server to non- SQL Server Subscribers.
Publishing data to and from Oracle has some restrictions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/heterogeneous-database-replication

NEW QUESTION 12
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.
A company has a Microsoft SQL Server environment in Microsoft Azure. The databases are stored directly in Azure blob storage. The company uses a complex backup process.
You need to simplify the backup process. Future restores should not require differential or multiple incremental logs to perform a restore.
You need to design a backup solution for the SQL Server instances. Which option should you use?

• A. backup compression
• B. backup encryption
• C. file snapshot backup
• D. mirrored backup media sets
• E. SQL Server backup to URL
• F. SQL Server Managed Backup to Azure
• G. tail-log backup
• H. back up and truncate the transaction log

Answer: C

Explanation: SQL Server File-snapshot backup uses Azure snapshots to provide nearly instantaneous backups and quicker restores for database files stored using the Azure Blob storage service. This capability enables you to simplify your backup and restore policies.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-snapshot-backups-for-database-file

NEW QUESTION 13
You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID column. New rows are inserted into the table every day.
Performance of queries that filter the Product ID column have degraded significantly. You need to improve the performance of the queries.
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: Drop the clustered columnstore index
Step 2: Create a clustered rowstore index on ProductID.
Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.
Step 3: Create a nonclustered index on ProductID

Topic 2, Exam Pool B

NEW QUESTION 14
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 configure a synchronization solution to copy data from the Current_Inventory database the DesABCopAppDB database.
What should you configure?

• A. Transactional Replication.
• B. Database Mirroring.
• C. Snapshot Replication.
• D. Incremental Backups

Answer: A

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.
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 reduce the amount of time it takes to back up OperationsMain. What should you do?

• A. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
• B. Modify the backup script to use the keywords INIT and SKIP in the WITH statement.
• C. Run the following Transact-SQL statement for each file in OperationsMain: BACKUP DATABASE OperationsMain FILE […]
• D. Run the following Transact-SQL statement: BACKUP DATABASE OperationsMain READ_WRITE_FILEGROUPS

Answer: D

Explanation: READ_WRITE_FILEGROUPS specifies that all read/write filegroups be backed up in the partial backup. If the database is read-only, READ_WRITE_FILEGROUPS includes only the primary filegroup.
Scenario: Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

NEW QUESTION 16
You use SQL Server 2014.
You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in Table1.
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: