Learn About Microsoft SQL Server 2008

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:

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Connecting to the SQL Server Database Engine > Client Network Configuration > Choosing a Network Protocol

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Connecting to the SQL Server Database Engine > Server Network Configuration > Default SQL Server Network Configuration

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:

MSDN > MSDN Library> Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > System Tables (Transact-SQL) > SQL Server Agent Tables (Transact-SQL) > sysjobhistory (Transact-SQL)

Objective: Manage SQL Server security.
Sub-objective: Audit SQL Server instances.

Single answer, multiple-choice

You are the database administrator for your company and manage all the SQL Server 2008 databases of the company. You are responsible for performing the daily backup activities on all the databases on the server. Database users regularly create new objects in one of the databases. You want to be notified when new objects are created in the database.

You want to create a trigger that will fire whenever a new user-defined function is created in the database. The trigger should insert statement-specific data, such as the name of the database user who issued the CREATE FUNCTION statement, the time the statement was issued and the Transact-SQL statement that was issued, into a database table named Log_structures. The Log_structures table was created by using the following statement:

CREATE TABLE Log_structures (
user1 nvarchar(100),
createtime datetime,
SQL_stat nvarchar(2000));

Which statement should you use to create the trigger?

A. CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

B. CREATE TRIGGER Audit_functions
AFTER CREATE_FUNCTION
ON DATABASE
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

C. CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

D. CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(2000)’));

Answer:
A

Tutorial:
You should use the following statement to create the trigger:

CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

This statement uses the correct syntax to create a trigger that will fire whenever a new function is created in the database. The trigger will insert the name of the current user, the time and the Transact-SQL statement that caused the trigger to fire into the Log_structures table. The CREATE_FUNCTION event is fired when a function is created in the database. The ON DATABASE clause in the statement specifies that the scope of the trigger is the current database. Therefore, this trigger will be fired whenever a function is created in the current database.

The trigger body also uses the EVENTDATA function. This function returns information regarding the database event that caused the trigger to fire, such as the name of the instance on which the event was fired, the Transact-SQL statement that caused the event to fire and the type of event that was fired. This function can be called from within trigger to return the specified information. The following line in the CREATE TRIGGER statement specifies that the Transact-SQL statement that caused the trigger to be fired should be returned:

@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’))

The value returned is stored in the Log_structures table. The Log_structures table also stores the name of the database user who created the function and the time at which the function was created. The CURRENT_USER function returns the name of the user who executed the statement, and the GETDATE() function returns the date and time the statement was executed.

You should not use the following statement:

CREATE TRIGGER Audit_functions
AFTER CREATE_FUNCTION
ON DATABASE
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

This statement is syntactically incorrect. In a CREATE TRIGGER statement, the ON DATABASE clause appears before the AFTER event_name clause. To correct this statement, you should place the ON DATABASE clause before the AFTER CREATE_FUNCTION clause.

You should not use the following statement:

CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’));

The mydata variable is not declared. You cannot use the variable until you declare the variable by using the DECLARE statement.

You should not use the following statement:

CREATE TRIGGER Audit_functions
ON DATABASE
AFTER CREATE_FUNCTION
AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT Log_structures(USER1, CREATETIME, SQL_stat)
VALUES(
CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(2000)’))

This statement inserts the EventType property returned by the EVENTDATA function. In this scenario, you are required to insert the Transact-SQL statement into the Log_structures table. To insert the Transact-SQL statement into the Log_structures table, you should replace @mydata.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(2000)’))
with @mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’)).

References:

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > CREATE TRIGGER (Transact-SQL)

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > EVENTDATA (Transact-SQL)

MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > DDL Triggers > Designing DDL Triggers > DDL Events

Objective: Maintain a SQL Server database.
Sub-objective: Restore databases.

Multiple answer, multiple-choice

You are the database administrator for your company. You are maintaining the SQL Server instance named Sql1 on the Sqlserver1 server. The Sql1 instance stores the Prod1 database. The Prod1 database is configured to use the full recovery model.

You are required to configure a secondary database for Prod1 to provide high availability of the database. You are required to initialize the secondary database from a full database backup of the Prod1 database and restore the logs from the online primary server to the secondary server. You do not want to bring the secondary database online immediately.

Which actions should you perform to initialize the secondary database from the primary database? (Choose two. Each correct answer represents part of the solution.)

A. Issue the RESTORE DATABASE statement with the WITH NORECOVERY clause.
B. Issue the RESTORE DATABASE statement with the WITH RECOVERY clause.
C. Issue the RESTORE LOG statement with the WITH NORECOVERY clause.
D. Issue the RESTORE LOG statement with the WITH RECOVERY clause.
E. Issue the RESTORE LOG statement with the WITH STANDBY clause.

