Latest MCTS 70-432 Dumps Real Exam Download 11-20

Ensurepass

QUESTION 11

You administer a Microsoft SQL Server 2008 instance that has TCP/IP enabled.

You need to verify the port that the SQL Server instance listens on.

What should you do?

 

A. Open SQL Server Configuration Manager Expand the SQL Server Network Configuration services. Select the Protocols for (InstanceName) service. In the right panel, right click TCP/IP, select the Properties option, and then select the IP Addresses tab.

B. Open SQL Server Configuration Manager Expand the SQL Native Client 10.0 Configuration service. Select the Client Protocols service. Set the Properties option.

C. Open SQL Server Configuration Manager Select the SQL Server Service service. In the right panel, right click SQL Server (InstanceName), right-click Properties and then select the Advanced tab.

D. Open SQL Server Configuration Manager Select the SQL Server Service service. In the right panel, right click SQL Server Browser, right-click Properties, and then select the Advanced tab.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 12

You administer a database for an online ordering system.

You plan to create a disaster recovery plan that uses transaction log backups by using only Microsoft SQL

Server native tools.

You need to ensure that the transaction log backup schedule meets the following requirements:

Log backups occur every 15 minutes between 9:00 hours and 17:00 hours.

Log backups occur every hour between 17:00 hours and 9:00 hours.

What should you do?

 

A. Create two SQL Server Integration Services (SSIS) packages by using a single schedule.

B. Create a single SQL Server Integration Services (SSIS) package by using two schedules.

C. Insert a new row to dm_os_schedulers.

D. Create a database maintenance plan by using the Database Maintenance Wizard.

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 13

You administer a Microsoft SQL Server 2008 R2 instance by using a database named

AdventureWorks2008R2.

You need to implement transparent data encryption for a database hosted by the server.

Which four Transaction-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Select and Place: 

clip_image002

Correct Answer:

clip_image004

 

QUESTION 14

You administer two SQL Server 2008 instance that contains a database named DB1. DB1 contains a table named Sales.Table1.

You need to obtain the effective permissions of a user named User1 to access the Sales.Table1 table.

Which Transact-SQL statement(s) should you execute in the DB1 database?

 

A. EXEC sp_helprotect ‘Sales.Table1’, ‘User1’;

B. EXECUTE AS USER = ‘User1’; SELECT * FROM sys.fn_my_permissions(‘Sales.Table1’, ‘OBJECT’);

C. SELECT * FROM sys.database_permissions WHERE class_desc = OBJECT_ID(‘Sales.Table1’) AND grantee_principal_id = USER_ID(‘User1’);

D. SELECT * FROM sys.sysprotects WHERE uid = USER_ID(‘User1’) AND id = OBJECT_ID(‘Sales.Table1’);

 

Correct Answer: B

Explanation/Reference:

http://msdn.microsoft.com/en-us/library/ms176097(v=sql.105).aspx

 

 

QUESTION 15

You administer a Microsoft SQL Server 2008 R2 database installed along with the default settings.

You want to migrate a database from a SQL Server 2000 server that is being decommissioned. The application executes a number of Windows command-line calls from stored procedures in the database.

You need to configure SQL Server 2008 R2 to allow command-line calls from the database.

Which three actions should you perform in sequence? (To answer, move the appropriate actions (steps) from the list of actions to the answer area and arrange them in the correct order.)

[Note: TO ANSWER, Click “SELECT and PLACE”. Press “Enter” when done.]

Select and Place:

clip_image006

Correct Answer:

clip_image008Explanation/Reference:

 

 

QUESTION 16

You administer two SQL Server 2008 instances named Instance1 and Instance2.

A database named Customers resides on Instance1. You move the Customers database from Instance1 to Instance2.

The user attempts to access the Customers database on Instance2 by using the SQL Server login User1.

However, the user receives an error message which indicates that the access to the Customers database is denied.

You need to ensure the User1 can access the Customers database.

Which Transact-SQL statements should you execute on Instance2?

 

A. USE Customers; ALTER USER User1 ENABLE;

B. USE Customers; ALTER LOGIN User1 WITH PASSWORD = ‘111999’ UNLOCK;

