Microsoft 70-764 Study Guides 2021

NEW QUESTION 1
You administer all the deployments of Microsoft SQL Server 2021 in your company.
You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off- loaded from the primary database to another server.
You also need to be able to add indexes to the secondary 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. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
• C. 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
• D. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
• E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• F. SQL Server that includes an application database configured to perform transactional replication
• 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: F

Explanation: References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication

NEW QUESTION 2
Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners
will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production.
Requirements Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.
The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications.
You need to recommend a feature to support your backup solution. What should you include in the recommendation?

• A. Transparent Data Encryption (TDE)
• B. Column-level encryption
• C. An NTFS file permission
• D. A Secure Sockets Layer (SSL)

Answer: A

Explanation: - Scenario: You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
- Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.
Transparent Data Encryption (TDE)

NEW QUESTION 3
Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners
will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production.
Requirements Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.
The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications.
You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements.
What should you recommend?

• A. Create stored procedures that use EXECUTE AS clauses.
• B. Create a user-defined database role and add users to the role.
• C. Create functions that use EXECUTE AS clauses.
• D. Create a Policy-Based Management Policy.

Answer: A

Explanation: - EXECUTE AS Clause (Transact-SQL)
In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

NEW QUESTION 4
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 meet the design requirement for the ProductTypes table in the Product database. Which of the following would be the best solution?

• A. A PRIMARY KEY constraint.
• B. A CHECK constraint.
• C. A UNIQUE constraint.
• D. A Data Definitions Language (DDL) trigger.
• E. A FOREIGN KEY constraint.

Answer: B

NEW QUESTION 5
You manage a sales database that uses the full recovery model. You use the following backup schedule:

The date is April 15, 2021. A developer creates a transaction named modificationtest and runs the transaction at 9:20 AM.
You determine that the change implemented by the transaction must be undone. You immediately back up the database log to L:LogbackupSalestaillog.bak using the NORECOVERY and INIT options. Then, you restore the full database backup from the previous night using the NORECOVERY option.
You need to recover as much data as possible without recovering the change implemented by the transaction. How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL
segments in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

NEW QUESTION 6
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 a server that runs Microsoft SQL Server 2021 Web edition. The server has a default instance that hosts a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to a named instance on a server that runs Microsoft SQL Server 2021 Enterprise edition.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2021 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

NEW QUESTION 7
You plan to deploy three highly available SQL Server environments that will use SQL Server 2014. You identify the following specifications for each environment as shown following table.

You need to recommend which high-availability feature is required for each environment.
Which features should you identify? To answer, drag the appropriate feature to the correct requirement in the answer area.

Answer:

Explanation: ENV1 - Database mirroring; ENV2 - AlwaysOn availability groups; ENV3 - AlwaysOn availability groups
- AlwaysOn availability groups
AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster.

NEW QUESTION 8
You are creating a database that will store usernames and credit card numbers for an application. You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Data encryption
• B. Transparent Data Encryption (TDE)
• C. Encrypting File System (EFS)
• D. Data hashing

Answer: A

Explanation: If we are going to encrypt credit card number for storage, then we should have Data Encryption Key(DEK) for encrypting the credit card number.

NEW QUESTION 9
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 10
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.
One instance hosts a user database named HRDB. The database contains sensitive human resources data. You need to grant an auditor permission to view the SQL Server audit logs 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 Server State
• G. View Definition
• H. sysadmin

Answer: F

Explanation: Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
Membership in the sysadmin fixed server role.
The CONTROL SERVER permission.
The VIEW SERVER STATE permission.
The ALTER ANY AUDIT permission.
The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).
References: https://technet.microsoft.com/en-us/library/cc280386(v=sql.110).aspx

NEW QUESTION 11
You plan to integrate an on-premises Microsoft SQL Server environment with Microsoft Azure. You need to create the authentication object so that you can connect to Azure.
Which Windows PowerShell command or commands should you run?

• A. Invoke-Sqlcmd “CREATE EXTERNAL DATA SOURCE MyAzureStorage WITH (LOCATION = ‘wasbs://Azure@myaccount.blob.core.windows.net/”, CREDENTIAL = Pa$$w0rd)”
• B. New-SqlAzureKeyVaultColumnMasterKeySettings-KeyUrlhttps://myvault.vault.contoso.net:443/keys/C
• C. Invoke-Sqlcmd “CREATE CREDENTIAL AzureCred WITH IDENTITY = ‘AzureKey’, SECRET = ‘Pa$$w0rd”
• D. Invoke-Sqlcmd “CREATE LOGIN AzureCred WITH CREDENTIAL = ‘AzureKey’, PASSWORD = ‘Pa$$w0rd”

Answer: C

Explanation: Invoke-Sqlcmd runs a script containing statements supported by the SQL Server SQLCMD utility.
The following example creates a SQL Server credential for the Database Engine to use when accessing the Azure Key Vault using the SQL Server Connector for Microsoft Azure Key Vault.
CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'ContosoKeyVault',
SECRET = 'EF5C8E094D2A4A769998D93440D8115DSECRET_DBEngine'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql

NEW QUESTION 12
You manage a Microsoft SQL Server environment. You plan to configure Database Mail. You need to ensure that all users can access a private Database Mail profile.
What should you do?

• A. On the tempdb database, add the DatabaseMailUserRole to userid 0.
• B. On the msdb database, add the DatabaseMailUserRole to the public user.
• C. On the master database, add the DatabaseMailUserRole to the public user.
• D. On the profile, grant access to the public user.

Answer: B

Explanation: For each private profile, Database Mail maintains a list of users that are permitted to send e-mail using that profile. Public profiles are available to users or roles in the msdb database who are also members of the DatabaseMailUserRole.
By default, a profile is private, and no users are granted access to the profile. To make the profile public, grant access to the user 'public' or the user id 0.
Note: Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.
References: https://technet.microsoft.com/en-us/library/ms189879(v=sql.105).aspx

NEW QUESTION 13
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet 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 14
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. Reduce the Fill factor of all the clustered indexes.
• B. Reduces the number of table variales used in the application.
• C. Use identity integers as primary key fields in the tables.
• D. Move the database files to SSD storage.

Answer: C

NEW QUESTION 15
You have a test server that contains a database named DB1. Backups of the database are written to a single backup device. The backup device has a full, differential, and transaction log backup.
You discover that the database is damaged. You restore the database to the point at which the differential backup was taken.
You need to rebuild the database with data stored in the latest transaction logs.
How should you complete the Transact-SQL statement? To answer. drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation: Box 1: RESTORE
Box 2: RECOVERY
The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database.
References:
https://www.mssqltips.com/sqlservertutorial/112/recovering-a-database-that-is-in-the-restoring-state/

NEW QUESTION 16
Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners
will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production.
Requirements Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.
The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications.
During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. What should you recommend?

• A. Partition the nonclustered index.
• B. Partition the Inventory table.snapshot replication
• C. Create a column store index.Master Data Services
• D. Drop the clustered index.change data capture

Answer: A

Explanation: Scenario:
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data. The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.
The column that is used as the primary key will use the identity property.