Microsoft SQL Server Replication
While 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.
SQL Server 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.
There are 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.
First 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.
Select Replication as shown below, it displays Local Publications and Local Subscriptions.
Right click 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.
Last three 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.
