Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2000 Replication 101: Terminology, Types, and Configuration : Page 2

It's wise to make sure you have a clear understanding of replication terminology, types of replication, and exactly what is occurring when you install and configure replication.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The Publish/Subscribe Metaphor
SQL Server replication adapts a publish/subscribe metaphor to name various replication roles and objects. You define certain data sources as publications that become available for replication. When you have database servers subscribe to the publication, replication begins. The publish/subscribe metaphor is pretty good, but don't take it too literally. Remember, you're publishing data from a database, not publishing a magazine. The publish/subscribe metaphor works well when you use it to help understand replication's components and roles, but do not expect all of the elements of a magazine or newspaper publishing business to apply.

Publisher, Publications, and Articles
The SQL Server that you define as having the origin of the data you want to replicate is called the publisher, or publishing server. A publisher is always one SQL Server, and you can only define a given SQL Server as a replication publisher once. It is logical to think of a publisher server first, but as you'll learn below in the section on configuring publishing, you actually have to define the distributor before you define the publisher. You can replicate SQL Server 2000 database tables, views, and stored procedures. When you replicate a table or view, you are sending data; when you replicate stored procedures, there are some restrictions on what you can send: snapshot replication will send the text of a stored procedure, but transactional replication will only send the execution of stored procedures.

For each database on the publisher SQL Server, you can define selected sets of tables and views (and sometimes stored procedures) containing the data you want to replicate. Each defined set of tables and views is called a publication. A publication must exist in one database only; it cannot span databases. Within a publication, each table or view is called an article. An article can be an entire table or view, or just a subset. You can publish a subset of a table or view's columns, and you can also restrict the rows of the table or view by using a WHERE condition when you define the article. There are other specialized methods of filtering as well, which you'll learn about in the sections on Transactional and Merge replication.

When you define a publication, you choose which type of replication to use; every publication must be a snapshot, transactional, or merge publication. Once you've defined the type of the publication, your options for creating articles are adapted to the type of replication you've chosen. You'll learn more about these types in the next section. Distribution
Before you can set up a replication publisher, you must define a distributor. The distribution server contains at least one distribution database in which SQL Server places system tables and procedures unique to replication.

The distribution server plays a major role in snapshot and transactional replication as a store-and-forward location for replicated data. However, it only plays a minor role in merge replication. In transactional replication, where the distribution server plays a store-and-forward role for data, it's more common and often more efficient to locate the distributor on its own SQL Server. On the other hand, with merge replication it's very common to make the publisher server do double-duty as the distributor server. Subscriber and Subscriptions
Once you have defined publications on a publisher server, other data sources known as subscribers can then receive or interact with the replicated data. You can create either a push or a pull subscription. You define a push subscription at the publisher, and you define a pull subscription on the subscribing server.

The limitations and behaviors of subscribers and subscriptions can change depending on the type of replication you're using. You'll learn more about the specifics of each subscriber behavior in the next section called Types of Replication.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date