Table of Contents

Chapter 14

Replication

Certification Objectives *

Distributed Data Alternatives to Replication *

Selecting the Proper Tool to Manage Distributed Data *

Consistency *

Types of Replication *

From the Classroom *

Replication to the Desktop *

Snapshot Replication *

Transactional Replication *

Merge Replication *

From the Classroom *

Replication For Contingency *

Publisher *

Article *

Distributor *

Snapshot Agent *

Distribution Agent *

Log Reader Agent *

Merge Agent *

Pull subscription *

Push subscription *

Diagnosing and Resolving Replication Problems *

Replication Monitor *

Windows NT Application Event Log *

SQL Server Performance Monitor *

Certification Objectives

This chapter will discuss data replication and the business needs that it is designed to solve. The replication technology in Microsoft SQL Server allows the developer to store data in multiple locations and automatically move the data to new locations. It also can be used to move updates to all desired locations. Replication provides a fast and reliable way to propagate information to multiple databases on one or more servers in one or more locations. This movement of data allows distributed business organizations to place their data closer to employees in local, remote, and mobile offices. The replication technology in SQL Server manages the difficulty of synchronizing the data so that all databases contain the appropriate updated data. Managing the movement of data can be automated through other SQL Server functions. The problem is that this approach is more expensive and less flexible. It is important to recognize that data storage requirements (both structure and location) may change as frequently as an organization’s structure.

Throughout the chapter, we will refer to the ACID test or transactions and their individual parts. The ACID test refers to a transaction’s ability to pass the four following separate tests: Atomicity, Consistency, Isolation, and Durability. These are defined as follows:

Replication: What is it?

Replication technology allows SQL Server to reliably transfer data from a source database to one or more destination databases. This data can be replicated continuously or at scheduled intervals. The volume and type of data that is transferred may be managed at both the source and destination databases. The source server may make either the complete source database, or a selected subset available to the destination database. The destination database also has the option to accept any subset of the source database.

There are two types of filtering implemented in SQL Server replication: horizontal and vertical. Horizontal filtering is used to restrict which rows are replicated. The impact of horizontal filtering is shown in Table 14-1. The highlighted area represents the data that is replicated if we elect to only send information about customers in Illinois (three rows in our example). Vertical filtering is used to restrict which columns are replicated. The impact of vertical filtering is shown in Table 14-2. The highlighted area represents the data that is replicated if we elect to only send information about the city and state in which our customers live.

LastName FirstName City State
Asper Randy Bloomington IL
Leary Bill Bloomington IL
Priess Michael Normal IL
Senesac Andy Jacksonville FL
Sutton Kerry Jacksonville FL

Table 1: Horizontal Filtering

LastName FirstName City State
Asper Randy Bloomington IL
Leary Bill Bloomington IL
Priess Michael Normal IL
Senesac Andy Jacksonville FL
Sutton Kerry Jacksonville FL

Table 2: Vertical Filtering

Replication technology was introduced in version 6.0 of SQL Server. In the 6.x versions, Microsoft provided administrators with the ability to replicate read-only transaction-based data to one or more different databases. This functionality was not limited to interaction between Microsoft SQL Server databases. Using ODBC (Open Database Connectivity), the data could also be transferred to other database platforms. SQL Server versions before 7.0 did not provide the functionality necessary to allow SQL Server to receive replication data from other database platforms.

Distributed Data Alternatives to Replication

Though replication technology is one tool available for managing distributed data, it is not the only option. Microsoft Open Data Services (ODS) provides a server-based Application Programming Interface (API) for creating server-based applications. Developers use this functionality to write applications that distribute data to and from outside data sources. These applications are typically designed to function as a gateway between an application and other data sources. ODS is a tool that is used to access multiple data sources rather than coordinating the synchronized update of multiple servers. (see Figure 14-1.) You will find more specific information about ODS in the SQL Server Books Online.

Fig14-01.gif (8142 bytes)

Figure 1: Open Data Services. Note that the client retrieves data from multiple platforms using only a DB-Library call. This allows the flexibility to move data to different platforms without modifying the user application.

Another tool for managing distributed data is the two-phase commit protocol. Similar to replication, two-phase commit transactions manage the synchronized update of multiple servers in a distributed SQL Server environment. Two phase commit is used to commit updates to multiple databases simultaneously rather than committing to one and then forwarding to other databases. As the name implies, there are two steps to a two-phase commit transaction. These phases are managed by SQL Servers playing two roles: Transaction Manager and Resource Manager. For each transaction, one server will function as the Transaction Manager that controls the transaction. Each server that has a database participating in the transaction functions as a Resource Manager.

In the first phase, the Transaction Manager verifies that all Resource Managers are available and willing to apply the update. In the second phase, the Transaction Manager instructs all Resource Managers to commit the transaction. If the Transaction Manager receives a negative response from any server during phase 1 or phase 2, the transaction is rolled back. (see figure 14-2.)

