Events And Processes - Microsoft SQL Server Black Book pdf

Now that we have covered the terminology and basic models, let’s go behind the scenes. As mentioned, replication is transaction log–based. The major components of replication are the published databases themselves, the

administration of the transaction log for any replicated database, the log reader task, and the synchronization process. A distribution database and the actual distribution process are also needed.

Log Reader Process

When a database is marked for replication, the transaction log is flagged so the log reader process knows to replicate these changes. Once you have completed a transaction in a replicated database, the actual transaction log entries are not removed until the log reader process moves them to the distribution database.

This causes your transaction log to fill a bit faster than a nonreplicated database with the same number of transactions. Once the transactions are moved to the distribution database, the entries in the publishing database transaction log can be flushed through the normal checkpoint process in Microsoft SQL Server.

The log reader process does not run continuously; it is an interval-based

process that is added to your server’s scheduled task list. Keep in mind that the SQL Executive service must be running for replication to work properly.

Synchronization Process

Synchronization can be done manually or through the Enterprise Manager. To perform manual synchronization, you would create an exact copy of the schema on the subscriber database and load the table or tables with exactly the same data as the publisher. If you select manual synchronization, Microsoft SQL Server assumes that both publisher and subscriber are identical. If you miss any records or some structural element, the replication process will generate errors.

If you choose automatic synchronization, Microsoft SQL Server will generate scripts that will create the structures for you on your subscribers and a BCP task to load the existing data into the new structure once it has been

successfully created. Upon completion of the BCP into the new structure, the distribution database is notified that the new subscriber is ready for any new transactions to be applied. Remember that transactions are applied on the

http://www.itknowledge.com/reference/standard/1576101495/ch04/118-122.html (2 of 4) [1/27/2000 6:15:54 PM]

subscriber in the same order as they were applied on the publisher to maintain the integrity of the data.

Replication Distribution Process

As mentioned, replication distribution is best described as the traffic-cop process in replication. The distribution process keeps a list of the transactions that are to be replicated to subscribers, which subscribers have received the transactions, and whether the transactions were applied to each table

successfully. No transactions are purged from the distribution lists until all subscribers have successfully received and applied their changes.

In a multiple-server environment that merges two or three of the scenarios described in this chapter, the distribution of replicated data can get fairly difficult to manage. Take the time to test your configuration and make sure you have published your data only as much as needed to meet the requirements of replication.

Publish your data only one time. If your process requires that a particular piece of data be used in many different locations, publish it as an article one time and subscribe to it many times. Do not create a new publication of the same data each time it is used. If you fall into this trap, you will bring your system to its knees unnecessarily.

Do not forget to have a good backup strategy for the distribution server in your replication scenario. The transaction log and database should be backed up regularly and frequently. Should this server go down, your subscribers will not receive any transactions until you can bring it back online.

Communication Failures

Failures to communicate can cause problems in any environment. I have not found a system yet that does not require solid communications in order to function well—although Microsoft SQL Server comes close with regard to replication. If your link between publisher and subscriber fails for some

reason, Microsoft SQL Server will basically cache the transactions that need to be applied to each of the subscribers until they can be contacted and

successfully applied. Even the link between publisher and distribution server can be lost for a short period of time, and once reestablished, the publisher will still have the transactions in the transaction log for distribution. Using a

distribution server with processes whose only function is to keep servers synchronized is a very fault-tolerant method of distributing data.

Should your link between publisher and distribution server be lost, be careful of the transaction logs in any database marked for replication. Keep in mind that transactions in logs marked for replication are not purged until they are successfully written to the distribution server. If the transactions have nowhere to go, they will not be purged by the checkpoint process in Microsoft SQL Server.

Note: As a rule, always back up your transaction log more frequently than normal if it is part of a database participating in replication. Do not allow the

http://www.itknowledge.com/reference/standard/1576101495/ch04/118-122.html (3 of 4) [1/27/2000 6:15:54 PM]

log to get more than 75 percent full before making a backup.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch04/118-122.html (4 of 4) [1/27/2000 6:15:54 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

In document Microsoft SQL Server Black Book pdf (Page 109-112)