|
|
|
SSC 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
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 5:07 PM
Points: 3,927,
Visits: 10,634
|
|
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 your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
SSC 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.
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 5:07 PM
Points: 3,927,
Visits: 10,634
|
|
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 your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 9,104,
Visits: 8,528
|
|
..+ 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."
|
|
|
|
|
SSC 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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, August 31, 2009 10:09 PM
Points: 13,
Visits: 34
|
|
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
|
|
|
|