Fig14-02.gif (6566 bytes)

Figure 2: Two-Phase Commit. In Phase 1, the Resource Managers are instructed to prepare the transaction. In Phase 2, the transaction is committed.

Selecting the Proper Tool to Manage Distributed Data

When determining the appropriate solution for managing data needed in more than one database, administrators must evaluate the following options: ODS, Two-phase Commit, and Replication. The decision on which functionality to use is driven by the organization’s business requirements. In particular, the need for synchronized edits, consistency, and availability will determine the appropriate solution.

There are many situations when you might work with either two-phase commit or replication. One situation would be if your business requirements dictate storing identical data in multiple SQL Server databases and performing synchronized updates. To determine which solution to use, we must look closer at the issues of consistency and availability.

Consistency

Consistency is used to describe and measure the possibility for differences to exist between two databases that share data in a distributed environment. Transactional consistency (with respect to Replication) and Two-phase commit means the data at participating sites is identical to results that would be seen if all transactions were completed at a single site. Transactional consistency verifies that the techniques used to move data from the source database to destination databases do not alter the data. When working with distributed applications that modify data, there are three basic levels of transactional consistency. These are covered in the following sections.

Immediate guaranteed consistency

Immediate guaranteed consistence describes an environment where all databases in the replication will always have the same data at the same time. In other words, the data in all participating databases is the exact same as what would have resulted had all work been performed on a single database. There is only way to achieve immediate guaranteed consistency across multiple databases. This consistency is accomplished with the use of a two-phase commit between all participating sites. All databases must simultaneously commit every change, or no site can commit the change. Such a solution is often not feasible for mission critical applications with a large number of distributed sites. Transactions would not be able to complete when unforeseen conditions (such as a network outage) occur.

Latent guaranteed consistency

Latent guaranteed consistency describes an environment where all databases participating in the replication are guaranteed to have the exact same data at some point in time. With latent guaranteed consistency, the source database is updated immediately, and the changes are passed to the destination databases after some period of delay. The data in all participating databases must still be the exact same that would have resulted had all work been performed on a single database. Unlike immediate guaranteed latency, not all databases are assured of having the exact same data at the exact same time. At any point in time that all updates from the source database have been received and committed in the destination database, the databases would contain the same data. Latent guaranteed consistency across multiple SQL Server databases is obtained using replication.

The primary difference between immediate and latent guaranteed consistency is timing. When all updates to the source database have been propagated to all destination databases, latent guaranteed consistency produces the same results as immediate guaranteed consistency.

Exam Watch. The primary difference between immediate and latent guaranteed consistency is timing. Despite that, time is not the only significant difference between two-phase commit and replication. It is important to understand that two-phase commit transactions must be coded as such and are therefore a design time decision. Replication services are a server-based solution. They can be added at any time without changing the application or its parameters. Remembering this difference will help you to eliminate multiple options on exam questions.

Convergence

The third level of transactional consistency is convergence. With convergence, all sites may end up with the same values. If all the work had been done at only one site, then these values are not necessarily the ones that would have resulted. All sites are allowed to function in a disconnected manner. All sites converge to the same values as all of the nodes synchronize.

True relational databases support atomic edits. In atomic edits, changes may be grouped together as a transaction. By creating a transaction, certain things occur. We guarantee the edits will either be committed in their entirety, or the database will be rolled back to it’s previous state prior to the transaction.

When working with a distributed database environment, the concept of atomic edits are extended to the replication process. This concept is know as anatomic transactions. If a distributed database environment supports atomic transactions, we are assured of guaranteed transactional consistency. In other words, a transaction that completes on one database will be applied to others and produce similar results. The concept of atomic edits extends to latent and immediate guaranteed consistency, but does not apply to convergence. One of the requirements to latent and immediate guaranteed consistency is as follows: the results are identical to those that would result from all work being completed on a single database. With convergence, this is not the case. Lotus Notes is an example of a convergence product. Though it can be used to build powerful distributed applications, it does not provide atomic transactions or any other model of guaranteed consistency.

Many database vendors promote the fact that the replication solutions in their product allows for updates to be done anywhere on any of the replication partners. These vendors typically offer the ability to resolve conflicts. They do this by discarding or changing the effects of one transaction when it conflicts with another transaction. While these solutions are marketed as providing complete site autonomy, it is often not explained that this conflict resolution eliminates guaranteed transactional consistency.

Replication uses a loose consistency distributed data model. Conversely, the two-phase commit uses a tight consistency model. While this offers some clear advantages, it is important to understand the compromise that is made in a system’s availability. When using two-phase commit, the transaction is rolled back if any server that is involved in the transaction is unavailable. The result is that your database is only available for update when all servers (and their network connections) are available. With replication, the database is available for update if the Publishing Server is available. In looking at the following chart in Table 14-3, you can see how we use the previous information to find the correct technology to meet our business needs.

