SQL Server Replication

MSDN Article here     SQL Server How to videos here

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.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

In addition to replication, you can synchronize databases by using Microsoft Sync Framework and Sync Services for ADO.NET. Sync Services for ADO.NET provides an intuitive and flexible API that you can use to build applications that target offline and collaboration scenarios. For complete documentation, see this MSDN Web site.

This is from SQL Server Training – great simple description of Replication

What is Replication ?

Replication is the mechanism for creating and maintaining multiple copies of the same data. Replication allows,
• Multiple copies of data can be kept in sync.
• Allows data be closer to users, improving performance.
• Allows a branch office to work when not connected to the main office.
• Separate process and offload work from production servers

What are the Components of Replication

Following are the important components in replication setup
• A publication contains articles.
• The publisher either distributes the magazine directly or uses a distributor.
• Subscribers receive publications to which they have subscribed.

What are the Server Roles Involved in Replication ?

SQL Servers can be assigned three different roles in a replication topology:
• Publishers create and modify data.
• Distributors store replication-specific data associated with one or more publishers.
• A subscriber holds a replica copy of the data.

What are the type of replication available in SQL Server ? SQL Server supports

1.Snapshot replication sends the entire data set to the subscriber.
2.Transactional replication only sends modifications to the data.
3.Merge replication items are modified at both the publisher and subscribers.
4.Heterogeneous replication allows access to other database products.
5.Peer to Peer Transactional Replication.

I wish to use Transactional

How to Setup Transactional Replication ? This video demonstrates

1.How to configure Transactional Replication between two instances of SQL Server 2008.
2.How to Create a Publisher database
3.How to Create a Subscriber database
4.How to Select replicable SQL Server database objects
5.How to Configure security for the replication