I want to sync 2 database specific records.
Let suppose I have two databases;
1.Shop
2.Stock
Now lets suppose user change the price of a specific product in stock. I want to change this product price in shop also!
What I work out is that - assuming Internet connection is stable,
- When price change in stock I invoke a web service this service will insert entries in web data table price.
- Now on shop side I ping that web data table using web service every 20 minutes if I find any new entry I update that relevant product price in shop!
Another option I thought about was replication. But we are using express edition of SQL Server and according to my knowledge express edition can not work as publisher!
Is my first option is efficient for this purpose or am I missing something and there is a better alternative to accomplish this purpose!
You could have a trigger on the table like pRime says above but instead of writing directly to the other database write the changes to a local "staging" table and then every 20 min or so schedule a task to send the updates to the second db.
You could set up the second DB as a Linked Server.
This way you avoid making the table the trigger is on Read Only if the connection between the two dbs goes down.
you can create a trigger on Stock table.
CREATE TRIGGER triggerName
ON [Stock].[dbo].[products]
AFTER UPDATE
AS
IF ( UPDATE (productPrice))
BEGIN
--insert to shop
END
GO
IF can't use the MS SQL Server replication feature (requires some non-Express edition as you already identified) for this situation (see http://msdn.microsoft.com/en-us/library/ms151198.aspx) then another option is to use the MS Sync Framework (can work with DBs down to SQL CE etc., files even custom data sources etc.) - see http://msdn.microsoft.com/en-us/library/bb726002.aspx .
IF you really want to implement this in code yourself (I strongly recommend against that) then implement it as a "push-scenario" :
- DB triggers which fill staging tables
- Windows Service which does check for changes in the staging tables and apply themn
- conflict resolution rules
- complete logging of all this to be able to analyze discrepancies (just in case)