C. USE Customers; ALTER USER User1 WITH LOGIN = User1;

D. USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;

 

Correct Answer: C

Explanation/Reference:

From MSDN: http://msdn.microsoft.com/en-us/library/ms176060.aspx

ALTER USER : Renames a database user or changes its default schema.

ALTER USER userName

WITH <set_item> [ ,…n ]

<set_item> ::=

NAME = newUserName | DEFAULT_SCHEMA = schemaName | LOGIN = loginName |

PASSWORD = ‘password’ [ OLD_PASSWORD = ‘oldpassword’ ]

Arguments:

userName

Specifies the name by which the user is identified inside this database.

LOGIN = loginName

Re-maps a user to another login by changing the user’s Security Identifier (SID) to match thelogin’s SID.

NAME = newUserName

Specifies the new name for this user. newUserName must not already occur in the current database.

DEFAULT_SCHEMA = schemaName

Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. Renames a database user or changes its default schema.

PASSWORD = ‘password’

Specifies the password for the user that is being changed. Passwords are case-sensitive. Note: This option is available only for contained users. See Contained Databases and sp_migrate_user_to_contained

(Transact-SQL) for more information.

OLD_PASSWORD =’oldpassword’

The current user password that will be replaced by ‘password’. Passwords are case-sensitive.

OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission.

Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password. Note: This option is available only for contained users.

Remarks:

If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema.

DEFAULT_SCHEMA can be set to a schema that does not currently occur in the database. Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot be specified for a user who is mapped to a Windows group, a certificate, or an asymmetric key.

Important:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

You can change the name of a user who is mapped to a Windows login or group only when the SID of the new user name matches the SID that is recorded in the database. This check helps prevent spoofing of

Windows logins in the database.

The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause.

Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.

The name of the user will be automatically renamed to the login name if the following conditions are true.

The user is a Windows user.

The name is a Windows name (contains a backslash).

No new name was specified.

The current name differs from the login name.

Otherwise, the user will not be renamed unless the caller additionally invokes the NAME clause.

 

 

QUESTION 17

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 administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Currently, the table is partitioned by year with each in its own filegroup.

You need to add a new partition for the upcoming year.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause. Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: F

Explanation/Reference:

 

 

QUESTION 18

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 administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Currently, the table is not partitioned and contains no indexes.

You need to partition the table by year.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause. Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: H

Explanation/Reference:

Original answer was:

Create a new Filegroup.

Create a new database file.

Use the ALTER PARTITION SCHEME statement

Use ALTER INDEX REORGANIZE statement.

Original answer disputed:

From my point of view the best choice is:

Create a new partition function.

Create a new partition scheme.

Add a clustered index to place the data onto the partition scheme.

http://sqlblogcasts.com/blogs/martinbell/archive/2009/07/22/Partitioning-existing-tables.aspx

http://www.kodyaz.com/articles/how-to-partition-table-non-partitioned-table-sql-server-2008.aspx

http://forums.asp.net/t/1581091.aspx/1

 

 

QUESTION 19

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 administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Currently, the table is partitioned by Quarter.

You need to make the table unpartitioned.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause. Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table.

Use the ALTER TABLE statement along with the SWITCH PARTITION clause.

Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: A

Explanation/Reference:

Feedback by Rafe:

If the table has a partitioned clustered index applied to it, you can obtain the same result by dropping the index and rebuilding it as a nonpartitioned index.

You can do this by using the Transact-SQL CREATE INDEX command with the DROP EXISTING = ON clause.

 

 

QUESTION 20

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 administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Data is grouped into quarterly partitions.

You need to configure the groupings into 12 monthly partitions.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause. Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: B

Explanation/Reference:

Feedback:

 

A. Splitting a partition of a partitioned table or index into two partitions The following example creates a partition function to partition a table or index into four partitions. ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions. Transact-SQL IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = ‘myRangePF1’) DROP PARTITION FUNCTION myRangePF1; GO CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES ( 1, 100, 1000 ); GO –Split the partition between boundary_values 100 and 1000 –to create two partitions between boundary_values 100 and 500 –and between boundary_values 500 and 1000. ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (500)

 

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