Latest MCTS 70-432 Dumps Real Exam Download 191-200

Ensurepass

QUESTION 191

You administer a SQL Server 2008 instance that contains a database named DB1.

The DB1 database contains the following stored procedure. (Line numbers are included for

reference only.)

01 CREATE PROCEDURE Sales.Procedure1

02 AS

03 IF OBJECT_ID(‘Sales.Table1’) IS NOT NULL

04 DROP TABLE Sales.Table1;

05

06 CREATE TABLE Sales.Table1 (

07 Id int PRIMARY KEY CLUSTERED,

08 Name varchar(100);

09 );

10

11 …

12 GO

When a user named User1 attempts to invoke Procedure1, the following exception is raised:

“Msg 262, Level 14, State 1, Procedure Procedure1, Line 5

CREATE TABLE permission denied in database ‘DB1’.”

You need to provide User1 access to execute Procedure1 by allocating only the required permissions.

What should you do?

 

A. Grant the ALTER permission on the Sales schema to User1.

B. Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.

C. Insert the WITH EXECUTE AS ‘dbo’ clause between lines 01 and 02.

D. Insert the EXECUTE AS USER = ‘dbo’ statement between lines 02 and 03.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 192

You administer a SQL Server 2008 instance that runs on a Windows Server 2003 computer.

The instance uses mixed authentication mode.

You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. You also need to ensure that the password complexity rules continue to be enforced.

Which two actions should you perform?

(Each correct answer presents part of the solution. Choose two.)

 

A. Use the ALTER LOGIN … HASHED statement to modify all logins.

B. Use the ALTER LOGIN … CHECK_POLICY = ON statement to modify all logins.

C. Use the ALTER LOGIN … CHECK_EXPIRATION = ON statement to modify all logins.

D. Use Policy-Based Management to create a policy that prevents any violation of these rules.

E. Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.

 

Correct Answer: BD

Explanation/Reference:

 

 

QUESTION 193

You maintain a SQL Server 2008 instance.

You create a new SQL Agent job that includes a Windows PowerShell job step. The PowerShell job step uses the SQLCmd utility to transfer data between servers.

You need to ensure that an operator named ResponseTeam is notified by an e-mail message if the job fails.

What should you do?

 

A. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on job failure.

B. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.

C. Create the ResponseTeam operator. Assign the ResponseTeam operator as the fail safe operator. Enable the job.

D. Create the ResponseTeam operator. Configure the job step proxy account to use the ResponseTeam operator account. Select the proxy account for the e-mail profile.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 194

You work for a company which uses SQL Server 2008. You are the administrator of the company database and in charge of three SQL Server 2008 instances. All the instances run on their independent server. The three instances are respectively named InstanceA, InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal database. InstanceA currently acts as the Principal and InstanceC acts as the witness. You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server. You must make sure that the database is online on the partner that currently does not apply the patch and during this time the database does not failover to the other partner. So what action should you perform to achieve this goal?

 

A. First, you should stop the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should resume the mirroring session. Fourth, you should manually failover the mirroring session. At last, you should apply the patch to the server that runs Instance1.

B. First, you should apply the patch to the server that runs InstanceB. Second you apply the patch to the server that runs InstanceA.

C. First, you should apply the patch to the server that runs InstanceB. Second you failover the mirroring session manually. At last apply the patch to the server that runs InstanceA

D. First, you should remove the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should apply the patch to the server that runs Instance1. At last re-establish the mirroring session.

 

Correct Answer: A

Explanation/Reference: QUESTION 195

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. In the Services schema, There is a table named Service.Table. The Services.Table has to be moved from the

Sales schema to a new schema named Costs.

In the options below, which Transact-SQL statement should you execute?

 

A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;

B. ALTER SCHEMA Costs TRANSFER Service.Table;

C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;

D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;

 

Correct Answer: B

Explanation/Reference:

 

 

QUESTION 196

You work in a company which uses SQL Server 2008. You are the administrator of the company

database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

The DB1 database contains the following stored procedure.

(Line numbers are useded for reference only.)

01 CREATE PROCEDURE Sales.Procedure1

02 AS

03 IF OBJECT_ID(‘Service.Table’) IS NOT NULL

04 DROP TABLE Service.Table;

05

06 CREATE TABLE Service.Table (

07 Id int PRIMARY KEY CLUSTERED,

08 Name varchar(100);

09 );

10

11 …

12 GO

The following exception is raised when a user tries to invoke Procedure1,

“Msg 262, Level 14, State 1, Procedure Procedure1, Line 5

