Microsoft SQL Server 2008, Implementation and Maintenance

These practice test questions from MeasureUp are based on Microsoft’s exam 70-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance .

The audience for this exam includes individuals who are responsible for installing, configuring and managing SQL Server. Experience using the management tools for SQL Server to configure and manage SQL Server instances, databases, services and components will help you prepare for this exam.

By passing exam 70-432, you earn the Microsoft Certified Technology Specialist (MCTS) SQL Server 2008 – Implementation and Maintenance certification.

You also earn credit toward the Microsoft Certified IT Professional (MCITP) Database Administrator certification.

Objective: Maintain SQL Server instances.
Sub-objective: Manage SQL Server Agent operators.

Single answer, multiple-choice

You manage a default instance of Microsoft SQL Server 2008. SQL Server Agent Mail is currently configured to use SQL Mail for sending e-mail notifications. SQL Server Agent is configured to start automatically any time you start the SQL Server Database Engine service.

You create a private database mail profile. You use sp_send_dbmail to test the profile and verify that it can be used to send mail. You configure SQL Server Agent Mail to use Database Mail and enable and select the profile you created. After configuring SQL Server Agent Mail to use Database Mail, the SQL Server Agent no longer sends notifications to any operators.

You need to correct the problem so that alert and job completion notification e-mails are sent to the designated operators. You need to have minimal impact on SQL Server operations. What should you do?

A.    Restart the SQL Server Database Engine service.
B.    Restart the SQL Agent service.
C.    Create a public database mail profile and configure SQL Server Agent Mail to use the public profile.
D.    Create a private profile for each of the designated operators.
E.    Create a public profile for the designated fail-safe operator.

Answer:
B

Tutorial:
You should restart the SQL Agent service. This is necessary any time you change the mail service used by SQL Server Agent Mail. Microsoft recommends using Database Mail instead of SQL Mail for SQL Server Agent Mail because SQL Mail is scheduled to be discontinued in a future release of SQL Server. Database operations will not be interrupted by restarting the SQL Agent service.

You should not restart the SQL Server Database Engine service. This would correct the problem but would interrupt operations.

You should not create a public database mail profile and configure SQL Server Agent Mail to use the public profile. There is no reason to create a public profile. You know the profile is configured correctly because it works with the sp_send_dbmail stored procedure. If there were a problem with the profile, the stored procedure would have failed.

There is no reason to create profiles for any of the operators as part of your solution. The profiles are used for sending mail, not receiving mail.

References:
SQL Server Agent Mail
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms188235(SQL.100).aspx
    
How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms186358(SQL.100).aspx
    
Database Mail
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms175887(SQL.100).aspx
    
Alerting Operators
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms175083(SQL.100).aspx
    
SQL Mail
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms177418(SQL.100).aspx

Objective: Manage SQL Server security.
Sub-objective: Manage schema permissions and object permissions.

Single answer, multiple-choice

You manage a default Microsoft SQL Server 2008 instance. The Inventory database contains a schema named SKU owned by dbo. Several database objects are contained in the schema.

You need to transfer ownership of the schema to InvData_owner. You need to do this with minimal administrative effort and with minimal interference with database operations. What should you do?

A.    Run ALTER AUTHORIZATION.
B.    Run ALTER SCHEMA.
C.    Run ALTER USER.
D.    Run DROP SCHEMA and CREATE SCHEMA.

Answer:
A

Tutorial:
You should run ALTER AUTHORIZATION. The schema owner is specified as the schema authorization. By changing the schema authorization, you change schema ownership. You can also use the schema properties in SQL Server Management Studio to change schema ownership.

You should not run ALTER SCHEMA. ALTER SCHEMA is used to move an object in a schema, not to change the schema owner.

You should not run ALTER USER. ALTER USER lets you modify database user properties but does not let you assign ownership of a schema to the user.

You should not run DROP SCHEMA and CREATE SCHEMA. You can drop and recreate a schema with a new owner, but this would require more effort than simply changing the schema authorization. You would have to specify the objects contained in the schema when you recreate the schema and redefine any permissions associated with the schema.

