[Free] Download New Updated (February 2016) Microsoft 70-458 Practice Tests 81-90

Ensurepass

QUESTION 81

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’

 

Correct Answer: H

 

 

 

QUESTION 82

DRAG DROP

You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a

database named AdventureWorks. You need to prepare the AdventureWorks database for

database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA.

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.

 

Select and Place:

clip_image002

 

Correct Answer:

clip_image004

 

 

QUESTION 83

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains

a table named Suppliers and a column named IsActive in the Purchases schema. You create a new

user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table.

You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also

need to grant ContosoUser only the minimum required permissions. Which Transact-SQL

statement should you use?

 

A.       GRANT DELETE ON Purchases.Suppliers TO Contoso User

B.       CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

WITH EXECUTE AS USER = ‘dbo’

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C.       GRANT SELECT ON Purchases.Suppliers TO ContosoUser

D.       CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

 

Correct Answer: B

 

 

 

 

 

 

 

QUESTION 84

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail.

You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need

to reduce fragmentation. You need to achieve this goal without taking the index offline. Which

Transact-SQL batch should you use?

 

A.       CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP

EXISTING

B.       ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

C.       ALTER INDEX ALL ON OrderDetail REBUILD

D.       ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

 

Correct Answer: B

 

 

QUESTION 85

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to

ensure that an OLTP database that uses a storage area network (SAN) remains available if any of

the servers fail. You also need to minimize the amount of storage used by the database. Which

configuration should you use?

 

A.       Two servers configured in different data centers

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

One server configured as an Active Secondary

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 different data centers

SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

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

F.        Two servers configured on the same subnet

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

G.       SQL Server that includes an application database configured to perform snapshot replication

H.  Two servers configured in a Windows Failover Cluster in the same data center

SQL Server configured as a clustered instance

 

Correct Answer: H

 

 

 

QUESTION 86

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

C.       SQL Server that includes an application database configured to perform snapshot replication

D.       Two servers configured in different data centers

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

One server configured as an Active Secondary

E.       Two servers configured on the same subnet

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

F.        Two servers configured in different data centers

SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

G.       SQL Server that includes an application database configured to perform transactional

replication

H.       Two servers configured in a Windows Failover Cluster in the same data center

SQL Server configured as a clustered instance

 

Correct Answer: C

 

 

QUESTION 87

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to

ensure that data changes are sent to a non-SQL Server database server in near real time. You also

need to ensure that data on the primary server is unaffected. Which configuration should you use?

 

A.       SQL Server that includes an application database configured to perform transactional

replication

B.       Two servers configured in different data centers

SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

C.       Two servers configured in different data centers

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

One server configured as an Active Secondary

D.       SQL Server that includes an application database configured to perform snapshot replication

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

F.        Two servers configured on the same subnet

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

G.       Two servers configured in a Windows Failover Cluster in the same data center

SQL Server configured as a clustered instance

H. &n
bsp;    
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

 

Correct Answer: A

 

 

QUESTION 88

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to

ensure that an OLTP database that includes up-to-the-minute reporting requirements can be

offloaded from the primary database to another server. You also need to be able to add indexes

to the secondary database. Which configuration should you use?

 

A.       Two servers configured in different data centers

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

One server configured as an Active Secondary

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

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

D.       Two servers configured in different data centers

SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

E.       Two servers configured on the same subnet

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

F.        SQL Server that includes an application database configured to perform transactional

replication

G.       SQL Server that includes an application database configured to perform snapshot replication

H.       Two servers configured in a Windows Failover Cluster in the same data center

SQL Server configured as a clustered instance

 

Correct Answer: A

 

 

 

 

 

 

QUESTION 89

You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two

servers in the same data center that hosts your production database. You need to ensure that the

database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention. Which configuration should you use?

 

A.       Two servers configured in a Windows Failover Cluster in the same data center

SQL Server configured as a clustered instance

B.       SQL Server that includes an application database configured to perform transactional

replication

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

D.       Two servers configured in different data centers

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

One server configured as an Active Secondary

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

F.        Two servers configured in different data centers

SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

G.       SQL Server that includes an application database configured to perform snapshot replication

H.       Two servers configured on the same subnet

SQL Server Availability Group configured in Synchronous-Commit Availability Mode

 

Correct Answer: H

 

 

QUESTION 90

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales

schema. Some users must be prevented from deleting records in any of the tables in the Sales

schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What

should you do?

 

A.       Create a custom database role that includes the users. Deny Delete permissions on the Sales

schema for the custom database role.

B.       Include the Sales schema as an owned schema for the db_denydatawriter role. Add the

users to the db_denydatawriter role.

C.       Deny Delete permissions on each table in the Sales schema for each user.

D.       Create a custom database role that includes the users. Deny Delete permissions on each

table in the Sales schema for the custom database role.

 

Correct Answer: A

 

Free VCE & PDF File for Microsoft 70-458 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…