Strategy for transferring db data between sql servers

Question

I would like some advice on the best approach for transferring DB data from one SQL server to another.

This is my scenario:

• Customers can make orders for products either through our website (hosted front end) or through our in-office call center (back end)

• Customers, once logged in, have a $$ balance that is displayed on the website. This balance is retrieved from the back-end sql. This balance is also available via the call center. Products that can be ordered is determined by this $$ balance.

• Orders made from the call center get saved to a back-end sql DB. Orders made from the website get saved to a front-end sql DB but needs to be transferred to the same back-end sql DB as call center order asap so that the order fulfillment team can start working on it. The order fulfillment team don’t have access to the front-end.

• The $$ balance displayed on the website needs to take into account orders that have not been transferred to the back-end yet. Currently we have a bit flag "HasBeenTransferred" on the order record indicating if it has been transferred or not.

What would be the best method for transferring these orders from our front end to our back end SQL Db?

I’ve looked into SQL replication - but the problem I’ve come across is that I won’t be able to set the "HasBeenTransferred” bit flag reliably using this method and this is critical for the system to work

Any help would be greatly appreciated.

Answer 1

If you need to data transferred immediately, use either SQL Server Continuous Replication, or a trigger which executes a SPROC, which does populates the data in the other server via a linked server call.

If you don't need it immediately, use a SQL Server Job, which synchronizes the data at a given time interval.

Answer 2

You should probably still consider replication for this. Other than setting the flag, your scenario sound perfect for replication. And, you can customize the replication so that the flag can be set. Although, if you use transactional replication, the information could transfer before anyone can evaluate the "HasBeenTransferred" flag.