This chapter is from the book
This chapter is from the book
This chapter is from the book
Replication Topology
Now that you've picked the type of replication you want, you'll need to do a little infrastructure work.
For a database to allow replication, it must be configured to do so. This is done through a wizard or with commands. This process is performed for all the databases within replication scheme— such as the Distributors, Publishers and Subscribers.
SQL Server 2000 uses system-level objects to perform replication, and it stores this information in tables in either the user databases or a separate database. If you use the wizard to set up your replication, you can also specify this separate database.
Let's continue with a little more theory.
Distributors
The first function to set up is the Distributor, which is the machine that distributes data. The Distributor makes the Publication available to the Subscriber. You set it with a wizard or with a command.
This machine stores the data to be replicated on tables and files that it creates, so you'll want to ensure that you have enough room to do that. The Distributor will make a copy of all the data that's replicated, so take that into account when determining the drive-space impact. SQL Server 2000 can place the snapshot type of replication's data on another location, and even compress the data to a CAB file.
This machine also need to be able to "talk" to the Subscriber, so make sure you take into account the bandwidth, firewall placement, and other network issues.
There are often a lot of benefits of moving this function to its own sever, such as space distribution and security issues.
Publishers
Publishers are the servers that have the data you want to replicate. This server takes the heaviest load, depending on the type of replication you select. For instance, snapshot replication that is scheduled for late at night when no one is on the server is less of an impact than is constant transactional replication.
You set a server to be used as the Publisher through the Replication Wizard or by using a stored procedure.
Now that you've decided on the Publisher and Distributor, you'll need to define the Subscribers.
Subscribers
The Subscribers are the databases that get the data. They will need to be able to access the Distributor, unless you're using the snapshot offline option I mentioned earlier.
The configuration of the Subscriber depends a great deal on the type of replication that you choose. If you've picked a push-snapshot, then you'll configure the Subscriber differently than if you picked merge-pull.
Now that you've planned the machines that will house the various functions, let's explore the data you'll replicate.
Articles
Articles are the base-level objects you'll want to replicate. Here's a list of what you can include, direct from Books Online:
- Tables
- Stored Procedures—Definition
- Stored Procedu—Execution
- Views
- Indexed Views
- Indexed Views as Tables
- User-Defined Functions
You'll see exactly how to add these items to the publication in the Graphical and Command Line sections a little later on.
Publications
Publications are the Articles you wish to send. You could select a table as one Article, a view as another, and then combine those Articles as a Publication.
Remember that the Subscriber always subscribes to a Publication, not an Article. Even if you select just one Article, you'll need to make it a Publication to transfer the data.
Subscription
The Subscription is the function that receives the publication. This is often another SQL Server, but can include other types. You can replicate to databases such as Oracle and Microsoft Access. The Subscriber database is set using the wizard or commands.