Business Requirements

Open Data Services

Two-phase commit

Replication

Access to other Database Platforms

Yes

Yes

Yes

Synchronize data between multiple databases

No

Yes

Yes

Guarantee identical results no matter where a query is run

NA

Yes

No

Allow updates to be processed while one server is down

NA

No

Yes

Table 3: Comparing Distributed Data Functionality

Types of Replication

In implementing Replication, Microsoft has chosen to follow the publish and subscribe metaphor. The source database that provides data for other databases is called the Publisher. The data items that a Publisher makes available to other databases is called an Article. The database that accepts data from the Publisher is called a Subscriber. A Distributor stores the articles from the Publisher and makes them available to the subscriber. Each of these elements are discussed in more detail later in the chapter.

As we look at the various types of replication, we will discuss the decisions that must be made. These decisions are based upon the balance of the size of the article versus the frequency of changes.

On the Job. When working with snapshot replication, it is often advisable to restart the subscription databases in read-only mode after the snapshot has been applied. You will see significant performance improvements of up to 20 percent, because SQL Server does not need to manage locks.

From the Classroom

Replication to the Desktop

SQL 7.0 gives you the option of replicating data from your Enterprise SQL Server to the desktop with SQL 7.0 Desktop Edition. Desktop replication is ideal for mobile sales people who may connect only once per day and even then over a slow dial-up network connection.

Many automation applications for sales were written before SQL 7.0 was released. SQL Server now provides what previously had to be written into the application by the developer. This new feature of desktop replication will provide a reduced time-to-completion for applications that use it.

—David Smith, MCSE + Internet

Snapshot Replication

As the name implies, snapshot replication takes a picture of the published database at a point in time and makes it available for distribution. Snapshot replication provides an update to the publishing server. When this time comes, it does not pass the inserts, updates, and deletes that have occurred since the last replication. Instead it provides another complete copy of the data. Snapshot replication is the simplest form of replication.

Snapshot replication is essentially an automated process for performing periodic backups and restores. It also has the enhanced ability to backup a subset of a database by creating an article that has been filtered. This replication technique places the lowest demand on the source computer’s processor resources. This is because it is not continually monitoring the transaction logs to track changes to the data.

Snapshot replication provides guaranteed consistency. In addition, it also guarantees latency between the Publisher and all Subscribers. Snapshot replication is commonly used to distribute data to Subscribers that meet the following parameters: they need a database for read only access and do not have a strong need for the latest updates. Two common scenarios for this include website databases and decision support systems. When using snapshot replication, nothing requires the Subscriber be attached to the Publisher between snapshots. This makes snapshot replication a good option for many remote users who are taking advantage of new workstation versions of SQL Server.

Exam Watch. SQL Server does not implement any technology to stop users from making updates to a destination database. This must be handled in the application. All changes made directly to a subscribing database are overwritten when the next snapshot is applied.

It is important to weigh the performance and resource tradeoffs when determining if snapshot replication is the best answer,. In deciding if snapshot replication is appropriate, you must balance the size of the article against the volatility of the data. If the article is very large, then snapshot replication will require substantial network resources to transmit. This volume of traffic can be managed by scheduling replication to occur during non-peak network times. Your source database might contain relatively small records that have very frequent changes (such as a database that tracks stock market prices). If this is the case, you may find that passing transactions generates more traffic than passing the complete article.

Snapshot replication is performed by the Snapshot Agent and the Distribution Agent. The Snapshot Agent creates snapshot files that consist of the database schema and the data from the published tables. This data is stored in the distribution database on the Distributor. The Distribution Agent moves the snapshot jobs held in the distribution database tables to the destination tables at the Subscribers. These agents are discussed in more detail later in the chapter.

Transactional Replication

Transactional replication uses the transaction log to capture changes that were made to an article’s data. When using transactional replication, SQL Server monitors INSERT, UPDATE, and DELETE statements. With transactional replication, changes made to the source database are typically applied to destination databases with limited latency. The changes made to article data are stored in the distribution database and forwarded to each subscriber where they are applied in the same order.

This approach results in frequent, relatively small updates. This continuous monitoring of the source databases and frequent distribution of data changes has certain outcome. One is that it creates a greater load on the source server’s processor than snapshot replication does. The network typically transmits smaller amounts of data at any given time. The overall burden placed on the network is driven by the size and frequency of the data being updated. Changes made to the Publisher flow either continuously or at scheduled intervals to one or more subscribing servers. Changes are typically propagated from the source database to destination databases in near real time.

Like snapshot replication, transactional replication requires that all changes be made at the publishing site. This avoids conflicts, and guarantees transactional consistency. Committed transactions are sent to subscribing servers in the guaranteed order in which they were committed at the publisher. This guarantees loose transactional consistency: Ultimately all subscribing sites will contain the same data values as those at the publisher. The data at all sites is identical to the results that would be achieved if all operations had been performed at a single site.

