Chapter 7

Replication

• To create a subscription

5. After the subscription has been created, in the console tree expand your server, expand Management, expand SQL Server Agent, and then click Jobs.

The job called SQLSERVER-Northwind-Northwind_Products_Pub-SQLSERVER-PullSubs- 0, with a category of REPL-Distribution is the job that starts the Distribution Agent for your new subscription.

Which server s system resources will this job consume? Would a push subscription change the resources being used?

This job will consume the system resources of the subscriber. If a push subscription were used, the system resources of the distributor would be used. For the purposes of this exercise, the subscriber and the distributor are on the same computer, but normally they would be on different computers.

Review Questions

1. Your company has been experiencing contention problems. The problems occur when the sales and marketing departments run their daily reports while the order processing department is processing orders. Your company has decided to implement some form of data replication. To choose the appropriate method for making copies of the company data, which features and characteristics should you consider?

At a minimum, you should consider transactional consistency, latency, site autonomy, security, existing data sources, data update frequency, performance, administration, and whether to allow updating subscribers.

2. Your company, a large, international manufacturer with many vendors around the world, has decided to distribute data by using replication. You currently are in the planning and design phase of your replication strategy. Your goal is to receive all of the sales information from the vendors at the company headquarters on a daily basis. Because most of your vendors are remote, you are concerned about the high long-distance costs. Which replication model or models would you implement? Why?

The best way to address these requirements would be to combine two of the replication models. You could have a central subscriber in each region that would collect all of the daily sales information for the region. The regional central subscribers could then publish the data to company headquarters at night. By using a single central subscriber/publisher in each region, you minimize the long-distance communication costs.

3. What are the best ways to replicate data from an OLTP Server to a data warehouse and from a data warehouse to a data mart?

Transactional replication and snapshot replication, respectively

4. What is the best way to replicate aggregate data?

Snapshot replication