| 1: | You are working for a large international organization that supplies packaging materials for companies that require custom commercial designs. The number of products is becoming too large for the current computer system to handle, and you need to provide a solution that will spread the load over the current server and a new machine coming into the system. Queries need to be performed over a wide variety of products, and there is no predictable pattern to the queries. What is an appropriate technique to implement the changes? A. Configure replication using the new machine as a Subscriber and the original machine as the Publisher/Distributor to balance the workload. B. Separate the table into two smaller tables and place one table on each server. Configure a partitioned view and appropriate constraints on each of the machines. C. Implement multiserver clustering so that each of the two servers can respond to data activities, thus achieving a balanced workload. D. Configure log shipping on both servers to have a copy of the data on each of the servers and propagate all changes to the alternate machine.
|
| A1: | Answer: B. This is a perfect example of where partitioning a table into two smaller objects enables you to use two machines to help reduce the load on the overall application. Remember that failover clustering is the only form of clustering supported by SQL. It therefore does not actually reduce the load; it only assists in obtaining an around-the-clock operation. Log shipping assists in offloading query load, but it does little to reduce update load because it leaves the second server in a read-only state. Merge replication may enable updates to span many servers, but the associated overhead and data latency make it a less than desirable alternative. For more information, see the "Partitioned Views" section of this chapter. |
| 2: | You are the administrator of a SQL Server 2000 computer. The server contains your company's Accounts database. Hundreds of users access the database each day. You have been experiencing power interruptions, and you want to protect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do? A. Enable the torn page detection database option for each database. B. Disable write caching on all disk controllers. C. Create a database maintenance plan to check database integrity and make repairs each night. D. Ensure that the write caching disk controllers have battery backups.
|
| A2: | Answer: D. Good controllers suitable for database use will have a battery backup. The battery should be regularly tested under controlled circumstances. Disabling caching if currently in place is likely to affect performance, as will enabling torn page detection. Torn page detection might help point out whether data is being corrupted because of failures. A maintenance plan is recommended, although it is not an entire solution in its own right. |
| 3: | You are the sole IT person working in a small branch office for a nonprofit organization that deals with natural resource conservation issues. A noncritical database is maintained on the database server. You have been given the task of configuring appropriate database properties that would allow for a minimum use of execution time and storage resources. Which of the following set of properties is most appropriate? A. Full recovery, auto shrink, torn page detection B. Bulk recovery, auto shrink, single user C. Simple recovery, auto close, auto shrink D. Simple recovery, auto shrink, single user E. Bulk recovery, auto close, auto shrink
|
| A3: | Answer: C. Simple recovery uses the least amount of log space for recording changes to the database. Full recovery uses the most space because it fully logs any bulk operations. Bulk recovery represents a midpoint between the two. Auto close frees up resources at the earliest possible point during process execution, and auto shrink minimizes the space used in the file system by periodically reducing the files when there is too much unused space. For more information, see the section "Recovery Interval (Advanced)." |
| 4: | Tom needs to achieve optimum performance within his application. He is examining different view technologies as a way to improve data access performance. He is currently managing a database that is replicated between five servers (for load balancing) and contains customer information for six sales divisions. The Customer table currently contains a consolidated list of 100,000,000 customer records. What could Tom try to implement to improve server performance? A. Indexed views B. Partitioned views C. Complex views D. Full outer views
|
| A4: | Answer: B. Partitioned views, and specifically distributed partitioned views, may improve the data access for Tom's database. Because a large number of rows could be separated by sales division per server (with one server maintaining information for two divisions), his database tables may be ideal candidates for distributed partitioned views. For more information about partitioned views, see the section "Partitioned Views." |
| 5: | You are the administrator of a SQL Server 2000 computer at your company's warehouse. All product orders are shipped out from this warehouse. Orders are received at 30 sales offices throughout the country. Each sales office offers a range of products specific to its own region. Each sales office contains one SQL Server 2000 computer. These servers connect to the warehouse through dial-up connections once a day. Each sales office needs data pertaining to only its own region. You need to replicate inventory data from the server at the warehouse to the servers at the sales offices. You have decided to use transactional replication. You want to minimize the amount of time needed to replicate the data. Which actions should you take? (Choose three.) A. Create one publication for each Subscriber. B. Create one publication for all Subscribers. C. Enable horizontal partitioning. D. Enable vertical partitioning. E. Use pull subscriptions. F. Use push subscriptions.
|
| A5: | Answer: A, C, E. It is necessary to design the structure of the database so that the publications can include an article specific to each location. This will mean having a compound primary key containing regional information and an article for each Subscriber that is horizontally partitioned based on the region. Because the connection is initiated by the Subscriber dial-up, you must also set up pull subscriptions. For more information, see the section "Types of Replication." |
| 6: | You configure transactional replication on the Headquarters server and make numerous publications available. Later, a member of the Sales department subscribes to one of the publications. However, the only article from the publication that he needs is MonthlyRevenues. He needs data quickly and needs to avoid receiving the redundant data that would come from subscribing to the whole publication. What can be done to solve this dilemma? A. Change the style of replication being used to snapshot replication. B. Filter the publication so that only MonthlyRevenues can be replicated; push this subscription to the Sales department. C. Create a new publication that contains only a single article, MonthlyRevenues. D. There is no way to solve this problem. He needs to subscribe to the whole publication.
|
| A6: | Answer: C. Of the options offered, creating a new publication is the best choice because it is the only option that will work. Creating filters can be done only vertically (columns) or horizontally (rows). A more experienced individual could use stored procedures to subscribe to a single article, but this option is not present in the answer choices. For more information, consult the "Partitioned Views" section. |
| 7: | Transactional replication offers low site autonomy, and merge replication offers high site autonomy. What is meant by "site autonomy"? A. The measuring of the consistency of transactions B. How long data in the Subscriber can stay without being renewed C. The independence of one site in relation to others D. How many Subscribers and Publishers are involved in a replication process
|
| A7: | Answer: C. Site autonomy refers to one site's independence from all other sites for processing modifications. Autonomy measures the effect of your site's operation on another. A site that has full autonomy is completely independent of all other sites, meaning that it can function without even being connected to another site. See "Types of Replication" for more information on autonomy and other considerations. |
| 8: | You work in a finance company where changes to the values in the Finance table are made quickly, and thus you want these incremental changes to be propagated to Subscribers as they occur. These Subscribers are always connected to the Publisher with a reliable connection. Which type of replication is used when you want updated changes at the server in almost real-time? |
| A8: | Answer: C. Transactional replication is a good solution when you want updated changes at the server in almost real-time. Because of the frequency of the changes, snapshot replication is not a good solution. Merge replication can be set up in a single direction but is generally used only when the Publisher and Subscribers make updates while connected or disconnected. See the section "Transactional Replication" for more details. |
| 9: | You and Josh are replicating data to multiple Subscribers who need to update data at various times and propagate those changes to the Publisher and to other Subscribers. These Subscribers need to be able to make changes offline and later synchronize data; this means that they need a replication strategy that offers high autonomy. Which type of replication offers almost complete site autonomy? A. Snapshot replication B. Snapshot replication and transactional replication C. Merge replication D. Transactional replication E. Transactional replication with Updating Subscribers
|
| A9: | Answer: C. Merge replication allows the most flexibility and adds the most autonomy to the replication process, enabling the Publisher and Subscriber to work virtually independently. The Publisher and Subscriber can combine their results and updates at any time. See the section "Merge Replication" for more details. |
| 10: | Your company has just purchased an accounting application from a vendor. The application stores its data in a database named Accounting. 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 accurate 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.) 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.
|
| A10: | Answer: B, E. Because there is no primary key and no other changes to the database can be performed, the only alternative that can be used is snapshot replication. Because the data does not need to be up-to-the-minute, a scheduled data refresh occurring overnight or during other nonpeak times is most appropriate. See the section "Use of Snapshot Replication" for more details. |
| 11: | Your company has just purchased an accounting application from a vendor. The application stores its data in a database named Accounting. 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 infrequently. Most reports will run each month, but the accounting department needs to have the ability to run reports at any time. Reports should be accurate through the last full working day. Disk space is at a premium and you want to conserve space as much as possible. Which action should you take? A. Use transactional replication. B. Use snapshot replication. C. Use a linked server for reporting. D. Use DTS to the reporting server. E. Use XML to the reporting server.
|
| A11: | Answer: C. Use of a linked server will allow for heterogeneous data access without taking up any additional disk space. The other solutions use some disk space; without this restriction, all the other solutions are possible. |