The latency between the publisher and any given subscriber is impacted greatly by the network connection between the two. Subscribers that require near real-time propagation of data changes will need a network connection to the publisher with available bandwidth. In an environment with a well-designed network, it is transactional replication that can provide very low latency to subscribers. Push subscribers can often receive changes within 15 seconds of when they occurred at the publisher. When designing an application with these requirements, it is very important to create a disaster recovery plan that allows for network failures. It may be necessary to configure a Remote Access Services (RAS) connection between the two servers that can be activated if a Wide Area Network (WAN) link fails.

Transactional replication relies on a given data element having only a single Publisher. If direct updates are made to replicated rows in the subscribing database, then transactional replication will not perform any cleanup. Data integrity will also be lost. The most straightforward form of a transactional replication environment has a single publisher database and any number of subscribers. Environments that are more complex will have multiple publishers that each have responsibility for a subset of the records in a replicated table. This configuration is most commonly used in scenarios that allow for logical partitioning of data and data ownership. We will discuss this in more detail later in the chapter.

Merge Replication

As discussed earlier, snapshot and transactional replication require all changes to the data be made at the publishing site. This eliminates all update conflicts, and transactional consistency is guaranteed. All subscribing sites will have the same values as the Publisher where the updates were made.

In production environments, the business requirements will often require that updates be allowed in multiple locations. In prior versions of SQL Server, this functionality required custom application code. This is because the replication services did not support multi-site updates of the same data. In SQL Server 7.0, this functionality is provided through merge replication. Merge replication provides the highest level of site autonomy of any replication solution. Publishers and Subscribers can work independent of each other and merge their results later when they reconnect.

The Snapshot Agent and Merge Agent carry out merge replication. The Snapshot Agent creates snapshot files that contain the database schema and the data in published tables. These files and record synchronization jobs are stored on the Distributor in the publication database. The Merge Agent applies the initial snapshot jobs to the Subscribers.

In transactional replication we talked about how we tracked all changes to a source database and synchronized the values from the Publisher to the Subscribers. Merge replication is unique as the data can be updated in both the Publisher and Subscriber. In merge replication, the Publisher is the server that created the publication. A conflict is created when users modify the same data in multiple locations. The winner of the conflict is determined by the Merge Agent. The Merge agent uses either rules, or a custom resolver created by the DBA to reconcile conflicts. Conflicts that are created by updates being made to the same data element at multiple sites are resolved automatically. These characteristics make merge replication an ideal solution for certain applications. One example would be sales force automation, where users need full access to local copies of the data in a disconnected environment.

The winner of the conflict can be resolved based on the following: priorities assigned to different sites, the timestamps on the changes, or a combination of the two. Data values are only replicated from the Distributors and applied to both Publishers and Subscribers when the reconciliation processes occurs. This process may be hours, days, or even weeks apart. Conflicts can be detected and resolved at the row level, or even at a specific column in a row.

When a table is published using merge replication, SQL Server makes three important changes to the schema of the database. These are listed as follows.

Under priority-based conflict resolution, every publication is assigned a priority number, zero being the lowest and 100 the highest. This is consistent with the priority system used by NT Spooler. The illustration in Figure 14-3 represents the simplest situation. In this scenario, all three sites agree that Site A created version one of the row, and no subsequent updates occurred. If Sites A and B both update the row, then Site A’s update is the conflict winner, because it has the higher priority.

Exam Watch. It is important to understand that merge replication is the only type of replication that will generate conflicts and thus require conflict resolution. There are no priority issues for snapshot or transactional replication. Merge replication is the greatest replication change in SQL Server 7.0. You will most likely see multiple questions on this topic in the exam.

In a more complex scenario where multiple changes have occurred to the same row since the last merge, certain parameters come into play. The maximum site priorities of changes made since the common version is used to determine the conflict winner. Using Figure 14-3 again, suppose that Site A makes version two, sends it to Site B, which makes version three, which then sends it back to Site A. Then Site C has also made a version two and reconciles with A. By choosing the maximum priority of changes that occurred since the common version, (Site A’s priority of 100), Site A's and B’s joint changes are the priority winner, so Site A is the conflict winner. This should illustrate how quickly merge replication can become complicated, and possibly return unexpected results. Let’s step through the process below to make sure it is clear:

  1. Site A makes version 2 – priority 100
  2. Site B makes version 3 – priority 90
  3. Site B merges with Site A – priority 100
  4. Site C makes a version 2 – priority 95
  5. Site C merges with Site A – Site A wins & version 3 is accepted and replicated.

Fig14-03.gif (4619 bytes)

Figure 3: Priority-based Merge Replication

