Hi

We have Sqlserver 2014 at our Head Office and Branch Office separately at different location. 

When ever data gets updated in Head Office database, database in Branch Office should get updated automatically Vise Versa.

What is the best option to do it.

Any Suggestions.

R

Hi raghu_grdr ,

According to your description, we need to verify that if you need to synchronize data bi-directionally or synchronize data only in one-way.

If you want to synchronize data bi-directionally between the head office database and the branch office database automatically, you can use merge replication and sync framework. However it is better to use merge replication because you can easily configure and administer a merger replication topology using replication's wizards, while the configuration of sync framework increases the cost and timeframe of implementation. For more information about merge replication and sync framework, please refer to the following links: Merge Replication and sync framework.

If you only want to synchronize data from the head office database to the branch office database, transactional replication and data mirroring can be used, I recommend you use transactional replication. With transactional replication, subscription database(branch office database) can be read and wrote. However, data mirroring requires that all involved server instances run the same version of SQL Server. During mirroring, data in mirrored database cannot be read unless you create a snapshot. For more information of data mirroring and transactional replication, please view the following links: Database Mirroring and Transactional Replication.

Best Regards,

Ice Fan 

Replication / Mirroring/ or any high availability is based on what we need. DO you plan to have your secondary(Branch office) readable/writable ?

I believe you could go with Replication if you want that way and say there are multiple branch offices and people in branch offices also need to update separately, then MERGE replication is the best option. In mirroring secondary will not be available (unless you failover to make it primary).

MERGE REPLICATION

Best Practic

Merge replication is the best option if you have only two different locations. -

https://msdn.microsoft.com/en-us/library/ms152746(v=sql.120).aspx

But if you have more than 2 different locations and all data needs to be available you need peer-to-peer replication. But this is a enterprise only feature.

https://msdn.microsoft.com/en-us/library/ms151196(v=sql.120).aspx

If the branches are not part of the Windows cluster you can use MERGE replication as Ashwin suggested below