Installing, Configuring and Administering Microsoft SQL Server 2000, Enterprise Edition

1.You are the administrator of a SQL Server 2000 computer. You import a large amount    of data into the Contracts database. After the import, you perform a full database backup. You examine the hard disk and discover that the log file has grown from 200 MB to 800 MB.
You want to recover disk space as quickly as possible without losing backup information. Which two Transact-SQL statements should you execute? (Each correct answer presents part of the solution. Choose two.)
A. BACKUP LOG Contracts WITH no_log
B. BACKUP LOG Contracts WITH truncate_only
C. BACKUP LOG Contracts To disk='E:\SQLBackup\Contracts_Log.bkp'
D. ALTER DATABASE Contracts
   (Name=Contract_Log.ldf,
   FILENAME='E:\Data\Contracts.ldf',
   SIZE=200)
E. DBCC ShrinkDatabase (Contracts, 200)
F. DBCC ShrinkFile (Contracts_Log, 200)
Answer: CF

2.You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database has a Parts table that has a field named InStock.hen parts are shipped, a table named PartsShipped is updated. When parts are received, a table named PartsReceived is updated. The relationship of these tables is

shown in the exhibit. (Click the <<ItemExhibitName>> button.) You want the database to update the InStock field automatically. What should you do?
A. Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the Parts table.
B. Create a user-defined function that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables.
C. Use a view that creates an InStock field as part of an aggregate query.
D. Create stored procedures for modifying the PartsReceived and the PartsShipped   tables that also odify the InStock field in the Parts table.Use these procedures exclusively when modifying data in the PartsReceived and the PartsShipped tables.
Answer: A

3.You are the administrator of a SQL Server 2000 computer. You are configuring a   database for an inventory application. The hard  disks on your server are configured as shown in the exhibit.    (Click   the <<ItemExhibitName>> button.)
The operating system files are located on drive C. Your database will store a maximum   of 6 GB of data and requires a maximum of 2 GB of disk space for the transaction log.You want to optimize database performance.
What should you do?

A. Add a 2-GB transaction log to drive D, a 3-GB data file to drive E, and a 3-GB data file to drive F.
B. Add a 1-GB transaction log to drive C, a 1-GB transaction log to drive D, a 3-GB data file to drive E, and a 3-GB data file to drive F.
C. Add a 1-GB transaction log to drive E, a 1-GB transaction log to drive F, a 3-GB data file to drive E, and a 3-GB data file to drive F.
D. Add a 2-GB transaction log to drive F, a 3-GB data file to drive D, and a 3-GB data file to drive E.
Answer: A

4.You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales that has two data files and one transaction log file. Each data file is located on its own hard disk and exists in its own filegroup.You perform full database, differential, and transaction log backups on a regular basis. All backups made during a  single week are striped across three disk backup devices. A portion of the header   information for the current week's backups is shown in the following table.
BackupName                     BackupType             BackupFinishDate
----------------------- --------------- ---------------------------
sales_db_20000625                   1                      2000-06-25 21:57:04.000
sales_tl_20000626_1                  2                      2000-06-26 11:04:22.000
sales_tl_20000626_2                  2                      2000-06-26 15:06:33.000
sales_df_20000626                    5                      2000-06-26 21:15:48.000
sales_tl_20000627_1                  2                      2000-06-27 11:03:39.000
sales_tl_20000627_2                  2                      2000-06-27 15:04:59.000
sales_df_20000627                    5                     2000-06-27 21:31:13.000
sales_tl_20000628_1                  2                      2000-06-28 11:05:16.000
On June 28, 2000, at 1:47 P.M., the hard disk that contains the PRIMARY filegroup fails. You want to recover as much data as possible.What should you do? (Each correct  answer presents part of the solution. Choose all that apply.)
A. Backup the transaction log by using the NO_LOG option.
B. Backup the transaction log by using the NO_TRUNCATE option.
C. Restore the most recent full database backup.
D. Restore the most recent differential backup.
E. Restore all differential backups in sequence.
F. Restore all transaction log backups in sequence.
G. Restore all transaction logs since the most recent differential backup.
Answer: BCDG

5.You are the administrator of a SQL Server 2000 computer. When you arrive at work, you learn that power to the server has been interrupted.You need to find out whether the power loss damaged the databases on the server. You also need to correct any damage that occurred as quickly as possible. You have valid backups of each database previous to the power loss.What should you do?
A. Select the torn page detection option for each database.If torn pages are detected,   execute the DBCC CHECKDB statement, and specify the REPAIR_FAST option.
B. Execute the Sp_table_validation stored procedure for all tables in each database.If errors are detected, restore each damaged database from the most recent backup.
C. Execute the DBCC CHECKDB statement, and specify the PHYSICAL_ONLY option.If errors are detected, execute the DBCC DBREPAIR statement for each damaged database.
D. Execute the DBCC CHECKDB statement, and specify the PHYSICAL_ONLY option.If errors are detected, restore the database from the most recent backup.
Answer: D

6.You are the database administrator  for a university. You are migrating a database   from an old database management system to SQL Server 2000. You export student data from the database to a text file. All students have unique ID numbers. In the file, the student data is ordered according to these ID numbers. On the server, you create a   table named Students. You create indexes on the Students table as shown in the
following output.
index_description                                        index_keys
nonclustered                                            lastName, firstName
clustered, unique, primary key                             studentID
You want to load the student data into the new database as quickly as possible by   using the BULK INSERT statement. Which two actions should you take? (Each correct answer presents part of the solution. Choose two.)
A. Specify an ORDER clause.Sort the student ID numbers in ascending order.
B. Specify an ORDER clause.Sort the student ID numbers in descending order.
C. Drop both indexes from the Students table before using the BULK INSERT statement.Re-create the indexes when the data load is complete.
D. Drop the nonclustered index from the Students table before using the BULK INSERT statement.Re-create the nonclustered index when the data load is complete.
E. Drop the clustered index from the Students table before using the BULK INSERT   statement.Re-create the clustered index when the data load is complete.
Answer: AD

7.You are the administrator of a SQL Server 2000 computer at your company's warehouse. All product orders are shipped from this warehouse. Orders are received at 30 sales offices. Each sales office offers a range of products specific to its region.Each   sales office contains one SQL Server 2000 computer. These servers connect to the
warehouse through dial-up connections as needed, typically once a day. Each sales office needs data pertaining only to its region.You need to replicate inventory data from the server at the warehouse to the servers at the sales offices. You want to minimize the amount of time needed to replicate the data. Which three actions should you take? (Each correct answer presents part of the solution. Choose three.)
A. Create one publication for each Subscriber.
B. Create one publication for all Subscribers.
C. Enable horizontal filtering.
D. Enable vertical filtering.
E. Use pull subscriptions.
F. Use push subscriptions.
Answer: ACE

8.You are the administrator of a SQL Server 2000 computer named CorpSql. The   server is a member of a Microsoft Windows NT domain named CORPDOMAIN. CorpSql is configured for Windows Authentication and contains three databases named Sales       Finance and Research Sophie is an administrative assistant in your company. She uses a suite of client/server applications to access all three databases on CorpSql. Sophie transfers to a different department within the company and requires access only to the Research database. Her former manager requests that you remove Sophie's access to the Sales And Finance databases. You need to configure the appropriate permissions for Sophie. Which batch of Transact-SQL statements should you execute?
A. USE Finance
   GO
  EXEC sp_revokedbaccess Sophie
   USE Sales
   GO
   EXEC sp_revokedbaccess Sophie
B. USE Finance
   GO
   EXEC sp_addrolemember 'db_denydatareader','Sophie'
   USE Sales
   GO
   EXEC sp_addrolemember 'db_denydatareader','Sophie'
C. USE Master
   GO
   EXEC sp_droplogin 'CorpDomain\Sophie'
   GO
D. USE Finance
   GO
   EXEC sp_denylogin 'Sophie'
   USE Sales
   GO
   EXEC sp_grantlogin 'Sophie'
Answer: A

9.You are the administrator of a SQL Server 2000 computer. The server is used in your   company's research department to store confidential information about projects. The ResearchProjects table is configured as shown in the exhibit. (Click the <<ItemExhibitName>> button.)

The statisticians in your company need to review information about completed projects. The statisticians belong to a database role named StatUsers. You need to allow the statisticians to access the information on completed projects,  while protecting the information on projects that are still in progress. The statisticians must not be able to modify any data.
Which batch of Transact-SQL statements should you execute?
A. CREATE VIEW StatView AS
   SELECT ProjectID, ProjectName, StartDate, ProjectLeader, LastModified FROM ResearchProjects
   GO
   GRANT SELECT ON StatView TO StatUsers
B. CREATE VIEW StatView AS
   SELECT ProjectID, ProjectName, StartDate, ProjectLeader, LastModified FROM ResearchProjects
   WHERE ProjectComplete = 1
   GO
   GRANT SELECT ON StatView TO StatUsers
C. CREATE PROCEDURE StatView AS
   SELECT ProjectID, ProjectName, StartDate, ProjectLeader, LastModified FROM ResearchProjects
   WHERE ProjectComplete = 1
   GO
   GRANT SELECT ON StatView TO StatUsers
D. CREATE PROCEDURE StatView AS
   SELECT ProjectID, ProjectName, StartDate, ProjectLeader, LastModified FROM ResearchProjects
   GO
   GRANT SELECT ON StatView TO StatUsers
Answer: B

10.You are the administrator of a SQL Server 2000 computer.  You configure the   SQLServerAgent  service  as shown in the exhibit. (Click the <<ItemExhibitName>> button.)
You configure several SQL Server Agent jobs to perform maintenance tasks.  These   jobs delete old database records and copy files from the server to file servers on your network. You discover that none of the jobs will execute. You use SQL Server Enterprise Manager to check the jobs and discover that the SQLServerAgent service will not start.

You need to allow the SQLServerAgent service to start and execute the jobs. What should you do?
A. Configure the SQLServerAgent service to start by using the local system account.
B. Grant the SQLAgent domain user account the           Logon as a service rights.
C. Add the SQLAgent domain user account as a Microsoft Windows login on the server.
D. Configure permissions on the master database to allow full access to the SQLAgent user account.
Answer: B

11.You are the administrator of a Microsoft Windows 2000 Advanced Server computer. The server is used to run only SQL Server 2000. Users report that database performance slows each day at 10:00 A.M. You use System
Monitor to examine the server's performance and receive the results shown in the  exhibit. (Click the <<ItemExhibitName>> button.)
You need to discover why database performance is slow. What should you do?
 
A. Configure disk quotas on the file system, and track each database user's disk utilization.
B. Configure SQL Profiler to trace all queries that are submitted to the server.
C. Use the Sp_who stored procedure to discover who is using the database.
D. Use the Sessions folder in the Computer Management console to discover who is connected to the server.
Answer: B

12.You are the administrator of a Microsoft Windows 2000 computer. You are preparing to install SQL Server 2000 on the computer.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.

 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.
Answer: BCE

13.You are the database administrator for an accounting company. You are installing SQL Server 2000 on a new Microsoft Windows 2000 Server computer. The computer will run two client/server database applications that your company has purchased. Each application uses a separate database. During the installation, you specify SQL_Latin1_General_CP1_CI_AI as the default collation for the SQL Server computer. After completing the installation, you discover that one of the new applications, named Financials, is sorting information incorrectly.
You contact the application vendor and discover that the Financials  application     requires the SQL_Latin1_General_CP437_BIN collation. You need to configure the   correct collation for the applications without diminishing the performance of the SQL Server computer.
What should you do?
A. Rerun SQL Server Setup and specify the SQL_Latin1_General_CP437_BIN collation.
B. Run the rebuildm utility and specify the SQL_Latin1_General_CP437_BIN collation.
C. Use the ALTER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation for the Financials database.
D. Use the ALTER TABLE statement to specify the SQL_Latin1_General_CP437_BIN collation for each table in the   Financials database.
Answer: C

14.You are the administrator of a SQL Server 7.0 computer. Developers in your company use this server to create client/server applications. You want to install SQL Server 2000 on this server. You want to ensure that developers can begin migrating their applications to the server. You need to maintain the SQL Server 7.0 installation until the migration is complete.
You need to install SQL Server 2000 to support the migration. What are two possible   ways of installing SQL Server 2000 to achieve your goal? (Each correct answer presents a complete solution. Choose two.)
A. as a named instance
B. as the default instance
C. on a second computer
D. on a separate partition
E. in the C:\MSSQL7 folder
Answer: AC

15.You are the administrator of a SQL Server computer. The server is running SQL Server 6.5 and SQL Server 7.0. You install a named instance of SQL Server 2000, and then run the SQL Server Upgrade Wizard. On the Database Selection screen, some of the SQL Server 6.5 databases are not listed. You cancel the   SQL Server Upgrade Wizard.
You need to ensure that the SQL Server 6.5 databases are listed in the wizard. What should you do?
A. Uninstall SQL Server 7.0, and then rerun the SQL Server Upgrade Wizard.
B. Run the Microsoft SQL Server-Switch application, and then rerun the SQL Server Upgrade Wizard.
C. Create a Data Transformation Services (DTS) package that imports the databases from SQL Server 6.5 to SQL Server 2000, and then execute the package.
D. Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance.
Answer: B

16.You are the administrator of a SQL Server 2000 computer. Your company uses the   server to store service contract information for its customers.
You are also the administrator of an Oracle relational database management system (RDBMS) server. This server is used to store your company's financial information. The financial information is updated frequently throughout the day.
You need to create a series of reports that combine the service contract information and the financial information. These reports will be updated several times a day.
You want to create these reports on the SQL Server computer by using the minimum amount of disk space. What should you do?
A. Set up SQL Server replication to replicate the data from the Oracle server to the SQL Server computer.
B. Set up the Oracle server as a linked server. Create a view that joins the service contract information and the financial information.
C. Set up a Data Transformation Services (DTS) package that imports and transforms the data from the Oracle server to the SQL Server computer.Use SQL Server Agent to execute the DTS   package throughout the day as needed.
D. Set up a Microsoft ActiveX script that connects to the Oracle server and imports the financial information into a SQL Server temporary table.Create a view that joins the service contract information and the temporary table.
Answer: B

17.You are the administrator of several SQL Server 2000 computers. You configure these servers to send e-mail messages to SQL Server operators whenever a SQL Server Agent job fails or encounters an error. Your company's software developers create a set of stored procedures that send query results in e-mail messages. The developers report, however, that attempts to send the e-mail messages by the stored procedures have not been successful. By using a MAPI profile named MS Exchange Settings you verify that SQLAgentMail is correctly configured and working properly. You need to configure the servers so that the stored procedures function correctly. Which two actions should you
take? (Each correct answer presents part of the solution. Choose two.)
A. Set the security permissions on your e-mail server to permit multiple connections to the SQL Server mailbox.
B. Set the security permissions on your e-mail server to permit connections from the   MSSQLServer service account.
C. Configure the MSSQLServer service to use the same service account as the SQLServerAgent service.
D. Configure the SQLServerAgent service to use the same service account as the MSSQLServer service.
E. Configure SQL Mail to use the MS Exchange Settings MAPI profile.
F. Configure SQL Mail to use the Default Settings MAPI profile.
Answer: CE

18.You are the administrator of a SQL Server 2000 computer. Approximately 500 users   access the server by means of the Internet and the company LAN. The server has the Named Pipes and TCP/IP Net-Libraries installed. You install a named instance of SQL Server 2000 on the server to support a new application. Approximately 100 additional users will access the application by means of the Internet. After the installation, users can connect to the new application from the company LAN. However, users cannot connect to the server from the Internet to run the application. These users can connect to the original instance of SQL Server 2000. You need to allow the Internet users to connect to the new instance of SQL Server 2000. You want to make the minimum number of changes necessary to your present configuration. What should you do?
A. Configure the application to connect to TCP/IP port 1433 through a Virtual Private Network (VPN).
B. Reconfigure the TCP/IP Net-Library on the new instance to listen to the standard SQL Server TCP/IP port.
C. Specify a static port for the TCP/IP Net-Library on the new instance, and configure the application to connect to this port.
D. Configure the TCP/IP Net-Library on the client computers so that the library always connects to TCP/IP port 1433.
Answer: C

19.You are the administrator of two SQL Server computers. One server is named SQL7, and the other is named SQL2000. SQL7 is running SQL Server 7.0, and SQL2000 is running SQL Server 2000. The Net-Libraries on SQL2000 are configured as shown in the exhibit. (Click the <<ItemExhibitName>> button.)

SQL7 is configured so that it has the Named Pipes, TCP/IP, NWLink IPX/SPX, and Multiprotocol Net-Libraries. SQL2000 and SQL7 exchange confidential company information. You need to ensure that this information cannot be accessed by unauthorized users. Which two actions should you take? (Each correct answer presents part of the solution. Choose two.)
A. On SQL2000, enable the Multiprotocol Net-Library.
B. On SQL2000, select the Force protocol encryption check box.
C. On SQL7, select the  Force protocol encryption check box.
D. On SQL2000, install a Secure Sockets Layer (SSL) encryption certificate.
E. On SQL2000 and SQL7, enable Multiprotocol encryption.
Answer: AE

20.You are the administrator of a SQLServer 2000 computer. The server has the   Named Pipes and NWLink IPX/SPX Net-Libraries installed. You need to configure the server to support encryption on all connections to the server. All client computers in
your company are configured to use both the NWLink IPX/SPX and AppleTalk network protocols. The server also connects to a SQL Server 7.0 computer that is running the TCP/IP and Multiprotocol Net-Libraries. You need to configure the SQL Server 2000 computer to support encryption on all connections. You want to make the minimum number of changes to the server. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Install the AppleTalk Net-Library and enable encryption.
B. Install the Multiprotocol Net-Library and enable encryption.
C. Select the Force protocol encryption check box in the Server Network Configuration application.
D. Remove the Named Pipes Net-Library.
E. Remove the NWLink IPX/SPX Net-Library.
Answer: BC

21.You are the administrator of a Microsoft Windows 2000 Advanced Server computer.   You are preparing to install SQL Server 2000 on the computer. The SQL Server computer will be used by various types of client computers that are running different network
protocols, as shown in the following table.

 Company policy requires the SQL Server computer to be configured only for Windows Authentication. You need to select the minimum number of Net-Libraries to install on the SQL Server computer. You also need to
reduce the amount of time spent configuring the SQL Server computer and the client computers. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. On the SQL Server computer, install the IPX/SPX Net-Library.
B. On the SQL Server computer, install the TCP/IP Net-Library.
C. On the SQL Server computer, install the AppleTalk Net-Library.
D. On all Windows 98 computers, install the TCP/IP network protocol.
E. On all Macintosh computers, install the TCP/IP network protocol.
Answer: AC

22.You are the administrator of a new Microsoft Windows 2000 Server computer named CPSRV1. CPSRV1 is a member server in your company's Windows NT 4.0 domain, which is named CPDOMAIN. A separate Windows NT domain named CPUSERS contains all of your company's user accounts. A trust relationship exists between
the CPDOMAIN domain and the CPUSERS domain. You are installing SQL Server 2000 on CPSRV1. You specify a service account for each SQL Server service as shown in the following table.

After the installation is complete, the MSSQLServer service and the SQLServerAgent service do not start. You need to configure the services to start without changing any security settings in either domain.What should you do?
A. Configure the services to run under the local system account.
B. Add CPSRV1 to the CPUSERS domain.
C. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the local Administrators group on CPSRV1.
D. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the CPUSERS\Domain Admins group.
E. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the CPDOMAIN\Domain Users group.
Answer: C

23.You are the administrator of a new Microsoft Windows 2000 Server computer named HQSQL5. HQSQL5 is a member server in your company's Windows NT 4.0 domain, which is named HQMAIN. After installing SQL Server 2000 on HQSQL5, you configure the MSSQLServer service   account to use the HQMAIN\sqladmin user account, which is a member of the local Administrators group on HQSQL5. You can use the HQMAIN\sqladmin user account to log on to HQSQL5. However, the   MSSQLServer service fails to start. You need to start the service. What should you do?
A. Revoke Log on interactively rights for the HQMAIN\sqladmin user account.
B. Add the HQMAIN\sqladmin user account to the HQMAIN\Domain Admins group.
C. Select the Password Never Expires check box for the HQMAIN\sqladmin user account.
D. Grant Log on as a service rights to the HQMAIN\sqladmin user account.
Answer: D

24.You are the administrator of a group of Microsoft Windows 2000 Server computers. You are installing SQL Server 2000 on these computers. You run SQL Server Setup on a server named CORPMAST. You install SQL Server 2000 remotely on a server named CORPSQL2. During the remote installation, the Setup program displays the following error message: "Setup could not verify the target path \\CORPSQL2\C$\Program Files\Microsoft SQL Server. Please verify the target UNC." You verify that \\CORPSQL2\C$\Program Files\Microsoft SQL Server is accessible for the remote installation. You need to correct the problem so that the Setup program can continue. What should you do?
 A. On CORPSQL2, share drive C as Cdrive.Change the   installation path to \\CORPSQL2\Cdrive\Program Files\Microsoft SQL Server.
B. On CORPMAST, log off, and then log on again with credentials that also have   administrative rights on CORPSQL2.Rerun the Setup program.
C. On CORPSQL2, change the installation path to a drive that has sufficient room for   the SQL Server 2000 installation.
D. On CORPMAST and CORPSQL2, configure the network protocols so that the   servers share at least two network protocols.
Answer: B

25.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.
Answer: BF

26.You are the administrator of a SQL Server 2000 computer. You have two new hard disks on which you will create a database named Inventory. You want to insert, update, and delete data as quickly as possible. Which two actions should you take? (Each correct answer presents part of the solution. Choose two.)
A. Configure the hard disks as two mirrored NTFS volumes.
B. Configure the hard disks as one mirrored NTFS volume.
C. Configure the hard disks as two independent NTFS volumes.
D. Configure the hard disks as one extended NTFS volume.
E. Place Inventory_data.mdf on the first volume and Inventory_log.ldf on the second volume.
F. Place Inventory_data.mdf and Inventory_log.ldf on the first volume and    Inventory_data2.ndf and Inventory_log2.ldf on the second volume.
G. Place Inventory_data.mdf and Inventory_log.ldf on the same volume.
Answer: CE

27.You are the administrator of a SQL Server 2000 computer. The server contains a   database that is heavily indexed and that company users query extensively. The database has grown and query response time has slowed. The database is stored in a single data file. You want to accelerate query response time. What should you do?
A. On a new hard disk, create a new filegroup.
   Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
B. On a new hard disk, add a new file to the PRIMARY filegroup.
   Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.
C. On the existing hard disk, create a new filegroup.
   Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
D. On the existing hard disk, add a new file to the PRIMARY filegroup.
   Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.
Answer: A

28.You are the administrator of a SQL Server 2000 computer. The server has a database named WebSales The database stores e-commerce transactions from your Web   server. The data file is configured as shown in the WebSales Data Files exhibit. (Click the <<ItemExhibitName>> button.)

The database options are configured as shown in the WebSales Options exhibit. (Click   the <<ItemExhibitName>> button.)
The database is currently 950 MB and grows about 150 MB per week. Once a month, closed order records are removed from the database. Twice a week, the database responds slowly and has a large amount of disk activity. During this time, the ASP pages on the Web server that are inserting and modifying data time out. You want to minimize disk activity and time-outs. Which three actions should you take? (Each correct answer
presents part of the solution. Choose three.)

A. Increase the size of the database to 2 GB.
B. Clear the Automatically grow file check box.
C. Clear the Auto shrink check box.
D. Add additional indexes to the tables.
E. Set automatic file growth to 10 percent.
F. Select the Auto update statistics check box.
G. Select the Auto create statistics check box.
H. Select the Auto close check box.
Answer: ABC

29.You are the administrator of a SQL Server 2000 computer. The server contains a  database named MedicalRecords Users access medical records by using the PatientID field. This field is the clustered primary key for the Patients table. When users try to access medical records, the database responds slowly. You examine the database options as shown in the exhibit. (Click the <<ItemExhibitName>> button.)
You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database?

A. Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly.
B. Select the Auto update statistics check box.
C. Run the Database Maintenance Plan Wizard, and accept the default settings.
D. Rebuild the primary key as a nonclustered primary key.
E. Clear the Auto create statistics check box.
Answer: B

30.You are an administrator of two Microsoft Windows 2000 Advanced Server computers. On these servers, you are configuring a database that will store accounting information for your company. The data must be available at all times. Interruptions in data connectivity should not last longer than five minutes. Any changes to the database should not require you to reconfigure the client computers.
How should you configure the database?
A. Configure the database on the two servers as a SQL Server 2000 cluster.
B. Configure the database on one server, and then configure a standby database on the second server.
C. Configure the database on each server. Use Microsoft Distributed Transaction Coordinator (MS DTC) to keep the two servers perfectly synchronized.
D. Configure the database as a federated database, and then partition half the data on each server.
Answer: A