A couple of friends of mine have been asking me to blog about some of the things I have done with custom replication in SQL 2000. I figured a good place to start would be with some basic principles about replication, and then dive deeper into the guts of what I have done over a few blog entries. Hopefully I won’t bore anyone. This post will cover the basics of Transactional Replication. It has been trimmed down for brevity, so please no complaints about “oh you left this out…” you can comment about it yourself and fill it in.
Microsoft SQL Server 2000 has three flavors of replication. Snapshot, Transactional, and Merge. For this series (if you can do that in a blog ;) I will focus on Snapshot and Transactional replication.
To start with, Microsoft uses a magazine metaphor for describing the parts of replication. You start off with a publication and add article(s). This publication is then subscribed to, and then distributed to the particular subscribers on some schedule (yes including continuous).
After you have defined a Subscription, you must synchronize the subscriber with the publisher. This is where the snapshot comes in. When you create the subscription, one of the questions you are asked is whether you want to start the snapshot agent. Selecting yes will start the agent after the wizard is finished. The snapshot agent will script out the table, copy the script over and BCP (ok I am not sure if it is BCP, or DTS. It gets the data there) and put down three stored procedures used by the distribution agent. If you did not select yes, do not worry. The distribution agent will not start sending data until it thinks the data is synchronized.
Now, data gets synchronized by a couple of different methodologies. The snapshot replication, backup and restore, or BCP. Now I assume that Snapshot replication is the preferred method for synchronizing, but we found a few issues with it that I will go into later in the series. Suffice to say, the distribution agent must “think” that the data is in sync. When you create the subscription, you can tell SQL Server that the subscriber already has the data, and SQL Server will take your word for it. Isn’t that special…
While the wizard was creating the snapshot agent job, it also created a couple of other agent jobs. The wizard also created the distribution agent job and the logreader agent job. The logreader job does exactly what it sounds like. It reads the transaction log and creates records in a table called ms_ReplCommands in the Distribution database. This table is the queue that the distribution agents use to serialize the commands that get the data into the subscriber. This job is the conduit that data will flow to the subscriber.
Ok, at this point you have a rough idea of the objects involved. At least the objects that we are going to fiddle with. Stay tuned because I am going to blog about what I don’t like about the snapshot agent, and how I get around it. On the way you learn some cool things about replication with SQL Server 2000.