Suppose you are working as Database administrator for your company and requirement is to replicate table’s data to another sql server instance installed on reporting server. In this way, they want to reduce network traffic, those users who use to generate reports based on one day old data. Now after replicating data, they will connect to reporting server to generate their daily business report.
You have to create a replication topology to apply this requirement. Let’s say table names are Orderdetails and Products. You have to make sure that if network drops due to any reason, these tables should get updated on reporting instance when the reporting server reconnects to the corporate network. The Orderdetails and products tables on reporting instance is frequently updated between reconnections.
You have to make sure that you can implement the replication topology along with the subscription type successfully and meanwhile reduce the bandwidth usage to the least.
Question comes in the mind that what is replication?
SQL Server offers replication as technology to replicate or copying the data or database objects from one database to another and maintains the database consistency by synchronizing them.
Replication will help you to distribute data from one location to another remote location either using internet or network connection. Using replication, sharing data across different location become easier. You can create copy to database on remote location that can help you in the time data recovery during disaster.
Here if you make any changes on one instance that will replicate to remote database without manual intervene.
Terminology used in this article
SQL Server supports four types of replications
- Snapshot replication helps you to copy database objects or data till that time.
- Remote client are updated with complete modified data whenever snapshot runs
- You can use this type of replication when the amount of data to be replicated is small and static or does not change frequently.
- Also known as static replication by nature.
- In transactional replication, modified data travels from server to client and get updated every time whenever modification occurs on server side.
- While configuration it require snapshot of database as basis for further processing.
- Server and client are always in sync and should always be connected.
- You can use transaction replication when client always need the latest data for processing.
- Transactional replication is also known as dynamic replication.
In Merge replication, you can make changes on both sides (server side as well as client side). Whatever changes have been done at client side goes to server and reconciles conflicts based on rules created by you using conflict resolver.
By default, server wins all conflicts that it has with clients because it has the highest priority. The conflict resolver can be customized.
Server and client can update the published data independently after the client receives an initial snapshot of the published data. Changes are merged periodically.
You can use merge replication to refresh data at client whenever client connect to network.
Microsoft SQL Server Compact Edition can only subscribe to merge publications.
Replication agents involved in merge replication are snapshot agent and merge agent.
Now question is which replication you will implement to achieve your goal?
Here is the Answer: you should implement merge replication topology along with a Pull subscription as changes are made constantly at the publisher(Server) and subscribing(Client) servers, and merged in the end.
Why Pull subscription ?
There are two subscription available in sql server either create as PULL or PUSH subscription.
Push subscription involves centralized distributor for controlling and syncing of all the changes done. This configurations helps you to pushes the changes whenever any change occur at publisher end. Moreover since this is a centralized way it’s helpful as all the subscribers to be updated by the distributor itself.
Pull Subscription is use to pull the changes from publisher on need basis. it triggers from client side without depending on the publisher
This is useful for those system which are not constantly connected to the Server. No use of push jobs even when the client is not connected. Rather whenever the Client gets connected to the Server it simply pulls the data and syncs up.
Merge replication is typically used in server-to-client environments. Like transaction replication, a merge replication starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the publisher(Server) and subscribers(Client) are tracked with triggers. The subscriber(Client) sync with the publisher(Server) when connected to the network and exchanges all rows that have changed between the publisher(Server) and subscriber(Client) since the last time synchronization occurred.