Learn About Microsoft SQL Server 2008
By Certification Magazine —
1 | 2 | 3 | 4 | These questions are based on 70-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance
A Self Test Software Practice Test
Objective: Install and configure SQL Server 2008.
Sub-objective: Configure SQL Server instances.
Multiple answer, multiple-choice
You are a database administrator in your company. You create a new database named Prod1 on a SQL Server 2008 instance named Sql1. The users on your company's LAN should have access to the Prod1 database. All the client computers are running Windows XP Professional and have the SQL Client component installed. The shared memory protocol also is enabled on all the client computers in the SQL Native Client Configuration.
What could you do to enable clients to connect to Sql1? (Choose all that apply. Each correct answer presents a unique solution.)
A. Enable the VIA protocol for all the clients.
B. Enable the named pipes protocol for Sql1 only.
C. Enable the TCP/IP protocol for all the clients.
D. Enable the shared memory protocol for Sql1.
E. Enable the named pipes protocol for all the clients and Sql1.
Answer:
C, E
Tutorial:
You could enable the named pipes protocol for all the clients and Sql1, or you could enable the TCP/IP protocol and the named pipes protocol for all the clients. In this scenario, the client computers should access Sql1 on the company's LAN. Therefore, you should enable the named pipes protocol on both Sql1 and the client computers. The named pipes protocol is designed primarily for use with LANs.
With this protocol, a part of memory is used by a process to pass information to another process. The information passed by the first process is used as input to the second process that is located on the same computer or on a remote computer. You also can enable the TCP/IP protocol for all the clients because the TCP/IP protocol is enabled on the server by default. Therefore, you can also use the TCP/IP protocol to enable clients to connect to Sql1.
You should not enable the Virtual Interface Adapter (VIA) protocol for all the clients. The VIA protocol works only with VIA hardware and should only be used in configurations that use VIA hardware.
You should not enable the named pipes protocol for Sql1 only. You also need to enable this protocol for the client.
You should not enable the shared memory protocol for Sql1 because the shared memory protocol does not support access over LANs. Using the shared memory protocol, you can only connect to a server instance running on the same computer.
To view the network protocols that are enabled for the server, you can open SQL Server Configuration Manager and expand SQL Server Network Configuration. This will display the four possible protocols you can use and the status of each, as shown in the following image:
The client network protocols are displayed in the Client Protocols section under SQL Native Client Configuration.
References:
Objective: Maintain SQL Server instances.
Sub-objective: Manage SQL Server Agent jobs.
Single answer, multiple-choice
You are the database administrator for a major shipping company. You manage all the SQL Server 2005 instances of the company. For one of your instances, you have created jobs to perform regular administrative activities.
Some of these jobs in the database fail because the server went down due to a power failure. You want to analyze these failed jobs. You also want to find out the tasks performed by these failed jobs. Which query will you use to achieve the objective?
A. SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 1
B. SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_status = -1
C. SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_status = 0
D. SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 0
Answer:
D
Tutorial:
The following option is correct:
SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 0
The sysjobhistory system table provides a historical record of the jobs that previously executed. This table resides in the msdb database and contains columns, such as job_id, step_id and step_name, that identify jobs and the steps involved in the jobs. To retrieve the details of tasks being performed by the failed jobs, you should retrieve rows for only the jobs that failed.
You can accomplish this by specifying a condition of run_status=0 in the WHERE clause of the query. A value of 0 in the run_status column indicates a failed job. Therefore, the job_id, step_id and step_name columns will be retrieved only for the jobs that have failed.
You should not use the following query:
SELECT job_id, step_id, step_name
FROM msdb.dbo.sysjobhistory
WHERE run_status = 1;
This query uses a condition of run_status=1 in the WHERE clause. A value of 1 in the run_status column indicates the jobs completed successfully. In this scenario, you must retrieve details about the jobs that failed, not the ones that were successfully completed.
You should not use the following query:
SELECT job_id, step_id, step_name
FROM msdb.dbo.sysjobactivity
WHERE run_status = -1;
This query uses the sysjobactivity system table that does not provide information for failed jobs. The sysjobactivity system table provides details on current jobs. This query will generate an error because the sysjobactivity system table does not contain a step_id, step_name or run_status column.
You should not use the following query:
SELECT job_id, step_id, step_name
FROM msdb.dbo.sysjobactivity
WHERE run_status = 0;
This query will generate an error because the sysjobactivity system table does not contain a step_id, step_name or run_status column. The sysjobactivity system table does not provide information for failed jobs. The sysjobactivity system table provides details on current jobs and their job steps. This table contains information, such as the time the last step in the job executed and the time at which the job is scheduled to run next.
Reference:
Objective: Manage SQL Server security.
Sub-objective: Audit SQL Server instances.
