SQL Server sync databases between enterprise - web edition

Question

We have an enterprise version of sql server 2012. we need to synchronize its data to a web edition database. what would be the best option t do this ? further to add on to this we need to provide high availability on the website. so the plan is to use 2 identical database on the web edition. only one will be active at a time. periodically the enterprise version needs to push the data to the inactive database. make it active. and then sync the same data to the other one.

what would be the best solution to achieve the above ?

Answer 1

What Neil has suggested is a viable option, but it is geared more towards Cloud environments and will be more complex than what you are trying to achieve.

OPTION 1 :

The simplest solution would be Transactional Replication (if you want near to real time data) or snapshot replication depending on your NEED.

You can use Replication from Enterprise Edison to Web edition. Important to note that web edition does not support PULL Subscription. It only supports PUSH Subscription.

Depending on your business needs, you can schedule to sync every 1 hr or 1 day.

Replication support matrix is here

OPTION 2

Use 3rd party applicaitons like Redgate schema and data compare for syncing data between 2 databases. It also has command line options, so you can write up your own custom scripts that will periodically sync data. You can get started here

SQL Server also has an inbuilt data sync functionality which is commandline called "TableDiff".

As you see, there are many options, which one will suffice your need depends on your Business requirements.

Answer 2

One viable solution could be the Microsoft Sync Framework

A comprehensive synchronization platform that enables collaboration and offline access for applications, services, and devices with support for any data type, any data store, any transfer protocol, and any network topology.