Tips for 70-462 questions and answers

Master the 70-462 questions and answers Administering a SQL Database Infrastructure (beta) content and be ready for exam day success quickly with this Examcollection 70-462 exam preparation answers. We guarantee it!We make it a reality and give you real 70-462 questions and answers questions in our Microsoft 70-462 exam dumps braindumps.Latest 100% VALID Microsoft 70-462 questions and answers Exam Questions Dumps at below page. You can use our Microsoft 70-462 exam dumps braindumps and pass your exam.

2017 NEW RECOMMEND

Free VCE & PDF File for Microsoft 70-764 Real Exam
(Full Version!)

Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions

Free Instant Download NEW 70-764 Exam Dumps (PDF & VCE):
Available on:
http://www.certleader.com/70-764-dumps.html

Q1. 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 manage a Microsoft SQL Server environment. You implement Transparent Data Encryption (TDE).

A user will assist in managing TDE.

You need to ensure that the user can view the TDE metadata while following the principle of lease 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: G

Explanation:

Viewing the metadata involved with TDE requires the VIEW DEFINITION permissionon the certificate.

References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde

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

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

Q4. 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 need to configure a Microsoft SQL Server instance to ensure that a user named Mail1 can send mail by using Database Mail.

Solution: You add the DatabaseMailUserRole to Mail1 in the tempdb database. Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database, not the tempdb database, in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges.

Note: Database Mail was first introduced as a new feature in SQLServer 2005 and replaces

the SQL Mail feature found in previous versions.

References:http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml

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

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

Q6. 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 than runs Microsoft SQL

Server 2016 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 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

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

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

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

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

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

Certleader Dumps
Certleader is a company specialized on providing high quality IT exam materials and fully committed to assist our respected clients crack any IT certification tests on their 1st efforts.