SQL Server Replication
This article is excerpted from Essential SQL Server 2000: An Administration Handbook (Addison Wesley, 2001, ISBN: 0201742039).
This chapter is from the book
This chapter is from the book
This chapter is from the book
Defining replication is pretty easy. The basic idea is that you want to copy data from one place to another, automatically. SQL Server 2000 can do that for you.
While the concept of replication is simple, several functions are involved, such as collecting the data, defining the conditions for moving the data, and moving the data.
These functions can be installed on different servers or on the same system, but the processes they use will be the same regardless of where they are installed.
The following functions and terms are used in replication:
- Publisher
- Publication
- Article
- Distributor
- Subscribers
- Subscription
The Publisher is the server that has the data you want to replicate. It makes the data available in a package called a Publication. You can set this server to "push" the data out to the receiving server, or have the receiving server "pull" the data in.
A Publication is made up of the various pieces of data you can replicate, such as tables, views, and so on—each individually called an Article.
The Distributor is the next function in the transfer. This function does the work of transferring publication out, and its job changes a bit based on the type of replication you are doing.
The system that receives the data is called the Subscriber. This server can request to "pull" the data from the Publisher rather than the Publisher "pushing" the data.
The Subscriber server, then, will have the information required to get the data from the Publisher, called a Subscription. It's important to note that a Subscription is to a Publication, not an Article. In other words, the Subscriber doesn't pick and choose the data that sent to it, all of the data is what it receives. That can become an important distinction.
SQL Server 2000 uses services called Agents that watch the various pieces of data and schedules to perform the replication.
So to summarize, a Publisher makes a group of information (the Publication, composed of Articles) available for replication. The Distributor performs the work of actually transferring the data to a Subscriber, which has a Subscription for that Publication.
There are two parts to setting up replication—planning and implementing. Never skip the planning step; this simply isn't one of those things that you can follow a wizard for and expect to work.
This planning is normally done in the order I describe here, but you will find that you'll move back and forth through the steps as each decision affects the others.
The first thing you need to plan for replication is the type of replication. The types of replication have a great deal to do with how you'll set up the rest of your plan.
The types are
- Snapshot
- Transactional
- Merge
Snapshot replication is taking an entire set of data and replicating it to another database. This is the most straightforward, easy to set up type, but usually has the largest data size.
Because of its size, this type is not always the best choice for disconnected remote clients. It's normally better suited to static data sets such as lists and catalog items.
Transactional replication takes an initial snapshot of data as well, but then tracks the changes that have been made at the Publisher and sends them to the Subscriber. This type of replication is typically smaller, and is often used for remote clients.
Merge replication allows for updates from both the Publisher and Subscriber of the data. This is very useful for remote clients who make changes in a disconnected fashion in the field to keep the office data set current, as well as receive changes from the office. This type can be a bit more involved to implement due to conflict resolution.
Once you've decided on the type of replication, you'll also need to decide on the physical layout. It's at this step that you decide which server will be the Publisher and which will be the Distributor. Often these are the same machine, but the load placed on your server from each function will have a performance impact.
Another impact in the physical layout choice is the network position of the server.
If the Publisher is behind a firewall, it may be necessary to have the Distributor on the other side of the firewall so that your remote clients can access the Distributor.
The next step in the plan involves selecting the data that you want to replicate.
You can replicate many forms of data, from a complete table to only parts of it, stored procedures or their output, views or their output, and even a user-defined function or its output. Often you'll want just a small subset of your data at another site, and sometimes you need an entire database to be sent to the Subscriber. The factors to consider here are the size of the data and the speed and quality of the connection, and of course the use of the data.
The next decision will be whether the Publisher sends the data along at a defined point, called a "push" subscription, or whether the Subscriber will go get the data at a specified point, called a "pull" subscription. If the user is using a laptop, you may not know when they are online, so you'd want a pull subscription. If you have a constant-connected link to the Subscriber you'll normally set up a push subscription.
Not only are the requirements a factor in this decision, setting the replication timing is vital, especially on push subscriptions. If the links to the Subscribers are constant-connected but very slow, the data should be transferred more often so that it doesn't pile up. This timing aspect is probably one of the most difficult parts of planning.
Now that you have your functional planning done, you'll set up your replication schema.
Setting up the schema involves defining all the pieces from the terms I mentioned earlier. The Publisher, Subscribers, Articles and the rest are all detailed at this stage.
Finally, you'll set the monitoring in place to make sure your replication works. Whenever you set up a process to happen automatically, whether it is backups or any other server process, you don't let it run blindly.
While you do have a feedback mechanism (via e-mail) to monitor maintenance plans, that process is a bit more hands-on with replication.
Let's begin the planning process with exploring the types of replication.
Types Of Replication
There are three main types of replication:
- Snapshot
- Transactional
- Merge
Snapshot
Snapshot replication is taking the data you want to move, in its entirety, and copying it to another server.
Snapshot replication is really well-suited to users who need an off-line copy of some data.
Suppose your sales force has a price list that should be updated daily. You might set the database at their site to pull data early in the morning and then use the data in a static mode throughout the day.
Because you're copying an entire data set that might be quite large, you'll want to limit how often you set this replication to happen.
Transactional
Transactional replication is where the changes on one server are replicated to another. This means that each time your users perform an insert, select, update, or delete on data in the Publisher, the Distributor sends it on to the Subscriber. This can happen instantly or pile up to go at a certain point in time.
One important thing to keep in mind with this type of replication is that it's an all-or-nothing proposition. If one of the changes in a transaction fails to go to the Subscriber for whatever reason, none of the changes will go. Because of that, this is the most difficult type of replication to monitor, although it's often one of the most useful types.
Transactional replication is used when you need to keep two data locations in sync, or if the data just gets too big to replicate with snapshot replication. Let's look at another example.
Headquarters records sales all day long, and inventory is reduced. Field offices need to know the new inventory counts instantly so that they don't sell what they don't have.
With these requirements, you'd set up transactional replication with the data pushing from the Publisher at headquarters out to the Subscriber servers at each office as soon as the changes occur. This process assumes that you have a constant connection to each office.
An interesting feature is the ability of the Subscriber to make changes based on received data and send those changes back to the Publisher, called two-way replication.
While you can use transactional replication over disconnected or dial-up methods, it requires a bit more monitoring.
Merge
Merge replication allows two copies of a data set to be synchronized. The toughest thing to decide here is how to handle conflicts, but if your data lends itself to this type of replication, you shouldn't encounter that too often.
The process involves sending an initial snapshot to the replication partner, and at synchronization the two systems accept each other's data. If there is a conflict, you have a lot of options on how to handle them, including setting default options (like Server A always wins) or having the system prompt you for what to do. Let's look at another example.
The sales force tells you that while they enjoy having a copy of the inventory, what they'd really like is to be able to record actual sales information on the spot. To do this, they need to reduce inventory by the sales amount and bill the customer.
To fulfill this requirement, you'd set up merge replication between a database on their laptops and the server. You could replicate only the data from their region if you wish, guaranteeing that they wouldn't see each other's customers.
One thing to keep in mind is that merge replication is the most restricted type—you can't merge-replicate the results of a stored procedure or an indexed view.