Merge replication is designed to handle the needs that applications have for flexible conflict resolution schemes. An application can override the default, priority-based resolution by providing its own custom resolver. Custom resolvers are COM (Common Object Model) objects or stored procedures, written to the public resolver interface, and designed to enforce business rules. The custom resolvers are invoked by the Merge Agent during reconciliation.

For example, suppose multiple sites participate in monitoring a data center. Their function is to record the low and high temperatures experienced each day. A priority-based or first-wins strategy would not deliver the lowest low and highest high value. Design templates and code samples make it simple to create a custom resolver to solve this business case.

Merge replication is a great solution for distributed applications that require very high site autonomy, and can either be partitioned or do not require transactional consistency. Merge replication is not the right choice if transactional consistency is required and the application cannot guarantee integrity using partitioning. Transactional consistency refers to the strict adherence to the ACID (Atomicity, Consistency, Isolation, Durability) properties, and specifically the D for durable transactions. Any replication solution that allows conflicts can not achieve the ACID properties. Any time conflicts are resolved, some transaction that had been committed gets "undone". This idea therefore breaks the rule for durability.

Since version SQL Server 6.0 was released, we have had the ability to replicate data from SQL Server to other heterogeneous databases. SQL Server 7.0 supports replication to heterogeneous data sources that provide 32-bit ODBC or OLE DB drivers for Windows. Out of the box, SQL Server supports Microsoft Access, Pocket Access, Oracle, and DB2 as heterogeneous subscribers. In addition, SQL Server 7.0 supports any other database server that complies with ODBC or OLE DB Subscriber requirements.

The cleanest way to publish data to a Subscriber that is not running SQL Server is through the following action. Use ODBC/OLE DB and create a push subscription from the Microsoft SQL Server Publisher to the ODBC/OLE DB Subscriber. The alternative is to create a publication and then create an application with an embedded distribution control. The embedded control implements the pull subscription from the Subscriber to the Publisher.

It is important to note that for ODBC/OLE DB Subscribers, the subscribing database has no administrative capabilities regarding the replication being performed.

For the first time, in version 7.0, Microsoft enables heterogeneous data sources to become Publishers within the SQL Server replication framework. Microsoft has exposed and published the replication interfaces, enabling a developer to use all the transaction replication features of SQL Server.

The replication services available to heterogeneous data sources include:

Microsoft has worked with third party vendors on the Distributor Agent interfaces, to ensure that ISVs —including Platinum and Praxis—are able to tightly integrate the heterogeneous replication solutions that they are building to the distributor interface, with SQL Server 7.0. This will allow database administrators to move publications from third-party databases directly into the Microsoft Replication Framework.. After a heterogeneous publication has been put into the distribution process, it can be monitored directly from third-party tools that support SQL-DMO replication objects.

Immediate Updating Subscribers Option

In their simplest form, both snapshot and transactional replication are based on a model of one-way replication. In this model, data is modified at the Publisher and distributed to a Subscriber. As we discussed earlier, some business requirements will dictate the need to update data at subscribing servers and have those changes flow upstream. To handle these requirements, SQL Server makes the Immediate Updating Subscribers option available with either snapshot or transactional replications.

This option is set when the article is created. When this option is set, a Subscriber can update the copy of its local data. It can update the data as long as that update can be immediately copied to the Publisher. If the Publisher is not available to make the update, the Subscriber rolls the transaction back. This process uses the two-phase commit protocol that we discussed earlier in this chapter. If the update can be performed successfully between the Subscriber and the Publisher, the Publisher will propagate the changes to all other Subscribers in the next distribution. The user can continue working with the updated data secure in the guarantee that the Publisher data also reflects the change. This is because the Subscriber making the update already has the data changes reflected locally.

The Immediate Updating Subscribers option is appropriate for use with specific subscribers. These are subscribers that are connected via a reliable network connection where contention for data is relatively low. For example, let’s look at an airline ticketing system. This system maintains local replicas of the ticketing database so that available seating can be quickly accessed. Because a seat must only be sold once (in theory), it is critical that the local ticket sale is immediately updated and committed to the central server (the Publisher). The transaction is then replicated to all other local ticket offices in the next distribution. Unlike merge replication, if the network connection between the subscriber and the publisher is broken, the subscriber can not act autonomously and make the ticket sale.

When a publication is enabled to support the Immediate Updating Subscribers option, a subscriber site can modify replicated data if the transaction can be performed using two-phase commit (two-phase commit) with the publisher. This approach provides immediate guaranteed consistency between the subscriber. It also provides latent guaranteed consistency to other Subscribers as they maintain a traditional replication relationship with the Publisher. Because the two-phase commit transaction back to the publisher is driven by the replication configuration, applications can be written as though they are updating just one site. This approach does not have the availability constraint of a pure two-phase commit application design. In this case, only the publisher needs to be available. (Q14) In Figure 14-4, you will see that the two-phase commit protocol communicates between a single subscriber and the publisher.

