Earlier, most of the applications were using standalone environment where a single centralized server was responding to multiple users, working in different locations.
Centralized Approach and Problems
To overcome all the above problems, we can use replication solution.
Replication allows to maintain same database multiple copies at different locations. Log shipping and mirroring allows to maintain complete database redundancy whereas replication allows to maintain some part of the database (a set of required objects) at users location. Changes made at different user locations are synchronized to the main server. It is object level high availability feature. According to Books Online:
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.
Unlike other methods of high availability, it doesn’t distribute entire database, but only distributes some part of database like tables or views.
SQL Server 2005 Features
SQL Server 2008 Features
SQL Server 2012 Features
SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.
Log Reader Agent
Queue Reader Agent
1. Snapshot Replication
For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.
Snapshot Replication Architecture
How it Works?
Step 1: Configuring distributor and publisher
Step 2: Creating Snapshot Publication
FAQ: How to display database names which consists of publications?
Ans: Go to publisher -> take new query ->
name sys.databases is_published=1 is_subscribed=1
FAQ: How many articles may be there in a snapshot publication?
FAQ: Max columns in a table?