working on Web or Windows applications sometimes we may get the requirement like
to maintain to copies database. For example we are developing the some windows
application which works online and offline also, so it has to connect to local
database instead of central database. But given period of time this data has to
sync with central database. In this type of situation SQL Server Replication is
one of the best option to Sync two databases.
Replication is nothing but copying database objects between servers at initial
stages and later on it syncs only data changes, if you want we can sync any
schema changes also. There are four types of Replication techniques available
in SQL Server, those are Snapshot Publication, Transactional Publication, Transactional
publication with updatable subscriptions, and Merge Publication. In this
article we will discuss about each of these techniques.
two main components Publications and Subscriptions. Publication will create on
main server and Subscription will create on local machines. In SQL Express we
cannot create Publication only we can create Subscription. In Microsoft SQL
Server we can create both Publication and Subscription.
Microsoft SQL Server Management Studio => Enter Database server details and
connect. Here we have to enter actual server name details not like
localhost\mssql even though database server is in local machine otherwise we cannot
create any Publication or Subscription.
Replication as shown below, it displays Local Publications and Local
on “Local Publications” or “Local Subscriptions” to create new Publication or
Subscription as shown below.
Snapshot Publication: The Publisher send total snapshot of
the database to the all subscribers in given intervals. That means every time
Publisher deletes Subscriber database and recreates new database.
Transactional Publication: The publisher sends only the
differential data to all Subscribers after they received initial Snapshot from
the Publisher. In this type of replication only Publisher pushes the changes to
Subscriber and vice versa not possible.
Transactional publication with
updatable subscriptions: It works same as Transaction Publication, but here Subscriber also send
the changes to the Publisher. That means both Publisher and Subscriber will
update each other. Here after Publisher applying the changes, Subscriber will
push the changes.
Merge Publication: In this type of Replication both
Publisher and Subscriber will merge the changes on Published data periodically.
Replication techniques Transactional Publication, Transactional publication
with updatable subscriptions, and Merge Publication should use the Snapshot
Replication initially to have the same schema on both Publisher and Subscriber.
In my next
articles we will discuss about each of these Replication techniques in-detail.