References:
ALTER AUTHORIZATION (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms187359(SQL.100).aspx
    
CREATE SCHEMA (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms189462(SQL.100).aspx
    
User-Schema Separation
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms190387(SQL.100).aspx
    
DROP SCHEMA (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms186751(SQL.100).aspx

ALTER USER (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms176060(SQL.100).aspx

Objective: Maintain a SQL Server database.
Sub-objective: Maintain database integrity.

Single answer, multiple-choice

You maintain a database named SalesDB. SalesDB is located on a server running SQL Server 2008. SalesDB has two filegroups. All tables are located on the filegroup named FG1. All nonclustered indexes are located on the filegroup named FGIndex.

You need to verify the integrity of the nonclustered indexes. The operation must complete as quickly as possible. What should you do?

A.    Execute DBCC CHECKFILEGROUP FG1.
B.    Execute DBCC CHECKFILEGROUP FGIndex.
C.    Execute CHECKSUM SalesDB.
D.    Execute DBCC CHECKDB SalesDB.

Answer:
D

Tutorial:
You should execute DBCC CHECKDB SalesDB. Because the nonclustered indexes are stored on a different filegroup than the base tables, you need to execute DBCC CHECKDB to verify the integrity of the indexes.

You should not execute DBCC CHECKFILEGROUP FG1. The integrity of a nonclustered index does not matter when validating the integrity of a base table. Therefore, nonclustered indexes will only be checked if they are located in the same filegroup as the base table.

You should not execute DBCC CHECKFILEGROUP FGIndex. In versions prior to SQL Server 2005, you could execute CHECKFILEGROUP on the filegroup containing the index to check the integrity of indexes even if the base tables are stored in different filegroups. However, this functionality was changed in SQL Server 2005.

You should not execute CHECKSUM SalesDB. The CHECKSUM command generates a CHECKSUM for the object specified in the expression. It does not validate index integrity.

References:
DBCC CHECKFILEGROUP (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms187332(SQL.100).aspx
    
CHECKSUM (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms189788(SQL.100).aspx
    
DBCC CHECKDB (Transact-SQL)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms176064(SQL.100).aspx
    

Objective: Perform data management tasks.
Sub-objective: Implement data compression.

Single answer, multiple-choice

You manage a database hosted on an instance of SQL Server 2008. The database has a table named ProductSpecs. The ProductSpecs table is shown in the exhibit. The ProductID can have between three and 10 characters. A number of products have the same measurement in one or more dimensions. For example, nearly 100 products have either a height or width of 10 inches.

A clustered index is created on the ProductID column. The table has a very large number of rows. You want to reduce the amount of storage space required for the ProductSpecs table. You need to choose the best compression option. What should you do?


 
A.    Use row compression.
B.    Define ProductID as a sparse column.
C.    Use page compression.
D.    Define all columns as sparse columns.

Answer:
C

Tutorial:
You should use page compression. Page compression first uses row compression to compress a row of data. If multiple rows are contained on a page, it also uses dictionary compression for multiple instances of the same values. Because there are duplicate values in many of the fields, dictionary compression will help conserve space.

You should not use row compression. Row compression uses the smallest number of bytes to store a specific value. For example, the number 10 would be stored in a single byte. However, it is a subset of page compression, so you would not get the added savings of combining instances of a duplicate value.

You should not use sparse columns. Sparse columns help conserve space for columns that allow nulls. All columns are required, so none allow nulls.

References:
Row Compression Implementation
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280576(SQL.100).aspx
    
Page Compression Implementation
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280464(SQL.100).aspx
    
Using Sparse Columns
Microsoft TechNet
http://technet.microsoft.com/en-us/library/cc280604(SQL.100).aspx

Objective: Optimize SQL Server performance.
Sub-objective: Implement Resource Governor.

Single answer, multiple-choice

You manage an instance of SQL Server 2008. You enable the Resource Governor and install a classifier function. Users report they cannot connect to SQL Server. You need to troubleshoot the problem while Resource Governor is running. What should you do?

A.    Connect to the instance by using a Dedicated Administrator Connection (DAC).
B.    Set the database to single user mode.
C.    Connect to the instance as a member of the sysadmin fixed server role.
D.    Set the database to restricted user mode.

Answer:
A

Tutorial:
You should connect to the instance by using a DAC. A DAC executes by using the internal resource pool, so it is not affected by problems with the classifier function or other Resource Governor configuration settings.

You should not set the database to single user mode. The Resource Governor does not run in single user mode, so while you can connect to the instance, you will not be able to resolve the problem. However, if the DAC is not enabled, you could use this method to disable Resource Governor to allow users to connect while you investigate the problem on a different instance.

You should not connect to the instance as a member of the sysadmin fixed server role. Members of the sysadmin fixed server role are still subject to classification, so you will not be able to connect.

You should not set the database to restricted user mode. Restricted user mode allows only members of db_owner, dbcreator and sysadmin roles to access the database, but it does not exempt the connection from being managed by Query Governor.

Reference:
Resource Governor Concepts
Microsoft TechNet
http://technet.microsoft.com/en-us/library/bb934084(SQL.100).aspx

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: