|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-03-26 10:24:51), 站内信件 |
| 121. You are the database administrator for a financial services company. Employees enter data 24 hours a day into a SQL Server 2000 database. These employees report slower response times when new account information is gathered from branch offices and added to the database. You currently use the following BULK INSERT statement to add the account information.
BULK INSERT finance.dbo.customers FROM 'd:\bulk\accts143_10142000.txt' WITH DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', TABLOCK You want to ensure that response time does not slow when new account information is added to the database. What should you do? A. Drop the indexes on the Customers table before the data load, and then re-create the indexes after the data load is complete. B. Remove the TABLOCK option from the BULK INSERT statement. C. Add the BATCHSIZE option to the BULK INSERT statement, and then set the option equal to 10 percent of the number of rows to be loaded. D. Add the ROWS_PER_BATCH option to the BULK INSERT statement, and then set the option equal to 10 percent of the number of rows to be loaded. Answer: B Reason: 设置TABLOCK提高性能的关键所在，但是表级锁降低了其他用户的操作性能。 ====================================================================== 控制锁定行为 bcp 实用工具和 BULK INSERT 语句接受 TABLOCK 提示，该提示使用户得以指定所使用的锁定行为TABLOCK 指定在大容量复制过程中获取一个大容量更新表级锁。使用 TABLOCK 可以提高大容量复制操作的性能，因为减少了表上的锁争夺。例如，若要将数据从 Authors.txt 数据文件大容量复制到 pubs 数据库的 authors2 表中，并指定表级锁，请在命令提示符下执行以下命令： bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK" 另外，也可以在 SQL 查询分析器这样的查询工具中使用 BULK INSERT 语句来大容量复制数据： BULK INSERT pubs..authors2 FROM 'c:\authors.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', TABLOCK ) 如果没有指定 TABLOCK，默认为使用行级锁，除非 table lock on bulk load 选项的设置为 on。使用 sp_tableoption 设置 table lock on bulk load 选项可以为表设置大容量装载过程中的锁定行为。 ====================================================================== 122. You are the administrator of an SQL Server 2000 computer. You create a Data Transformation Services (DTS) package. The package is stored as a local package on the server. The package exports data from an online transaction processing (OLTP) database system to an online analytical processing (OLAP) database system that is located on a second SQL Server 2000 computer. You want the package to execute automatically each evening at 8:00 P.M. What should you do? A. Use SQL Server Enterprise Manager to create a new job Create a CmdExec job step that runs the dtsrun utility Schedule the job to run each night at 8:00 P.M. B. Use SQL Server Enterprise Manager to create a new job Create a Transact-SQL job step to query data from the OLTP database Create a second Transact-SQL job step to launch the DTS package Schedule the job to run each night at 8:00 P.M. C. Create a batch file that runs the dtsrun utility Use the Microsoft Windows Task Scheduler to run the job each night at 8:00 P.M. Configure the Windows Task Scheduler to use the local system account D. Move the DTS package to the repository Configure the SQLServerAgent service on the OLAP database server to update the package each night at 8:00 P.M. Answer: A Reason: A C是2个可能入选的答案，但是由于C使用系统账号，系统账号不一定具备SQL的访问权利，排除。 ====================================================================== 如何在企业管理器中调度一个DTS包，步骤如下： >>器组-〉服务器-〉管理-〉SQL Server 代理 >>点击作业-〉新作业 >>完成常规表的信息，然后选择步骤表。 >>单击新建，选择类型中的操作系统命令。 >>在命令中输入dtsrun命令。 ====================================================================== 123. You are a database administrator in the Los Angeles branch office of a specialty foods supplier. A mainframe database at the headquarters contains all company data. Each branch office contains a SQL Server 2000 computer that imports regional data from the mainframe database. The server in Los Angeles contains a Data Transformation Services (DTS) package that uses OLE DB to connect to the company's mainframe database. The DTS package extracts and transforms data about buyers and products for that region. The DTS package then writes the data to the SQL Server database in Los Angeles. You need to configure a SQL Server computer for a new branch office in Sydney. You want to copy the Los Angeles package and modify it so that it writes data to the SQL Server database in Sydney. You use the DTS Designer to modify the DTS package so that it imports regional data from the mainframe database to the server in Sydney. The modified DTS package runs successfully on the server in Los Angeles. You save this DTS package to the server in Sydney, but the server in Sydney cannot connect to the mainframe database. You want to enable the server in Sydney to connect to the mainframe database. What should you do? A. Change the connection properties in the DTS package so that the package uses new login credentials to connect to the mainframe database. B. Modify the workflow in the DTS package so that the server in Sydney is included. C. On the server in Sydney, install an OLE DB provider for the mainframe database. D. On the server in Sydney, delete and then re-create the DTS package. Answer: C Reason: C 的理由是很明显的，异种数据库连接需要接口驱动程序。 124. You are the administrator of a SQL Server 2000 computer. The server contains your company's order processing database. Two-hundred operators take orders by telephone 24 hours a day. Three-hundred data entry personnel enter data from orders received by mail. To ensure that order data will not be lost, your company's disaster recovery policy requires that backups be written to tape. Copies of these tapes must be stored at an off-site company location. Orders must be entered into the database before they can be filled. If the server fails, you must be able to recover the order data as quickly as possible. You need to create a backup strategy that meets the company requirements and minimizes server workload. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.) A. Perform a combination of full database and filegroup backups. B. Perform a combination of full database and file backups. C. Perform a combination of full database, differential, and transaction log backups. D. Back up the data to a local tape drive. E. Back up the data to a network share, and then use enterprise backup software to write the disk backups to tape. Answer: C E Reason: C 方案是目前采用最多的备份方式，恢复的速度也是比较快的一种，虽然没有全备份恢复的速度快，但备份所消耗的资源小，速度快。至于E倒是有些奇怪，这样做的结果可以减少负载吗？不过这样备份所需要的时间倒是比较少的。 125. You are the administrator of a SQL Server 2000 computer. The server contains a database named CustomerManagement that tracks customer requests for product information. The CustomerManagement database has two filegroups. The PRIMARY filegroup stores current transactional data, and the SECONDARY filegroup stores historical data. Your backup strategy includes full database and transactional log backups. A portion of the header information for the current week's backups is shown below: BackupName BackupType BackupFinishDate ---------------------------- --------------- --------------------------- custmgmt_full_20000625 1 2000-06-25 21:57:03.000 custmgmt_tlog_20000626_1 2 2000-06-26 10:34:21.000 custmgmt_tlog_20000626_2 2 2000-06-26 14:36:32.000 custmgmt_tlog_20000626_3 2 2000-06-26 18:42:11.000 custmgmt_tlog_20000627_1 2 2000-06-27 10:33:37.000 custmgmt_tlog_20000627_2 2 2000-06-27 14:35:18.000 On June 27, 2000, at 4.18 PM, an application developer accidentally deletes the current customer requests and immediately notifies you of the problem. You want to recover as much data as you can as quickly as possible to minimize server downtime. What should you do? A. Back up the transaction log. Restore the PRIMARY filegroup to a new location. Restore subsequent transaction logs in sequence. Use the STOPAT option to restore the final transaction log backup. Import the deleted data from the new location. B. Back up the transaction log. Restore the PRIMARY filegroup to the existing database. Restore subsequent transaction logs in sequence. Use the STOPAT option to restore the final transaction log backup. C. Back up the transaction log. Restore the entire database to a new location. Restore subsequent transaction logs in sequence. Use the STOPAT option to restore the final transaction log backup. Import the deleted data from the new location. D. Restore the PRIMARY filegroup to the existing database. Restore subsequent transaction logs in sequence. Answer: B Reason: ====================================================================== 如何还原到某个即时点 (Transact-SQL) 还原到某个即时点 执行使用 NORECOVERY 子句的 RESTORE DATABASE 语句。 执行 RESTORE LOG 语句以应用每个事务日志备份，同时指定： 事务日志将应用到的数据库的名称。 要从其中还原事务日志备份的备份设备。 RECOVERY 和 STOPAT 子句。如果事务日志备份不包含要求的时间（例如，如果指定的时间超出了事务日志所包含的时间范围），则会生成警告，并且数据库将保持未恢复的状态。 示例 下例将数据库还原到它在 1998 年 7 月 1 日上午 10:00 点的状态，并且举例说明涉及多个日志以及多个备份设备的还原操作。 -- Restore the database backup. RESTORE DATABASE MyNwind FROM MyNwind_1, MyNwind_2 WITH NORECOVERY GO RESTORE LOG MyNwind FROM MyNwind_log1 WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM' GO RESTORE LOG MyNwind FROM MyNwind_log2 WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM' GO ====================================================================== ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |