70-764 Exam
How to pass 70-462 exam preparation in Sep 2021
It is impossible to pass Microsoft 70-462 exam preparation exam without any help in the short term. Come to Exambible soon and find the most advanced, correct and guaranteed Microsoft 70-462 exam preparation practice questions. You will get a surprising result by our Latest Administering a SQL Database Infrastructure (beta) practice guides.
Q11. 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.
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 than runs Microsoft SQL
Server 2021 Standard edition.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
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
Q12. HOTSPOT
You are configuring log shipping for a Microsoft SQL Server database named salesOrders. You run the following Transact-SQL script:
You need to determine the changes that the script has on the environment.
How does the script affect the environment? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Box 1: is
The dedicated backup file share is \\localhost\Backup
Box 2: does not run
The only thing with a name related to ADATM-SQL11 is the schedule name.
Box 3: 72 hours
4320 minutes equals 72 hours.
Note: @backup_retention_period= ] backup_retention_period
Isthe length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_period is int, with no default, and cannot be NULL.
Box 4: 15 minutes.
[ @freq_subday_type = ] freq_subday_type
Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.
Here it is 4, which means minutes.
[ @freq_subday_interval = ] freq_subday_interval
The number of freq_subday_type periods to occur between eachexecution of a job. freq_subday_intervalis int, with a default of 0. Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal
to 1.
Here it is 15.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-log-shipping-primary-database-transact-sql
Q13. You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.
You need to restore the DB1 database to the point in time of May 25, 2021 at 02:23 and ensure that the database is functional and starts to accept connections.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q14. A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups.
DB1 experiences a failure. You must restore FG1 and then FG2.
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?
A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
C. the WITH RECOVERY clause for both FG1 and FG2
D. the WITH NORECOVERY clause for both FG1 and FG2
Answer: A
Q15. You administer a SQL Server 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. DENY SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Object::Regions FROM Sales
E. REVOKE SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Schema::Customers FROM UserA
H. EXEC sp_droprolemember 'Sales', 'UserA'
I. REVOKE SELECT ON Object::Regions FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales
Answer: F
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx
Q16. DRAG DROP
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
Q17. You administer all the deployments of Microsoft SQL Server 2012 in your company.
A database contains a large product catalog that is updated periodically.
You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?
A. • 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
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 Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance
E. • SQL Server that includes an application database configured to perform snapshot replication
F. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
G. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
H. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
Answer: E
Explanation:
I think that i'll stick with "E" as that was the original answer and "You need to be able to send the entire product catalog to all branch offices on a monthly basis. " definatly sounds like it wants snapshot
Q18. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to prevent users from disabling server audits in Server01. What should you create?
A. an Alert
B. a Resource Pool
C. an Extended Event session
D. a Database Audit Specification
E. a SQL Profiler Trace
F. a Server Audit Specification
G. a Policy
Answer: G
Q19. 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 are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.
You need to monitor WingDB and gather information for troubleshooting issues. What should you use?
A. sp_updatestats
B. sp_lock
C. sys.dm_os_waiting_tasks
D. sys.dm_tran_active_snapshot_database_transactions
Answer: B
Explanation:
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its
information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql
Q20. 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.
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