[Sep 2017] 70-462 exam dumps

Want to know Testking 70-462 exam preparation Exam practice test features? Want to lear more about Microsoft Administering a SQL Database Infrastructure (beta) certification experience? Study Free Microsoft 70-462 exam dumps answers to Most up-to-date 70-462 questions and answers questions at Testking. Gat a success with an absolute guarantee to pass Microsoft 70-462 exam dumps (Administering a SQL Database Infrastructure (beta)) test on your first attempt.

Q11. 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


Q12. You administer a Microsoft SQL Server 2012 instance.

After a routine shutdown, the drive that contains tempdb fails.

You need to be able to start the SQL Server. What should you do?

A. Modify tempdb location in startup parameters.

B. Start SQL Server in minimal configuration mode.

C. Start SQL Server in single-user mode.

D. Configure SQL Server to bypass Windows application logging.

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms186400.aspx

http://msdn.microsoft.com/en-us/library/ms345408.aspx


Q13. 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 2016 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 2016 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 2016 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


Q14. DRAG DROP

You are configuring a new Microsoft SQL Server Always On Availability Group. You plan to configure a shared network location at \\DATA-CI1\SQL.

You need to create an availability group listener named AGL1 on port 1433.

In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation:

Step 1: Launch the Failover Cluster Manager and..

To support theAlways On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets requirements including:

* Ensure that each computer is a node in a WSFC (Windows Server Failover Clustering).

Step 2: Add andconfigure the replica and…

All the server instances that host availability replicas for an availability group must use the same SQL Server collation.

Step 3: Enable the SQL Server 2016 Always On Availability Group feature.

Enable the Always On availability groups feature on each server instance that will host an availability replica for any availability group. On a given computer, you can enable as many server instances for Always On availability groups as your SQL Server installation supports.

Step 4: Create the Always On Availability Group and..

Using Transact-SQL to create or configure an availability group listener

Step 5: Select the Full data synchronization method and… References:


Q15. HOTSPOT

You manage a Microsoft SQL Server environment. A server fails and writes the following event to the application event log:

MSG_AUDIT_FORCED_SHUTDOWN

You configure the SQL Server startup parameters as shown in the following graphic:

Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Box 1: single-user

The startup option -m starts an instance of SQL Server in single-user mode.

Box 2: sysadmin

Starting SQL Server in single-user mode enables anymember of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

References:https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options


Q16. 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 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.

The wait statistics monitoring requirements for the instances are described in the following table.

You need to reduce the amount of time it takes to backup OperationsMain. What should you do?

A. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.

B. Modify the backup script to use the keyword SKIP in the WITH statement

C. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.

D. Modify the full database backups script to stripe the backup across multiple backup files.

Answer: D

Explanation:

One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.

From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat 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.

References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server


Q17. 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


Q18. 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:


Q19. 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.

Clients connect to databases by using line-of-business applications. Developers connect by using SQL Server Management Studio (SSMS).

You need to provide permissions to a service account that will be used to provision a new database for a client.

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

Explanation:

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

References:https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles


Q20. You administer a SQL 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 remove the Select permission for UserA on the Regions table.

You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. Which Transact-SQL statement should you use?

A. DENY SELECT ON Object::Regions FROM UserA

B. DENY SELECT ON Schema::Customers FROM UserA

C. EXEC sp_addrolemember 'Sales', 'UserA'

D. REVOKE SELECT ON Object::Regions FROM UserA

E. REVOKE SELECT ON Object::Regions FROM Sales

F. EXEC sp_droproiemember 'Sales', 'UserA'

G. REVOKE SELECT ON Schema::Customers FROM UserA

H. DENY SELECT ON Object::Regions FROM Sales

I. DENY SELECT ON Schema::Customers FROM Sales

J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: D

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