Answer:
A, C

Tutorial:
You should perform the following steps to initialize a secondary database from a primary database without bringing the secondary database online immediately:

1. Issue the RESTORE DATABASE statement with the WITH NORECOVERY option.
2. Issue the RESTORE LOG statement with the WITH NORECOVERY option.

To initialize a secondary database from a primary database, you must first restore a full database backup of the primary database to the location where the secondary database will be stored. This is done by issuing the RESTORE DATABASE statement.

Specifying the WITH NORECOVERY clause with the statement will initialize the secondary database. The WITH NORECOVERY clause is used when you need to restore additional transaction logs on the database. Using the WITH RECOVERY clause with the RESTORE DATABASE statement will perform a recovery on the secondary database and bring it online immediately.

In this scenario, you are not required to bring the secondary database online immediately. Using the WITH NORECOVERY clause with the RESTORE LOG statement restores the transaction log. The WITH NORECOVERY clause should be used with the RESTORE LOG statement when additional transaction logs must be applied to the secondary database.

You should not issue the RESTORE DATABASE statement with the WITH RECOVERY clause. Using the WITH RECOVERY clause with the RESTORE DATABASE statement performs a recovery on the secondary database and brings the secondary database online immediately. In this scenario, you are not required to bring the secondary database online.

You should not issue the RESTORE LOG command with the WITH RECOVERY option. The WITH RECOVERY option with the RESTORE LOG command performs a recovery on the secondary database transaction log and brings the secondary database online immediately. The WITH RECOVERY option is used with the RESTORE LOG command when you want to apply the last transaction log to the secondary database and bring it online. In this scenario, you are not required to bring the secondary database online.

You should not issue the RESTORE LOG statement with the WITH STANDBY clause because this is not allowed for an online restore. For an online restore, you must use the RECOVERY or NORECOVERY clause. Using the WITH STANDBY clause leaves the database in read-only, standby mode.

References:

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > RESTORE Statements for Restoring, Recovering, and Managing Backups (Transact-SQL) > RESTORE Arguments (Transact-SQL)

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > EVENTDATA (Transact-SQL)

Objective: Monitor and troubleshoot SQL Server.
Sub-objective: Identify concurrency problems.

Multiple answer, multiple-choice

You are the database administrator for your company. You manage all the SQL Server 2008 databases. The Prod_details database is the main database accessed by the company’s users. The head office receives data from other branches of the company. After the data is verified by employees of the audit department, the database is updated with this information.

Some employees in the audit department complain they cannot update data in certain tables. You suspect other database users are holding locks on these tables. You must identify the oldest transaction in the database and the SQL Server logins associated with the transaction. Which statements or functions should you use to obtain the desired results? (Choose two. Each answer represents a part of the solution.)

A. The DBCC OPENTRAN statement.
B. The DBCC ROWLOCK statement.
C. The USER_NAME function.
D. The SUSER_SNAME function.
E. The SUSER_SID function.
F. The USER_ID function.

Answer:
A, D

Tutorial:
You should use the DBCC OPENTRAN statement and the SUSER_SNAME function. The DBCC OPENTRAN statement is used to obtain the details of the oldest transaction in the database. This statement returns the security identification number (SID), the server process ID (SPID), the name and the start time of the transaction. The SID value returned can be used to obtain the SQL Server login associated with the transaction. The SUSER_SNAME function is used to obtain the SQL Server login associated with the SID. When you pass the SID returned by the DBCC OPENTRAN statement to the SUSER_SNAME function, you can obtain the SQL Server login associated with the oldest transaction in the database.

You should not use the DBCC ROWLOCK statement because this statement does not provide information about the oldest transaction in the database or the SQL Server log-in associated with the transaction. The DBCC ROWLOCK statement was valid in the earlier versions of the SQL Server but is not supported in SQL Server 2008.

You should not use the USER_NAME function because the USER_NAME function does not provide the information required in this scenario. The USER_NAME function returns the database user name associated with the user identification number passed as an argument.

You should not use the SUSER_SID function because this function does not provide the information required in this scenario. The SUSER_SID function returns the SID associated with the database user name passed as an argument.

You should not use the USER_ID function because this function does not provide information required in this scenario. The USER_ID function returns the user ID associated with the database user name passed as an argument.

References:

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > DBCC (Transact-SQL) > DBCC (Transact-SQL)

TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > SUSER_SNAME (Transact-SQL)

Like what you see? Share it.
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: