0

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 ?

Improve this question
1
  • I hope that "so the plan is to use 2 identical database on the web edition" actually means "we have two servers running web edition". More generally, "high availability" and "web edition" don't really belong in the same sentence. The only HADR features in web edition are replication and log shipping, both of which are near-realtime. Failover cannot be guaranteed to be lossless.
    – Greenstone Walker
    Mar 25, 2013 at 5:12

2 Answers 2

Reset to default
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.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.