Microsoft SQL Server 2008, Implementation and Maintenance
BackBy Certification Magazine —
1 | 2 |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.
1 | 2 |