Snapshot replication

Excerpted from the McGraw-Hill/Osborne Media book SQL Server 2000 Administration, this tip explains why snapshot replication is the simplest to set up and understand.

The following tip is excerpted from the McGraw-Hill/Osborne Media book SQL Server 2000 Administration, Chapter 10, 'Replication', written by Mark A. Linsenbardt and Shane Stigler. For a summary and link to the complete chapter, click here.

The simplest method to set up, and perhaps the easiest to understand, the snapshot replication method functions by periodically sending data in bulk format. You will use this method when the subscribing servers can function in read-only environment, and also when the subscribing server can function for some time without updated data.

Functioning without updated data for a period of time is referred to as latency. For example, a retail store uses replication as a means of maintaining an accurate inventory throughout the district. Since the inventory can be managed on a weekly or even monthly basis, the retail stores can function without updating the central server for days at a time. This scenario has a high degree of latency and is a perfect candidate for snapshot replication.

Additional reasons to use this type of replication include scenarios with low-bandwidth connections. Snapshot replication will take a long time, but can be scheduled for a time when other activity on the network is low. Since the subscriber can last for a while without an update, this provides a solution that is lower in cost than other methods while still handling the requirements.

Snapshot replication occurs once in almost all replication scenarios, because SQL Server uses snapshot replication to establish the first copy of data on a subscriber. Later in the chapter, you will learn more about how SQL Server initiates replication. Snapshot replication also has the added benefit of being the only replication type in which the replicated tables are not required to have a primary key.

Snapshot replication works by reading the published database and creating files in the working folder on the distributor. These files are called snapshot files and contain the data from the published database as well as some additional information that will help create the initial copy on the subscription server. SQL Server stores configuration and status information in the DISTRIBUTION Database but stores all actual data in the snapshot files.

In the case of a push subscription, the Distribution agent runs on the distributor and sends the snapshot files to the subscriber. If you are using a pull subscription, the Distribution agent runs on the subscriber and obtains the snapshot files from the working folder on the distributor. The difference is important to understand. The computer that is running the Distribution agent has the higher workload. If you are using a push subscription, the distributor machine carries the majority of the workload. If you are using a pull subscription, the subscribing machine carries the majority of the workload. Depending on the machines in your deployment, you will often choose what type of subscription to use based on the number of users and the total overhead of the servers. In most cases, you will want to place the Distribution agent on the machine with the lower workload.

From the field: Snapshot replication is the most common implementation for small organizations who connect using modems, but do not have to merge data. If your organization uses a modem to connect, and only needs to send the data one way, you will most likely use snapshot replication.

Click for the next tip in this book excerpt: Transactional replication

Dig Deeper on SQL Server Replication