CREATE TABLE permission denied in database ‘DB1’.”

You should grant the user access to execute Procedure1, you must assign only the required permissions.

What action should you do perform?

 

A. Between lines 01 and 02, you should insert the WITH EXECUTE AS ‘dbo’ clause.

B. Between lines 01 and 02, you should insert the EXECUTE AS USER = ‘dbo’ statement.

C. You should give the user the ALTER permission on the Service schema

D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 197

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named named Dworks in the instance. The table below shows the two tables of the

Dworks database: There are two partitions in the BillHistory table. Partition1 is empty while partition2 contains all data. Now you intend to move data from partition2 to the appropriate partition of the BillHistory table. Partions2 contains data for the oldest month of the Bills table.

You are going to develop a process to achieve this.

You must make sure that at the end of every month the process can be repeated.

 

A. Alter the partition function of the BillHistory table by using the split option. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. Alter the BillHistory table by using the split option.

B. Alter the partition function of the Bills table by using the split option. Alter the Billstable by using the merge option.

C. Alter the partition function of the BillHistory table by using the split option. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. Alter the BillHistory table by using the merge option. Alter the Bills table by using the merge option.

D. Alter the partition function of the Bills table by using the split option. Alter the BillHistory table by using the split option. Alter the partition function of the BillHistory table by using the merge option. Alter the Bill stable by using the merge option.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 198

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a remote SQL Server 2008 instance. You notice that the application has a poor performance. On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local

SQL Server instance.

So what action should you perform to achieve this goal?

 

A. You should recapture the workload by using the data collector.

B. You should enable the XP_MSVER stored procedure on the local server.

C. You should enable the XP_MSVER stored procedure on the remote server

D. You should recapture the workload to a trace file by using SQL Profiler.

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 199

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. Now your company CIO assigns a task to you.

The company CIO wants you to configure FILESTREAM data and the two requirements below must be met: you must enable FILESTREAM for file I/O streaming access; remote client computers must be able to have streaming access to FILESTREAM data.

You must make sure that FILESTREAM data is enabled.

Which service should you configure?

 

A. You should configure SQL Server VSS Writer

B. You should configure Distributed File System

C. You should configure SQL Server

D. You should configure SQL Server Full Text

 

Correct Answer: C

Explanation/Reference:

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL

Server Database Engine.

This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.

To enable and change FILESTREAM settings

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to

Configuration Tools, and then click SQL Server Configuration Manager.

In the list of services, right-click SQL Server Services, and then click Open.

In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

Right-click the instance, and then click Properties.

In the SQL Server Properties dialog box, click the FILESTREAM tab.

Select the Enable FILESTREAM for Transact-SQL access check box.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/Ostreaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

Click Apply.

In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

 

QUESTION 200

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the instance. The Dworks database has the Products table.

Now you have to replicate the Products table to the SQL Server instances at remote locations, so you create a Merge Replication topology and a Publication to do this.

The Publication has a 21-day retention period. There is a user who goes on a business trip, 30 days later he comes back and finds that data in his database is not the latest.

The Windows Event log states the following error message:

“Replication: expired subscription dropped.”

You must get the latest data in the user’s database and make sure that future data changes are appropriately replicated.

So what action should you perform to achieve this goal?

 

A. You should recreate the publication

B. You should upload unsynchronized changes.

C. You should reinitialize the publication and generate a new snapshot at once.

D. You should upload unsynchronized changes, and then reinitialize the publication

 

Correct Answer: C

Explanation/Reference: Merge replication uses the publication retention period (the @retention and @retention_period_unit parameters of sp_addmergepublication (Transact-SQL)). When a subscription expires, it mustbe reinitialized, because metadata for the subscription is removed. Subscriptions that are not reinitialized are dropped by the Expired subscription clean up job that runs on the Publisher. By default, this job runs daily; it removes all push subscriptions that have not synchronized for double the length of the publication retention period. For example: If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days. If the Publisher is running SQL Server 2005 or a later version and the agent for the subscription is from SQL Server 2005 or a later version, a subscription only expires if there have been changes to the data in that subscription’s partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only

if there have been changes to the German customer data in the last 14 days.

From 14 days to 27 days after the last synchronization, the subscription can be reinitialized.

At 28 days after the last synchronization, the subscription is dropped by the Expired subscription clean up job. If a push subscription expires, it is completely removed, but pull subscriptions are not.

You must clean up pull subscriptions at the Subscriber. For more information, see How to: Delete a Pull

Subscription (Replication Transact-SQL Programming).

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.