Free Microsoft 70-457 Practice Test & Real Exam Questions, Page 3

Question #27

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

Discussion 0

Correct Answer: F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #28

DRAG AND DROP You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN). Connections between offices vary significantly in both bandwidth and reliability. You need to identify the correct replication method for each scenario. What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)
Select and Place:

Discussion 0

Correct Answer:

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms151198.aspx

Question #29

Question #30

You administer a Microsoft SQL Server 2012 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost.
Data can be recovered by using the minimum amount of administrative effort.
What should you do? Choose all that apply.

A. Configure the database to use the SIMPLE recovery model.

B. Create a FULL database backup every 24 hours.

C. Configure the database to use the FULL recovery model.

D. Create a DIFFERENTIAL database backup every 4 hours.

E. Create a DIFFERENTIAL database backup every 24 hours.

F. Create a LOG backup every 5 minutes.

Discussion 0

Correct Answer: B,C,D,F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #31

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 on the same subnet * SQL Server Availability Group configured in Synchronous-Commit Availability Mode

B. * Two servers configured in different data centers * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

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

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

E. * SQL Server that includes an application database configured to perform transactional replication

F. * Two servers configured in a Windows Failover Cluster in the same data center * SQL Server configured as a clustered instance

G. * Two servers configured in different data centers * SQL Server Availability Group configured in Synchronous-Commit Availability Mode * One server configured as an Active Secondary

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

Discussion 0

Correct Answer: F Vote an answer

Question #32

DRAG AND DROP You administer a Microsoft SQL Server database that is used by an application. Users of the application report performance issues. You need to choose the appropriate tool for performance-tuning of SQL Server databases. Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Select and Place:

Discussion 0

Correct Answer:

Explanation:
--Burgos - YES
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/bb630282.aspx Reference: http://msdn.microsoft.com/en-us/library/ms191246.aspx Reference: http://msdn.microsoft.com/en-us/library/ms181091.aspx

Question #33

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

A. ALTER TABLE Orders ADD CONSTRAINT CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))

B. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
Orders (CustomerID)

C. ALTER TABLE Customer ADD OrderID INT NOT NULL;
ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);

D. ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)

E. ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #34

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

A. ENCRYPTBYKEY

B. ENCRYPTBYPASSPHRASE

C. ENCRYPTBYCERT

D. ENCRYPTION

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #35

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. ALTER INDEX ALL ON OrderDetail REBUILD

B. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

C. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #36

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)
- - Create Audit Records

B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted)
- - Create Audit Records

C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber)
- - Create Audit Records

D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber)
- - Create Audit Records

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #37

You administer a Microsoft SQL Server 2012 instance. You need to stop a blocking process that has an SPID of 64 without stopping other processes. What should you do?

A. Execute the following Transact-SQL statement:
EXECUTE sp_KillSPID 64

B. Execute the following Transact-SQL statement:
ALTER SESSION KILL '64'

C. Restart the SQL Server service.

D. Execute the following Transact-SQL statement:
KILL 64

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #38

You administer a Microsoft SQL Server 2012 default instance. The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server. You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. What should you do? Choose all that apply.

A. Execute the following Transact-SQL command: sp_configure 'remote admin connections',

B. Restart the SQL Server Agent Service.

C. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.

D. Execute the Reconfigure command.

E. Execute the following Transact-SQL command: sp_configure 'remote access', 1

F. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.

Discussion 0

Correct Answer: A,D,F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #39

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. Install a new default SQL Server instance on the server.

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

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

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

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Free Microsoft 70-457 Practice Test & Real Exam Questions

Download Free Microsoft 70-457 Demo