Microsoft SQL Server Replication Overview
Replication is a set of technologies for copying and distributing data and database objects from one 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. This SQL Server Replication Overview will explain the terms and functions available through Microsoft SQL Server.
Usually replication is done to ensure redundancy. For instance, if a business runs a mission critical database, it is always wise to have a replication of this database on a separate physical database server, that can take over in case of failure in the primary database. Replication is useful in Data warehousing & reporting, improving scalability and availability, delegating batch processing tasks etc. When it comes to SQL Replication, Publisher, Distributor and Subscriber are the terms associated with it. The Publisher is the part that offers the data for distribution. The Distributor is the intermediary in the act of publishing and subscribing. The Subscriber is the part that consumes (gets updates) the data from the Publisher.
The three main replication models are Snapshot Replication, Transactional Replication and Merge Replication:
Snapshot Replication takes a snapshot of a publication and makes it available to subscribers. It is a one-shot deal; there is no continuous stream of data from the publisher to the subscriber. The data on the publisher at the time the snapshot is taken is applied to the subscriber.
Transactional Replication is generally used with databases where data changes frequently and there’s need for constant refreshing of the data. Transactional replication allows for faster data synchronization with less latency. It is useful in cases where you need incremental changes to happen at the subscriber quickly.
Merge Replication is usually used when there is no constant network connectivity among publishers and subscribers. It enables sites to work autonomously and merge the changes to the data when the connectivity is established.
Replication capabilities:
- SQL Express Edition has extremely limited replication capabilities. It can act only as a replication client.
- SQL Workgroup Edition has limited publishing capabilities. It is able to serve five clients using transactional replication and up to 25 clients using merge replication. It can also act as a replication client.
- SQL Standard Edition has full and unlimited replication capabilities with other SQL Server databases.
- SQL Enterprise Edition adds a powerful tool for those operating in mixed database environments. It is capable of replication with Oracle databases.
| Feature / Capabilities | Express Edition | Workgroup Edition | Standard Edition | Enterprise Edition |
| Snapshot Replication | Subscriber Only | Subscriber Only | All Capabilities | All Capabilities |
| Transactional Replication | Subscriber Only | Subscriber Only | All Capabilities | All Capabilities |
| Merge Replication | Subscriber Only | Subscriber Only | All Capabilities | All Capabilities |
SQL Server 2008 R2 Express Edition replaces the Microsoft Data Engine (MSDE) as the free version of SQL Server for application development and lightweight use. It retains the limitations of MSDE with respect to client connections and performance. A good tool for developing and testing applications and extremely small implementations.
SQL Server 2008 R2 Workgroup offers an impressive array of functionality. Workgroup edition can handle 2 CPUs with 3GB of RAM and allows for most of the functionality you’d expect from a server-based relational database.
SQL Server 2008 R2 Standard remains the staple of the product line for serious database applications. It can handle up to 4 CPUs with an unlimited amount of RAM.
SQL Server 2008 R2 Enterprise allows unlimited scalability and partitioning. In addition, it supports 8 CPUs.
(Source: softwarenewsdaily.com)
