Вчера сдал этот экзамен. Хотелось бы обсудить некоторые вопросы, точнее, правильные ответы на них с пояснениями.



1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Pierre reports that
he cannot modify data in the database. You use SQL Profiler to capture
his activities as shown in the exhibit.

set transaction isolation level serializable
begin transaction
select * from Products
update Products
set UnitPrice = 18.50
where UnitPrice = 18.00
commit transaction

a. Change the isolation level of the database connection.

b. Add indexes to the Products table.

c. Remove the explicit transaction from the Transact-SQL batch.

d. Create a stored procedure to execute the Transact-SQL batch.

e. Add a cascading UPDATE trigger to the Products table




2. You are the administrator of a SQL Server 2000 computer.
Your company purchases an accounting application from a vendor.
The application stores its data in a database named Accounting
on the server. The tables in this database contain columns that
function as primary keys, but PRIMARY KEY and FOREIGN KEY
constraints are not used.

You need to replicate data from this database to another SQL Server
computer. This server will use the replicated data to generate reports. Most reports will run each month, but the
accounting department needs to have the ability to run reports
at any time. Reports should be acurate through the last full
working day.

You cannot make any changes to the database, but you need to implement
replication. Which two actions should you take? (Each correct answer
represents part of the solution. Choose two.)

a. Implement merge replication.

b. Implement snapshot replication.

c. Implement transactional replication.

d. Schedule replication to run continuously.

e. Schedule replication to run during off-peak hours.




3. You are the administrator of a Microsoft Windows 2000 Advanced Server
computer. The server is also running SQL Server 2000 and contains a
database named Sales. The server is configured as shown in the exhibit.

The Sales database is configured by using the default options and
increases by 50 MB per day. You want an operator to be notified
automatically before the database runs out of disk space. You also want to
minimize the amount of administrative overhead necessary for this database.

... DISK1: Sales_data.mdf (6GB) + Sales_data.ldf (1GB)

What should you do?

a. Configure System Monitor to log an alert if the hard disk contains less
than 600 MB of free space
Configure SQL Server Agent to send an e-mail message notifying the
operator of the System Monitor alert

b. Create a SQL Server Agent job that uses the sp_spaceused stored
procedure each morning
Place the results of the stored procedure in an e-mail message and use SQL
Mail to send the results to the operator

c. Set the MaxSize property of Sales_data.mdf to 7.5 GB and the MaxSize
property of Sales_log.ldf to 1.5 GB
Configure SQL Server Agent to send an e-mail message to an operator when
the data file and the transaction log have reached these limits

d. Use Microsoft Windows Explorer to verify the available space on the
hard disk each morning
Execute the sp_spaceused stored procedure each morning




4. You are the administrator of a SQL Server 2000 database. You import
a table of geographic information from a Microsoft Access database into a
SQL Server 2000 database. The table has 12,000 rows. Each row averages
5,000 bytes. The table contains lookup data that does not change. You
want to minimize the size of the data file and the time required to back
up the data. Which two actions should you take?

(Each correct answer presents part of the solution, Choose two )


a. Create a 60-MB data file named Geography.ndf

b. Create a 95-MB data file named Geography.ndf

c. Create a 60-MB data file named Geography.mdf

d. Create a 95-MB data file named Geography.mdf

e. Place the table in the PRIMARY filegroup

f. Place the table in a new filegroup named LOCATION




5. Your company contains a variety of client computers
that will connect to the SQL Server 2000 computer by using
a specific Net-Library, as shown in the following table:


Client-computer Net-Library

Microsoft Windows 98 Named Pipes

Novell NetWare IPX/SPX

Apple Macintosh TCP/IP


You need to allow the client computers to connect to the
SQL Server computer. You also want to minimize the number
of configuration changes required on the client computers.
Which three actions should you take?

(Each correct answer presents part of the solution, Choose three)

a. Install SQL Server 2000 as a named instance

b. Install SQL Server 2000 as the default instance

c. Configure the new instance for Mixed Mode authentication

d. Configure the new instance for Windows Authentication

e. Configure the server to use the Named Pipes, IPX/SPX, and TCP/IP Net-Libraries

f. Configure the server to use the Multiprotocol Net-Library




