[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 41-50

Get Full Version of the Exam
http://www.EnsurePass.com/70-765.html

Question No.41

You administer a SQL Server 2014 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 following requirements are met:

Which Transact-SQL statement should you use?

  1. REVOKE SELECT ON Schema::Customers FROM UserA

  2. DENY SELECT ON Object::Regions FROM UserA

  3. EXEC sp_addrolemember #39;Sales#39;, #39;UserA#39;

  4. DENY SELECT ON Object::Regions FROM Sales

  5. REVOKE SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM Sales

  7. DENY SELECT ON Schema::Customers FROM UserA

  8. EXEC sp_droprolemember #39;Sales#39;, #39;UserA#39;

  9. REVOKE SELECT ON Object::Regions FROM Sales

  10. REVOKE SELECT ON Schema::Customers FROM Sales

Correct Answer: J

Explanation:

Use REVOKE to remove the grant or deny of a permission.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql- data-warehouse-parallel-data-warehouse

Question No.42

You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:

You need to grant users access according to the following requirements: What should you do?

  1. Create a database role called Employees.Add CORP\Employees to the db_datareader role. Add all company employees except HR administrators to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.

  2. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role. Add all HR administrators to the HRAdmins role. Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.

  3. Create two database roles: Employees and HRAdmins. Add all company employees to the Employees role.Add HR administrators to the HRAdmins role. Grant SELECT access to all tables except SalaryDetails to the Employees role. Grant SELECT access to the SalaryDetails table to the HRAdmins role. Deny SELECT access to the SalaryDetails table to the Employees role.

  4. Create a database role called Employees.Add all HR administrators to the db_datareader role. Add all company employees to the Employees role. Grant SELECT access to all tables except the SalaryDetails table to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.

Correct Answer: D

Explanation:

Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

References:

https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

Question No.43

You administer a single server that contains a Microsoft SQL Server 2014 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?

  1. Use the SQL Server default instance and configure an affinity mask.

  2. Install a new named SQL Server instance on the server.

  3. Use the SQL Server default instance and enable Contained Databases.

  4. Install a new default SQL Server instance on the server.

Correct Answer: B

Explanation:

https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances- of-sql-server

Question No.44

You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.

You have multiple queries that read data and produce reports from the database.

You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica.

What should you do?

  1. Set the Availability Mode property of HA/Server02 to Asynchronous commit.

  2. Set the Readable Secondary property of HA/Server02 to Read-intent only.

  3. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.

  4. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

Correct Answer: B

Explanation:

To set up a readable secondary replica, you first create an availability group. Then you add replicas.

You can choose either Yes or Read-intent only options.

image

References:

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

Question No.45

You administer a Microsoft SQL Server 2014 server. You plan to deploy new features to an application.

You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.

What should you do?

  1. Query the sys.dm_db_index_usage_stats DMV.

  2. Query the sys.dm_db_missing_index_details DMV.

  3. Use the Database Engine Tuning Advisor.

  4. Query the sys.dm_db_missing_index_columns DMV.

Correct Answer: C

Explanation:

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server.

Question No.46

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.

You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.

What should you create?

  1. An Alert

  2. A Resource Pool

  3. An Extended Event session

  4. A Server Audit Specification

  5. A SQL Profiler Trace

  6. A Database Audit Specification

  7. A Policy

  8. A Data Collector Set

Correct Answer: C

Explanation:

SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

Extended Events is a light weight performance monitoring system that uses very few performance resources.

A SQL Server Extended Events session is created in the SQL Server process hosting the Extended Events engine.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

Question No.47

You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

  1. STANDBY

  2. Differential

  3. FULL

  4. CHECKSUM

  5. BULK_LOGGED

  6. CONTINUE_AFTER_ERROR

  7. SIMPLE

  8. DBO_ONLY

  9. COPY_ONLY

  10. SKIP

  11. RESTART

  12. Transaction log

  13. NO_CHECKSUM

  14. NORECOVERY

Correct Answer: F

Explanation:

The CONTINUE_AFTER_ERROR option, of the Transact-SQL BACKUP command, instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

Question No.48

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?

  1. REVOKE SELECT ON Schema::Customers FROM UserA

  2. DENY SELECT ON Object::Regions FROM UserA

  3. EXEC sp_addrolemember #39;Sales#39;, #39;UserA#39;

  4. DENY SELECT ON Object::Regions FROM Sales

  5. REVOKE SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM Sales

  7. DENY SELECT ON Schema::Customers FROM UserA

  8. EXEC sp_droprolemember #39;Sales#39;, #39;UserA#39;

  9. REVOKE SELECT ON Object::Regions FROM Sales

  10. REVOKE SELECT ON Schema::Customers FROM Sales

Correct Answer: E

Explanation:

Use REVOKE to remove the grant or deny of a permission. References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql- data-warehouse-parallel-data-warehouse

Question No.49

You administer a Microsoft SQL Server 2014 database named Contoso on a server named

Server01.

You need to be notified immediately when fatal errors occur on Server01. What should you create?

  1. A Database Audit Specification

  2. A Policy

  3. An Alert

  4. A SQL Profiler Trace

  5. A Resource Pool

  6. An Extended Event session

  7. A Server Audit Specification

Correct Answer: C

Explanation:

SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don#39;t refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.

image

References:

https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/

Question No.50

You administer a Microsoft SQL Server 2014 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:

image

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?

  1. Perform a differential backup at 08:59 hours.

  2. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at17:01 hours.

  3. Create a database snapshot at 08:59 hours.

  4. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at08:59 hours.

  5. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.

  6. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.

Correct Answer: C

Explanation:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql- server

Get Full Version of the Exam
70-765 Dumps
70-765 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>