SQL server replication tools

1
Modern businesses generate massive amounts of
data with the IT departments expected to keep
data online and accessible round the clock. This
puts intense pressure on the systems to maintain,
store, and manage the data. There is also the
additional activity of replacing outdated data
with new and more agile techniques of which SQL
Server Replication is the most suitable to
accommodate these demands.

• Overview of SQL Server Replication Components

2
What is SQL Server Replication

• SQL Server Replication is a widely-used
  technology that helps to copy and distribute data
  and database from one source to another. After
  completing this activity the process synchronizes
  between the databases so that the consistency and
  the integrity of the data are maintained. This
  process can be programmed to run constantly or at
  pre-determined intervals. There are several
  replication techniques such as one-way,
  one-to-many, many-to-one, and bi-directional, all
  of which leads to datasets being in sync with
  each other.

3
Types of SQL Server Replication Tools

• There are three distinct types of SQL server
  replication tools.
• Snapshot ReplicationThis tool simply takes a
  snapshot of the data on one server and moves it
  to another or another database in the same
  server. After the first synchronization snapshot,
  it is possible to periodically refresh data in
  published tables as per the pre-programmed
  schedule. This is the easiest of all replication
  tools but comes with a rider - all data has to be
  copied each time a table is refreshed. Between
  scheduled refreshes in snapshot replication, the
  data on the publisher might be different from the
  data on the subscriber.  

4

• Transactional ReplicationIn transactional
  replication, data is copied from the publisher to
  the subscriber(s) once and then transactions are
  delivered to the subscriber(s) as and when they
  take place on the publisher. The initial copy of
  the data is transported in a similar process as
  snapshot replication where the SQL server takes a
  snapshot of data on the publisher and then
  forwards it to the subscriber(s). But here, as
  and when database users update, insert or delete
  records on the publisher, transactions are
  forwarded to the subscriber(s).

5

• Merge ReplicationIn this technique, data from
  multiple sources are combined into a single
  central database. As in transactional
  replication, merge replication also uses initial
  synchronization through the snapshot of data on
  the publisher and moves it to subscribers. But
  unlike transactional replication, this method
  permits modifications of the same data on
  publishers and subscribers, even though
  subscribers might not be connected to the
  network. Whenever subscribers connect to the
  network, merge replication tool will notice it
  and combine changes from all subscribers and
  change the existing data on the publisher. Of all
  SQL server replication tools, merger replication
  is very useful if there is a need to modify data
  on remote computers even in the absence of
  continuous connection to the network.There are
  some of the important concepts of SQL server
  replication tools.