6. You are the administrator of a Microsoft Windows NT Server computer The server is the PDC in your domain and also runs SQL Server 2000. The server has four processors and 1 GB of RAM. Your network consists of 500 client computers that are running Microsoft Windows Millennium Edition. Users report that when they log on in the morning their computers respond slowly and occasionally return error messages. You use System Monitor to monitor processor performance and RAM utilization You discover that the % Total Time and % User Time counters average 90 percent and the % Privileged Time counter averages 20 percent. The Available Mbytes counter averages 800 MB. You need to improve server performance for user logons. What should you do?

a. Increase the physical RAM on the computer to 2 GB

b. Increase the virtual RAM on the computer to 4 GB

c. Configure SQL Server to use only processors 1,2, and 3

d. Configure SQL Server to use only processors 0, 1, and 2




7. You are the administrator of a SQL Server 2000 computer. The server
contains two databases. A client/server application accesses one of the
databases. A different client/server application accesses the other
database. Each application uses several stored procedures to query and
update its database. Users report that both applications respond slowly.
You use SQL Profiler to monitor activity on the server. You receive
results of the trace as shown below:


Event Class Test Date

SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN

SQL:BatchCompleted Update Customer Set CustomerZip = ......

SQL:BatchCompleted Update Products Set SKU = '23232323.....

SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN

SQL:BatchCompleted Update Customers Set CustomerZip=


You need to improve the performance of the applications. What should you
do?

a. Modify the applications so that they use views to query data.

b. Modify the stored procedures so that they select data into temporary
tables.

c. Re-create the stored procedures so that they do not contain the WITH
RECOMPILE option.

d. Disable the 'Autoupdate statistics' option in both databases.


Благодарю.

Добавка: в первом вопросе необходимо выбрать два независимых решения.

Пардон, привожу еще раз первый вопрос, т.к. я его не верно описал:

4. You are the administrator of a SQL Server 2000 computer. The server
contains a database named Sales. Pierre reports that he cannot modify data
in the database. You use SQL Profiler to capture his activities as shown
in the exhibit.

set transaction isolation level serializable
begin transaction
select * from Products
update Products
set UnitPrice = 18.50
where UnitPrice = 18.00
commit transaction

You want Pierre to be able to modify data. You also want the Transact-SQL
statement to execute without compromising data integrity.

What are two possible ways to achieve this goal? (Each correct answer
represents a complete solution. Choose two.)

a. Change the isolation level of the database connection.

b. Add indexes to the Products table.

c. Remove the explicit transaction from the Transact-SQL batch.

d. Create a stored procedure to execute the Transact-SQL batch.

e. Add a cascading UPDATE trigger to the Products table


Мои ответы были такие:

1. a, d
Почему a я объяснить не могу кроме как методом исключения.
d потому как нужна integrity.
В Сети нашел, что вместо a нужно e, но каким образом это объяснить?



2. b, e
Но сомневаюсь, т.к. snapshot для большого объема данных может быть
не лучшим решением, тем более, что не сказано, что данных мало. А вот
что они меняются, сказано... То есть transactional...

3. a
Я сомневался, что информация о том, что на диске менее 600 MB будет
информативнее точной оценки размера БД (b)

4. d,f
95 выходит из 12000*8Kb (page size, each page can hold only one row)


5. bce
Сомнения были по Multiprotocol

6. d
Потому как последний процессор (3) будет занят NIC
Или я не прав?

7. c
Это методом исключения, только я все равно не понял...

Сказано: Each row averages 5,000 bytes.

То есть, на страницу влезет только один row (между страницами row не могут быть).

Отсюда я сделал вывод, что each page can hold only one row

2Glory

Насчет первого вопроса:

Да, только сейчас понял, что я стромозил. Я думал, что это его (Pierre'а) активности трейс. Тогда a и c конечно.

По шестому вопросу:

Before you change the setting of affinity mask, keep in mind that Windows NT 4.0 assigns deferred process call (DPC) activity associated with NICs to the highest numbered processor in the system. In systems with more than one installed and active NIC, each additional card's activity is assigned to the next highest numbered processor. For example, an eight-processor system with two NICs has DPCs for each NIC assigned to processor 7 and to processor 6.
Или опять торможу?

Спасибо большое за разъяснения.

Здравствуйте, gda

Почти все, что попалось мне на экзамене 70-228, есть тут:

http://www.yaxoo.com/Books/Certification/Microsoft/Testking - 70-228 Win2000 SQL Server 2000 Administration.pdf
Там же есть масса полезной информации вообще по сертификации.

Удачи