Fig14-04.gif (10206 bytes)

Figure 4: Immediate Updating Subscribers

When working with earlier versions of SQL Server, application developers would write their applications to perform read transactions locally and write transactions on the Publishing server. These applications required an extraordinary amount of error handling and negotiation logic. For example, two subscriber sites open the same record from their local database and attempt to write updates at the Publishing sites. If this is the case, only the first one can be allowed to write, otherwise the second write will overlay changes made in the first.

Reasons for Replication

Companies use replication to solve a number of technical problems. These technical problems typically fall into one of the following categories:

From the Classroom

Replication For Contingency

There are many mission critical database applications where down-time can mean lost money or even lost lives. Traditional backup of your database to tape will not restore your database as quickly as many business situations require. Replication of your data to another SQL Server in another Data Center can provide accessibility in an emergency.

—David Smith, MCSE + Internet

Elements of Replication

We have spent a fair amount of time talking about the types of replication that are available and the strengths and weaknesses of each. Now we will take a look at the individual elements that make up the replication process. This will include providing a greater definition of some terms we have used before, as well as introducing the agents that work behind the scenes and make the replication process(es) work.

Publisher

The publisher is the server that makes data available for replication to other servers. The publisher is responsible for which data is to be replicated, as well as which data has changed. The publisher also maintains information about all publications at that site. It is important to understand that any data element that is replicated has a single Publisher. Data that may be updated by any number of Subscribers or can be published again by a Subscriber still has a single Publisher.

Subscribers

Subscribers are servers that store data replicated from publishers. In earlier versions of SQL Server, updates could only be performed at the Publisher. SQL Server 7.0, however, allows Subscribers to make updates to data. It is important to remember, however, that a Subscriber making updates is not the same as a Publisher. A Subscriber can, in turn, also become a Publisher to other Subscribers.

Article

An article is a grouping of data to be replicated. An article may be an entire table, a subset of the columns (using a vertical filter), or a subset of the rows (using a horizontal filter). An article may even be a stored procedure (in some types of replication). A collection of articles is called a publication.

Distributor

The distributor is the server that contains the distribution database. The core responsibility of the distributor is to take publications from the publisher and distribute them to the subscribers. The exact role of the Distributor is different in each type of SQL Server replication.

Snapshot Agent

The Snapshot Agent performs its role in steps. It prepares the schema and initial data files of published tables and stored procedures. It then stores these snapshots on the Distributor, and records information about the synchronization status in the distribution database. Each publication has its own Snapshot Agent that runs on the Distributor and connects to the Publisher.

Each time the Agent runs, it creates schema and data files to be sent to Subscribers. The agent does this in several steps:

  1. The agent creates a connection from the Distributor to the Publisher. It establishes a share-lock (read only) on all tables included in the publication. The share-lock prevents any updates to the data during the snapshot, thus ensuring a totally consistent snapshot of data.
  2. The agent creates a connection from the Publisher to the Distributor and writes a copy of the table schema for each article to a file on the Distributor. The file, which has a .sch extension, is stored in a subfolder in the working folder of the distribution database. If the article specifies that indexes be included, they are created as a part of this schema.
  3. The agent creates a connection from the Distributor back to the Publisher and starts writing history entries and errors to the distribution database.
  4. The agent takes a "snapshot" of the data in the published table on the Publisher and writes the data to a file on the Distributor. The file is stored in a subfolder in the working folder of the distribution database. If the Subscriber uses native SQL Server tables, the snapshot is stored as a native bulk copy program (.bcp) file. If the Subscriber is a heterogeneous (non-SQL Server) data source, the snapshot is stored as a character mode (.txt) file. The .sch and .bcp files are the synchronization set that represent the table. There is a separate synchronization set for each article within a publication.
  5. The agent inserts data into the MSrepl_commands and MSrepl_transactions tables of the distribution database. The MSrepl_commands table stores commands indicating the location of the synchronization set (.sch and .bcp files) and ordered references to any specified pre-creation scripts. The MSrepl_transactions table stores commands referencing the Subscriber’s synchronization task.
  6. Finally, the agent releases its share-locks on each published table and completes writing the log history file.

Distribution Agent

The distribution agent moves the transactions and snapshot jobs from the distribution database tables to the Subscribers. Transactional and snapshot publications have their own Distribution Agent that runs on the Distributor and connects to the Subscriber. These publications are set up for immediate synchronization when a new subscription is created Transactional and snapshot publications that are not set up for immediate synchronization share a Distribution Agent across the Publisher/Subscriber pair. That distribution agent also runs on the Distributor and connects to the Subscriber.

Pull subscriptions to either snapshot or transactional publications have Distribution Agents that run on the Subscriber instead of the Distributor. The Distribution Agent typically runs under SQL Server Agent and can be directly administered by using SQL Server Enterprise Manager.

