SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Snapshot Replication

By Andy Warren,

Replication offers some interesting ways to solve problems. We get a fair number of questions in the discussion area about when to use which type of replication. Sometimes that is an easy decision, sometimes not. You really have to know a good bit about how all the types of replication work AND have a good understanding of the business problem. In the next few articles I'm going to explore replication and talk about some of the issues that you might not think about when deciding to use replication.

Let's start with snapshot replication. I would guess that this method is the least used (we'll put a poll up soon to see!) but it does have it's uses. I'll put up a tutorial on how to do it in a future article, for now let's talk about how it works at a high level, something like this:

  • Create the publication (basically a one time set up)
  • Snapshot agent runs which creates at least three files. One to create the tables involved, one to create the indexes for those tables, and one bcp file per table.
  • Snapshots get applied to subscriber at whatever schedule makes sense

What that means to us is that we typically will have a very high latency. Changes accrue on the publisher and at some point we just make a complete copy and send to the subscriber to replace the existing one. Depending on the table size it also requires a significant amount of bandwidth. Both merge and transactional require the same snapshot, the difference is they only need it once. With snapshot you're doing it over and over again.

So what would be a good situation to use snapshot? How about a fairly static look up table? States. Zip codes maybe. Changes to those would be pretty rare and probably a few hours or even a day of latency probably wouldn't matter. Even here, why not use transactional and only send the changes which we know will be few? Why keep sending over data that probably hasn't changed?

Probably not the best example. Hoping someone will offer some good ones!

Let's look at it from another angle. Why would I NOT want to use transactional or merge? Aside from the apparent (and sometimes real) complexity compared to snapshot, I can make some good arguments for not using them:

  • No log reader required. Log readers take about a meg of memory. They also have to run near continuously (the log reader actually reads the transaction log to determine if each transaction meets the criteria for replication. Either way, once it has finishing it marks the transaction as clear, allowing you to remove it from the transaction log).
  • No restrictions on schema changes. With SQL2K you can at least add or remove columns without a lot of work, changing an existing column is a pain. In SQL7 any change is a pain.
  • No requirement to have a primary key (or a GUID for merge).

None of those are insurmountable. The last one is actually something I ran into recently. We needed to replicate a table used by a legacy app and the table had no primary key defined and no combination of keys that I could find to create one, so I added an identity column. That broke the legacy app. Lacking the time and knowledge of how the app worked, I opted for snapshot as a short term solution. Don't underestimate the value of being able to make schema changes easily! What would be a simple change becomes a serious task.

One point in favor of transactional over snapshot is how the snapshots are processed. A standard snapshot will lock the tables during the BCP step to make sure you get a valid copy. Transactional in SQL2K offers the option of a 'concurrent' snapshot which uses the log reader in conjunction with the snapshot agent to reduce locking.

Hopefully that leaves you with more questions than answers! Anyone out there using snapshot? Give me a good reason why it was the right choice? Interested in a detailed walk through of how it works in a follow up article? Post a comment in the attached discussion forum!

Total article views: 11703 | Views in the last 30 days: 9
Related Articles

snapshot and transactional replications

snapshot and transactional replications


Snapshot Agent in Transactional Replication

Function of Snapshot Agent in Transactional Replication


Snapshot Replication - Transactions after snapshot replication starts are replicated to subscribers as part of Snapshot

Snapshot Replication - Transactions after snapshot replication starts are replicated to subscribers ...


Transactional Replication without snapshot replication (SQL Server 2005)?

Can a transactional replication be created without snapshot replication?




sql server 7