1、微软认证 70-228-试卷 2 及答案解析(总分：14.00，做题时间：90 分钟)一、单选题(总题数：7，分数：14.00)1.You are the administrator of a SQL Server 2000 computer. Each evening after business hours, you perform database maintenance tasks. You create a Data Transformation Services package to populate the data warehouse. MAPI is not installe
2、d on the server.You want to schedule the DTS package to run each night. You want to attend to other duties while the DTS package is executing. You also want to be notified if the DTS package fails.What should you do?（分数：2.00）A.Select the DTS packages log package execution to SQL server check box. Cr
3、eate a SQL server event alert to notify you if the package fails.B.Select the DTS packages write completion status to event log check box. Create a SQL server event alert to notify you if the package fails.C.Configure the DTS package to include an on failure precedence constraint and a send mail tas
4、k. Configure the send mail task to notify you if the package fails.D.Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the execute process task to execute a not send command that will notify you if the package fails.2.servers msdb database in an
5、 e-mail message to the administrator of another SQL Server computer.You create a job that contains the following steps:-Detach the msdb database-Send the database to the administrator in an e-mail message-Reattach the msdb databaseYou test the job, and it fails on the first step. You need to ensure
6、that the msdb database is mailed to the administrator every night.What should you do?（分数：2.00）A.Ensure that the SQLServerAgent service is running under a user account that has database owner access to the msdb databaseB.Delete the first and last steps in the jobC.Configure the job to back up the msd
7、b database to a temporary file. Send the file to the administrator in an e-mail messageD.Insert a new job step before the first step. Configure the new step to terminate all processes that use the msdb database3.You are the database administrator for an accounting company. You are installing SQL Ser
8、ver 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 se
9、rver 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_CP437_BIN collation. You need to configure the
10、 correct collation for the applications without diminishing the performance of the SQL Server computer.What should you do?（分数：2.00）A.Rerun the 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.U
11、se 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.4.You are the database administrator of a SQL Server 2000
12、computer. The server contains your companys Accounts database. Hundreds of users access the database each day.Because you have had power interruptions in the past, you want to perfect the physical integrity of the Accounts database. You do not want to slow down server operations.What should you do?（
13、分数：2.00）A.Enable the torn page detection database option for each database.B.Disable write caching on all disk controllers.C.Ensure that write caching disk controllers have battery backups.D.Create a database maintenance plan to check database integrity and make repairs each night.5.You are the admi
14、nistrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configured as shown in the exhibit. （分数：2.00）A.Create a parameterised stored procedure to retrieve the data.B.Create a denormalized table that is maintained by triggers.C.Use ad hoc queries to re
15、trieve the data.D.Create a nonclustered index on the primary key of each table.6.You are the administrator of a SQL Server 2000 computer. The server contains a database that stores inventory data. Another database administrator has created a number of scheduled jobs to maintain the inventory databas
16、e. Each weeknight the following jobs occur.- A BULK INSERT job imports data at 10:00 P.M.- Indexes are rebuilt at 10:15 P.M.- Data integrity checks are performed at 10:30 P.M.- A differential backup is performed at 10:45 P.M.- A DBCC SHRINKDATABASE job runs at 11:00 P.M.You notice that the final job
17、 often fails and returns the following error message: “Server Msg 3140, Level 16, State 3. Could not adjust the space allocation for file inventory_data.“You need to ensure that the final job runs without errors. What should you do?（分数：2.00）A.Create a new DBCC SHRINKFILE job to shrink the inventory_
18、data file. Schedule the new job to run at the same time as the DBCC SHRINKDATABASE job.B.Modify the DBCC SHRINKDATABASE job so that it uses DBCC SHRINKFILE statement to shrink each file individually.C.Increase the time between the data integrity checks and the differential backup.D.Increase the time
19、 between the differential backup and the DBCC SHRINKDATABASE job.7.You are the administrator of a SQL Server 2000 computer in your companys personnel department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown in the exhibit. （分数：2.00）A.Use the bcp ut
20、ility to export data from each table to a separate text file. Use format files to select the appropriate columns. Merge the data from each text file into a single text file.B.Create a view that joins data from all three tables include only the columns you want to appear in the text file. Use the bcp
21、 utility to export data from the view.C.Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a local temporary table. Use the bcp utility to export data from the local temporary table to a text file.D.Create a SELECT qu
22、ery that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a global temporary table. Use the bcp utility to export data from the global temporary table to a text file.微软认证 70-228-试卷 2 答案解析(总分：14.00，做题时间：90 分钟)一、单选题(总题数：7，分数：14.00)1.You are the
23、 administrator of a SQL Server 2000 computer. Each evening after business hours, you perform database maintenance tasks. You create a Data Transformation Services package to populate the data warehouse. MAPI is not installed on the server.You want to schedule the DTS package to run each night. You w
24、ant to attend to other duties while the DTS package is executing. You also want to be notified if the DTS package fails.What should you do?（分数：2.00）A.Select the DTS packages log package execution to SQL server check box. Create a SQL server event alert to notify you if the package fails.B.Select the
25、 DTS packages write completion status to event log check box. Create a SQL server event alert to notify you if the package fails. C.Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the send mail task to notify you if the package fails.D.Configu
26、re the DTS package to include an on failure precedence constraint and a send mail task. Configure the execute process task to execute a not send command that will notify you if the package fails.解析：解析：Explanation: When jobs are completed (successfully or not), the subsystem returns a result status (
27、with optional messages) to SQL Server Agent. SQL Server Agent then records the completion status in both the operating systems application log and the job history table in SQL Server and optionally sends an e-mail report or an SQL Server event alert of the job status to the designated operator. Inco
28、rrect Answers: A: We are interested to when the package has completed. The completion status, not the log package execution, should be used to trap the completion of a package. The execution status concerns Last run time, Last run status, Last run message, etc. C, D: The Messaging Application Progra
29、mming Interface (MAPI) isnt installed. MAPI is needed for SQL Server to send e-mail. No e-mail can be send by SQL Server in this scenario.2.servers msdb database in an e-mail message to the administrator of another SQL Server computer.You create a job that contains the following steps:-Detach the ms
30、db database-Send the database to the administrator in an e-mail message-Reattach the msdb databaseYou test the job, and it fails on the first step. You need to ensure that the msdb database is mailed to the administrator every night.What should you do?（分数：2.00）A.Ensure that the SQLServerAgent servic
31、e is running under a user account that has database owner access to the msdb databaseB.Delete the first and last steps in the jobC.Configure the job to back up the msdb database to a temporary file. Send the file to the administrator in an e-mail message D.Insert a new job step before the first step
32、 Configure the new step to terminate all processes that use the msdb database解析：解析：Explanation: The current job fails since the detach msdb statement fails. It fails because detaching a database requires that no one is using the database. The msdb database is used by the SQL Server Agent service, w
33、hich performs scheduled activities such as backups and replication tasks. We want to send the msdb database. The msdb database could be detached if all processes that use it where terminated. Then we could send the database file and reattach the msdb database. This would stop scheduled tasks from be
34、ing run during this period. A better approach would to be to backup the database and send the temporary file instead. Note: A new SQL Server 2000 installation automatically includes six databases: master, model, tempdb, pubs, Northwind, and msdb. The master, model, and tempdb databases cannot be det
35、ached. Incorrect answers: A: The problem that the SQL Service Agent is using the msdb database. This problem would still be there even user account running the SQL Server Agent service is changed. B: The msdb database is used by the SQL Server Agent service and the database file would be locked. It
36、cannot be sent directly in an e-mail message. D: The msdb database could be detached if all processes that use it where terminated. Then we could send the database file and reattach the msdb database. This would stop scheduled tasks from being run during this period.3.You are the database administra
37、tor 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_
38、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
39、SQL_Latin1_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?（分数：2.00）A.Rerun the SQL Server Setup and specify the SQL_Latin1_General_CP437_BIN collation.B.Run the rebuildm utility a
40、nd 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.
41、解析：解析：Explanation: The collation of a database can be specified in the COLLATE clause of the ALTER DATABASE statement. This will change the default collation of the database that was specified when the database was created, or to alter the collation for a database where the collation was not specifi
42、ed when the database was created and thus the default collation of the server was used. Incorrect Answers: A: The collation of a database can be altered using the COLLATE clause of the ALTER DATABASE statement. It is therefore not necessary to reinstall the instance of SQL Server 2000 or to rerun th
43、e setup program. Furthermore, rerunning the SQL Server 2000 setup program would disrupt the performance of the SQL Server computer. B: The rebuildm utility alters the master database and can be used to change the collation settings for an instance of SQL Server 2000. However, it is only the Financia
44、l database that needs to be altered as it is financial application is the only application that uses the SQL_Latin1_General_CP437_BIN collation. D: The COLLATE clause can be specified at different levels. It can be specified for the database, a table, a column or an expression. In this scenario the
45、collation can be altered at the database level as this will reduce administrative effort when new tables are added to the database.4.You are the database administrator of a SQL Server 2000 computer. The server contains your companys Accounts database. Hundreds of users access the database each day.B
46、ecause you have had power interruptions in the past, you want to perfect the physical integrity of the Accounts database. You do not want to slow down server operations.What should you do?（分数：2.00）A.Enable the torn page detection database option for each database.B.Disable write caching on all disk
47、controllers.C.Ensure that write caching disk controllers have battery backups. D.Create a database maintenance plan to check database integrity and make repairs each night.解析：解析：Explanation: The scenario states that power interruptions have occurred in the past. By buying a battery backup solution f
48、or the disk controllers the power interruption problem would be prevented. Incorrect Answers: A: Torn page detection is a reactive solution which would slow down performance since restores would have to be made. It would better to use a preventive solution. Note: Torn page detection: If a torn page
49、is detected, the database must be restored from backup because it will be physically inconsistent. B: Caching is very import for performance. Disabling write-caching would be a bad idea. D: Repairing the database every night is not a good solution. Database errors cannot be accepted. It is better to prevent the problem instead of trying to fix it after it has happened.5.You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configured as shown in the exhibit. （分数：2.00）A.Create a parameterised stored proce