Each time the Distribution Agent runs for a publication, it moves the schema and data to Subscribers. The agent does this in several steps:

  1. The agent creates a connection from the server where the agent is located to the Distributor. For push subscriptions, the Distribution Agent is located on the Distributor. For pull subscriptions, the Distribution Agent is located on the Subscriber.
  2. The agent examines the MSrepl_commands and MSrepl_transactions tables in the distribution database. The agent reads the location of the synchronization set from the first table and the Subscriber’s synchronization commands from the second table.
  3. The agent moves the snapshot of the schema and data to the Subscriber. The image of the entire publication flows to the destination database where it is re-created as an exact duplicate.
  4. After the snapshot files arrive at the Subscriber, the distribution agent takes out a table lock on the Subscriber. The table lock prevents any exclusive locks from being taken out on the affected tables while the snapshot is applied at the Subscriber. This lock can be held for an extensive period of time. Therefore it is important to schedule these synchronization tasks to execute during non-peak production database use.
  5. The agent applies the commands to the subscription database. If the Subscriber is not a SQL Server database, the agent converts the commands into native syntax as necessary. All articles of a publication are synchronized simultaneously, preserving transactional and referential integrity across tables.

On the Job: When handling more than five Subscribers, running the Distribution Agent at the Subscriber (that is, as a pull subscription) will greatly reduce the demand on processing resources at the Distributor. This will improve the overall performance of your replication process.

Snapshots can be applied either when the subscription is created or at a schedule time that is set at the time the publication is created. When the scheduled time arrives, all Subscribers that have not been synchronized since the last scheduled synchronization event occurred get synchronized.

Log Reader Agent

The Log Reader Agent moves transactions that are marked for replication from the transaction log on the Publisher to the distribution database. Each database that is published using transactional replication uses its own Log Reader Agent. The Log Reader Agent runs on the Distributor and connects to the Publisher.

Merge Agent

The Merge Agent moves and reconciles incremental data changes that occurred after the initial snapshot was created. With merge replication, data may move in both directions: from the Subscriber to the Publisher, and from the Publisher to the Subscriber. Each merge publication has its own Merge Agent. The Merge Agent connects to both the Publisher and the Subscriber - potentially updating both. Push subscriptions to merge publications have Merge Agents that run on the Publisher. Pull subscriptions to merge publications have Merge Agents that run on the Subscriber. As you probably guessed, snapshot and transactional publications do not have Merge Agents.

Pull subscription

A pull subscription is one where the Subscriber asks for periodic updates of all changes at the Publisher. Pull subscriptions are best for publications having a large number of Subscribers, as they distribute the demand for server resources. Pull subscriptions are also best for autonomous mobile users as it allows them to control when the data changes are synchronized. Push and pull subscriptions can be used together on a single publication.

Push subscription

A push subscription is one in which the Publisher propagates the changes to the subscriber without a specific request from the Subscriber. Push subscriptions are used to propagate changes immediately as they occur, or as scheduled. The schedule is set by the publisher

Replication Uses

The replication technology implemented in Microsoft SQL Server is flexible enough to meet a wide variety of customer needs. Most implementations of database replication are targeted at meeting one or more of the following requirements.

Replication Examples

Now you should have a solid understanding of replication services offered in SQL Server. Next, we are going to give you a chance to apply it to the business requirements for a sample system and develop a replication strategy.

Exercise 14-1: Choosing the appropriate replication scenario for a given business need

Your customer is a property and casualty insurance company. They have decided to build a portable networked claim system for handling large catastrophes. They will place one database server and 100 workstations in each office. Each office will be responsible for handling claims within a geographic area. In addition to the individual claim offices, they will open a central call center location. In the central location, the call representatives need read-only access to data from all claim offices. In addition, managers will need to be able to generate reports for the total catastrophe (using data from all offices).

Because this system will be used in catastrophic situations, it is not reasonable to assume that phone lines will be stable. All offices must be able to function if communication lines are down. The claims personnel tell us that on an average day, less than five percent of the claims are modified.

You have been asked to architect the system. They want to know where the database should be located, and how data will be moved as necessary. Here are some questions you should consider, and their answers:

  1. Is it necessary to have more than one database? YES. One of the requirements is that offices are able to function if communication lines are down.
  2. Is replication needed? Is the same data needed in multiple locations? YES. The data is needed in both the local claim office and in the central location.
  3. Where is the data being edited (i.e. where is the publisher?) The data is being edited in the local claim office.
  4. Do we need to edit it in more than one location (i.e. is merge replication needed)? NO. Data will only be modified in the claim office. The central office will have read-only access.
  5. How frequently do we need to replicate? Call representatives can provide better information if replication is done frequently.
  6. Which location should initiate the replication (Push or Pull)? Because we cannot count on the communication lines being available, we will use Pull Replication with the central site performing periodic requests when the communication lines are up.
  7. Do we have a large percentage of our data being updated during each replication cycle (Snapshot Replication or Transactional Replication)? NO. A small percentage of records are changing, so we should use transactional replication.
  8. What is our solution? The solution is diagrammed and illustrated in Figure 14-5 and summarized as follows:

Fig14-05.gif (4297 bytes)

Figure 5: Solution for Exercise 1

SQL Server Replication Process

The SQL Server Replication process consists of multiple sequential steps that allow the elements to interact and provide a reliable method to manage and automate a distributed data environment. The steps are outlined below:

  1. The Snapshot Agent creates the schema and data files to be sent to the Subscribers.
  2. The Distribution Agent moves the schema and data files to Subscribers.
  3. If you are using Transactional Replication, the Log Reader Agent runs against the publisher’s transaction log (either continuously or at a scheduled interval). If you are using Snapshot Replication, the Snapshot agent will run again at the scheduled interval.
  4. The Distribution Agent transfers these updates to Subscribers.

When to Use Replication

Replication is the appropriate solution to meet business requirements that comply with the following:

  1. A dataset must be moved from one database to one or more other databases on a periodic basis. If you only need to move the data one time, a backup and restore may be more appropriate.
  2. It is acceptable for data in the subscribing databases to not receive all updates in real-time. If all databases must receive updates simultaneously, two-phase commit is likely to be a better solution.

Diagnosing and Resolving Replication Problems

One of SQL Server’s greatest strengths has always been the ease of management and troubleshooting that is made available to the Database Administrator. Replication is no exception to this rule. With SQL Server 7.0, Microsoft has included standard tools to make troubleshooting easier.

Replication Monitor

After setting up your replication environment with the Replication Navigator, the Replication Monitor can be used for viewing the status of replication agents and troubleshooting potential problems at the Distributor. The Replication Monitor is activated as a component of a server in SQL Server Enterprise Manager when the server is enabled as a Distributor and the current user is a member of the sysadmin fixed server role. All replication agents must be scheduled through SQL Server Agent. It is important to note that replication and the Replication Monitor will not work unless the SQL Server Agent is running.

You can use Replication Monitor in SQL Server Enterprise Manager for the following functions:

Windows NT Application Event Log

Microsoft SQL Server writes status information to the Application Event Log. To view the Windows NT application event log, use the Windows NT Event Viewer. The event log contains SQL Server error messages as well as messages for all activities on the computer. The event log can be viewed locally or remotely. When you use the Windows NT event log, you can filter the log for specific events. This is because each SQL Server session writes new events to an existing event log. Unlike the SQL Server error log, a new event log is not created each time you start SQL Server. You can specify how long logged events will be retained for the entire application log.

SQL Server Performance Monitor

The SQL Server Performance Monitor is a graphical tool provided with Windows NT for measuring the performance of your own computer or other computers on a network. You can view the behavior of objects, such as processors, memory, cache, threads, and processes. It provides charting, alerting, and reporting capabilities that reflect both current activity and ongoing logging. You can open, browse, and chart log files later as if they reflected current activity.

SQL Server provides its own objects and performance counters that can be viewed using Performance Monitor. When SQL Server is installed, a SQL Server Performance Monitor icon is installed in the Microsoft SQL Server program group. SQL Server Performance Monitor makes it possible to get up-to-the-second activity and performance statistics about SQL Server. Using this graphical tool, you can:

SQL Performance Monitor is a very effective way of monitoring replication activity. SQL Performance Monitor can be used to determine if the resource load is becoming excessive for the given hardware platform or configuration.

Certification Summary

In this chapter we introduced you to replication technology and the manner in which it has been implemented within Microsoft SQL Server 7.0. Replication provides you with the capability to automatically move data from one database to another. In SQL Server 7.0, you can now replicated data to and from other ODBC compliant database platforms. You can replicate an entire table or subsets of the rows and/or columns in a table.

There are three core types of replication offered within SQL Server: snapshot, transactional, and merge. With snapshot replication, the entire publication is sent to subscribers every time replication occurs. With transactional replication, only rows of data that have changed are replicated to subscribers. Because of the reduced amount of data being replicated, transactional replication is typically configured to occur with much greater frequency than snapshot replication. Merge replication is new in SQL Server 7.0. Merge replication provides the ability to make changes to a publication in multiple locations. It is necessary to implement conflict resolution in merge replication. This is because merge replication combines the ability to edit data in multiple locations and the site autonomy to make changes without input from any other servers. By default, SQL Server uses priority based conflict resolution. In addition, custom resolution can also be implemented.

In version 7.0, Microsoft has also added a second method to support edits in more than one location. Through the use of Immediate Updating Subscribers, Microsoft has taken advantage of their two-phase commit client functionality. This has provided a server-side implementation that allows updates in multiple locations if the subscriber can make a connection

Two-Minute Drill