Synchronizing two database

Question

Synchronizing two database

..+ hunter +..

Mr or Mrs. 500

Points: 533
More actions
August 12, 2009 at 9:43 am

#213396

Hi Guys
I am currently developing an application that has a capability to synchronized two database at first i try it using a trigger. You have any idea on how can i synchronized my two database without using a trigger? why am i asking this? due to some issue in using a trigger in a linked server :crying:
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

well if you are doing this to improve your understanding of how SQL works, I applaud you; sometimes reinventing the wheel can be a very rewarding experience education wise.

practically speaking, however, you'd usually use the built in tools SQL server can provide, for example replication, snapshot replication and mirroring, it really depends on your requirements.

you didn't really as a question...just made a statement on what you are planning to do; did you have a question or were looking for strategies?

triggers and linked servers are one way to do it; the way i've done it was by adding columns for last_updated to every table I cared to track, and the triggers to update that last_updated column during an UPDATE...then you can just scan for rows that have changed since the last time you synced them.

In my situation, we didn't need to track DELETED data, but I'd like to hear your strategy for when a row gets deleted...in or case we didn't want the changes propigated if deleted, so we kind of had the last updated value forever.

SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

..+ hunter +.. Mr or Mrs. 500 Points: 533 More actions · Answer 2

Hi Lowell

Thanks for a quick response, Actually my main goal here is I have two database in where it can be place in the same server (what we call a single server) or in a different server (this is what we call a Linked Server) in where if i Insert/Update/delete data in one database it will cascade in the another database which is perfectly working using a trigger in a single server but not in a linked server.

This two database are in different database schema so what i did is I make sure that the column available in the list of table in the first database is properly mapped in the table inside the next database. So what am i asking is like what you have said am i asking for strategies?Absolutely yes, I am asking for another strategies in where i can synchronized my two database without or aside from using a trigger?

I have noticed also that u said "SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger." an you give me some example?I will really appreciate it. 🙂

Regards,

Hamtaro

Lowell (8/12/2009)
well if you are doing this to improve your understanding of how SQL works, I applaud you; sometimes reinventing the wheel can be a very rewarding experience education wise.
practically speaking, however, you'd usually use the built in tools SQL server can provide, for example replication, snapshot replication and mirroring, it really depends on your requirements.
you didn't really as a question...just made a statement on what you are planning to do; did you have a question or were looking for strategies?
triggers and linked servers are one way to do it; the way i've done it was by adding columns for last_updated to every table I cared to track, and the triggers to update that last_updated column during an UPDATE...then you can just scan for rows that have changed since the last time you synced them.
In my situation, we didn't need to track DELETED data, but I'd like to hear your strategy for when a row gets deleted...in or case we didn't want the changes propigated if deleted, so we kind of had the last updated value forever.
SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger.

Lowell SSC Guru Points: 323504 More actions · Answer 3

the new 2008 booksonline has a lot of stuff on MERGE and EXCEPT

http://technet.microsoft.com/en-us/library/bb510625.aspx

the neat thing is how it has clauses for WHEN MATCHED and NOT MATCHED:, when the merge finds a match, based on the criteria, you know you need to update, if no match was found it must be new

USE AdventureWorks;

GO

-- Create a temporary table variable to hold the output actions.

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target

USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))

AS Source (NewName, NewReasonType)

ON Target.Name = Source.NewName

WHEN MATCHED THEN

UPDATE SET ReasonType = Source.NewReasonType

WHEN NOT MATCHED BY TARGET THEN

INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.

SELECT Change, COUNT(*) AS CountPerChange

FROM @SummaryOfChanges

GROUP BY Change;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

..+ hunter +.. (8/12/2009)
Hi Lowell
Thanks for a quick response, Actually my main goal here is I have two database in where it can be place in the same server (what we call a single server) or in a different server (this is what we call a Linked Server) in where if i Insert/Update/delete data in one database it will cascade in the another database which is perfectly working using a trigger in a single server but not in a linked server.
This two database are in different database schema so what i did is I make sure that the column available in the list of table in the first database is properly mapped in the table inside the next database. So what am i asking is like what you have said am i asking for strategies?Absolutely yes, I am asking for another strategies in where i can synchronized my two database without or aside from using a trigger?
...

Based on this description is sounds like a perfect use case for Replication.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

..+ hunter +.. Mr or Mrs. 500 Points: 533 More actions · Answer 5

Hi Lowell,

Thanks for the sample script 😀

Let me ask one thing?...using the sample script you gave me how it will be used? I mean is like, for example i have a windows application that will insert data in a database then using the script you gave me it will automatically insert the data to another database or do i need to run it again in order to cascade the inserted data from one database to another database? base on my understanding, correct me if i am wrong:-P i need to run it every time I insert/update data from one database in order to cascade to another database...

BTW, thanks for the sample scripts it serves as additional knowledge of my closet:cool:

Thanks

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 6

Hi

you can schedule the job which will execute after every 1 hour to synchronise database.

For synchronising as Lowell has suggested, a trigger can update date column or a flag which will mark records for synchronisation. Later your jobs picks up those records and reset the flag.

In a way it is doing replication.

Thanks,

Rupashri