Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
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 '13 at 5:12
add comment

migrated from stackoverflow.com Mar 24 '13 at 10:32

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.