70-764 Exam
[Aug 2021] 70-462 exam preparation
Testking offers free demo for 70-462 exam preparation exam. "Administering a SQL Database Infrastructure (beta)", also known as 70-462 exam preparation exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-462 exam dumps exam, will help you answer those questions. The 70-462 exam preparation Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-462 questions and answers exams and revised by experts!
Q1. You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.
Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?
A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Answer: C
Explanation:
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read- only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
Q2. 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 and 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 the Regions table.
Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. REVOKE SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Schema::Customers FROM Sales
E. EXEC sp_droprolemember 'Sales', 'UserA'
F. REVOKE SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM UserA
J. DENY SELECT ON Object::Regions FROM Sales
Answer: J
Explanation:
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
Q3. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to be notified immediately when fatal errors occur on Server01. What should you create?
A. an Alert
B. a Server Audit Specification
C. an Extended Event session
D. a Resource Pool
E. a Policy
F. a SQL Profiler Trace
G. a Database Audit Specification
Answer: A
Q4. 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 UserA is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp droprolemember 'Sales', 'UserA'
Answer: H
Explanation:
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
Q5. 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 have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted.
You need to grant the correct permission to the service account that backs up the HRDB database.
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: G
Explanation:
Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup- restore/backup-encryption
Q6. You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours.
Users modify more than 30 percent of the data in the database during the week. Backups are performed as shown in the following schedule:
The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?
A. Perform a differential backup at 08:59 hours.
B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
C. Create a database snapshot at 08:59 hours.
D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
Answer: C
New questions:
61. HOTSPOT
You manage a Microsoft-SQL Server database named sales Orders.
You need to verify the integrity of the database and attempt to repair any errors that are found. Repair must not cause any data to be lost in the database.
How should you complete the DBCC command? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
Box 1: CHECKDB
DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database.
Partial syntax: DBCC CHECKDB
[ ( database_name | database_id | 0 [ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
….
Box 2: REPAIR_REBUILD
DBCC CHECKDB …REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST |REPAIR_REBUILD
specifies that DBCC CHECKDB repair the found errors.
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.
References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
Q7. You administer a single server that contains a Microsoft SQL Server 2012 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?
A. Use the SQL Server default instance and configure an affinity mask.
B. Install a new named SQL Server instance on the server.
C. Use the SQL Server default instance and enable Contained Databases.
D. Install a new default SQL Server instance on the server.
Answer: B
Explanation:
I would have gone with Contained Databases, but the application requires sysadmin permissions.
Q8. 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
Q9. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database.
The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:
One of the hard disk drives that stores the reporting database fails at 16:40 hours.
You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.
What should you do?
A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
B. Perform a partial restore.
C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
D. Perform a point-in-time restore.
E. Restore the latest full backup.
F. Perform a page restore.
G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
H. Restore the latest full backup. Then, restore the latest differential backup.
Answer: H
Q10. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?
A. an Extended Event session
B. a Policy
C. a Database Audit Specification
D. an Alert
E. a Server Audit Specification
F. a SQL Profiler Trace
G. a Resource Pool
Answer: F