Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Synchronizing two database Expand / Collapse
Author
Message
Posted Wednesday, August 12, 2009 9:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 17, 2009 4:27 PM
Points: 25, Visits: 43
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

Thanks
Post #769439
Posted Wednesday, August 12, 2009 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #769482
Posted Wednesday, August 12, 2009 10:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 17, 2009 4:27 PM
Points: 25, Visits: 43
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.
Post #769499
Posted Wednesday, August 12, 2009 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #769591
Posted Wednesday, August 12, 2009 12:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
..+ 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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #769597
Posted Wednesday, August 12, 2009 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 17, 2009 4:27 PM
Points: 25, Visits: 43
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 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

Thanks
Post #769603
Posted Monday, August 17, 2009 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 27, 2010 11:18 PM
Points: 43, Visits: 35
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
Post #772427
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse