Microsoft 70-228 Exam - OKExams.com

Free 70-228 Sample Questions:

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: C, F

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. 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

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: A, D

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: A, C, E

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. 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

11. 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: C, E

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: B, C, E