Replication provides for copying and distributing data and database objects from one SQL Server database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Types of Replication supported by SQL Server 2005

Microsoft SQL Server 2005 provides the following types of replication for use in distributed applications:

• Transactional replication: typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed. Typically used in a server to server environment. By default, Subscribers to transactional publications in SQL Server should be treated as read-only, because changes are not propagated back to the Publisher. However, SQL Server transactional replication does offer options that allow updates at the Subscriber.
• Merge replication: similar to transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred. SQL Server merge replication is typically used in server-to-client environments.
• Snapshot replication: distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire database snapshot is generated and sent to Subscribers. SQL Server snapshot replication is most appropriate when data changes are substantial but infrequent.

Selecting the appropriate type of replication The type of replication you choose for an application depends on many factors, including the physical replication environment, the type and quantity of data to be replicated, and whether the data is updated at the Subscriber. The physical environment includes the number and location of computers involved in replication and whether these computers are clients (workstations, laptops, or handheld devices) or servers.

SQL Server 2005 replication components and processes A SQL Server 2005 replication topology involves the following components and processes:

• Publisher: a SQL Server database instance that makes data available to other locations through replication. The Publisher database can have one or more publications, each defining a logically related set of objects and data to replicate.
• Distributor: a SQL Server database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. A single database server instance may often acts as both the Publisher and the Distributor in which case it is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.
• Subscribers: a SQL Server database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.
• Article: identifies a SQL Server database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.
• Publication: a collection of one or more articles from one SQL Server database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.
• Subscription: a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions in SQL Server: push and pull.
• Replication agents: SQL Server replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL Server Agent must be running for the jobs to run. Replication agents can also be run from the command line and by applications that use Replication Management Objects (RMO). Replication agents can be administered from SQL Server Replication Monitor and SQL Server Management Studio.

Click here to go back to the SQL Server content index page.