Database table synchronization?

[gbaughma]

I have a stand-alone SQL server for a POS system running SQL 2008, and then another SQLExpress 2008 box installed ON one of the POS systems, for "Stand-alone" operation.

I'd like to easily synchronize some of the tables (mainly the sales transactions and inventory items) between the two.

Is there an easy way using SSMS to say "Synchronize Server1.dbo.table with Server2.dbo.table"?

Obviously, the two systems won't always "see" each other, so replication, in my understanding of it, won't work.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[djj55]

I may be stating the obvious but, might I suggest using a job on the SQL 2008 box to update on a schedule. Of course you would need to check the connection between the linked servers.

I have actually did log shipping between a SQL 2000 and SQL 2008 by manually mimicking the process created by the wizard. (Yes I know I could not go the other way, but it was only for a week before an upgrade).


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gbaughma]

Mmm. I think I've figured it out.

1) Link servers together.
2) Create a temp table from a UNION ALL statement between the tables I need to replicate.
3) Truncate the two original tables
4) Insert temp table into the two (now empty) tables.

The whole thing should be about a half-dozen lines of Stored Procedure.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[djj55]

Sounds good to me. The only thing to watch for is timing of the empty tables, if the tables are large it will take some time to repopulate.

Do not forget (as I would) the try catch for rollback.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!