January 18, 2010 at 12:52 am
Hello there,
I have multiple databases (with the same structure but the data may be different) distributed in many locations. Now, I want to collect all that distributed data into a central database for reporting purpose.
So, could you please help me if the replication approach is appropriate in this situation?
If the answer is "YES", then will the "central subscriber, remote distributors" topology be appropriate?
I intend to make the distributed databases be the "Publishers" and the central database (which I want to have the all data gathered from the distributed databases) will be the "Push Subscriber". Will this be good for this situation?
I am new to SQL Replication so I'm not sure if there will be conflicts in data when using this tool (e.g. PK - FK conflicts).
Please kindly help.
Thanks.
January 18, 2010 at 1:48 pm
see my comments in bold...
I have multiple databases (with the same structure but the data may be different) distributed in many locations. Now, I want to collect all that distributed data into a central database for reporting purpose.
So, could you please help me if the replication approach is appropriate in this situation? ----"YES"
If the answer is "YES", then will the "central subscriber, remote distributors" topology be appropriate?
I intend to make the distributed databases be the "Publishers" and the central database (which I want to have the all data gathered from the distributed databases) will be the "Push Subscriber". Will this be good for this situation? --"OK -If your publisher is not heavy loaded"
I am new to SQL Replication so I'm not sure if there will be conflicts in data when using this tool (e.g. PK - FK conflicts).---> YEs, you have to take care of this otherwise you will be having issue. check this completely before implementing
HTH
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 18, 2010 at 10:18 pm
What are your PKs? Are they integers or characters? You could have conflicts. Typically if you are merging things, you want GUIDs to minimize conflicts.
Depending on what you are bringing together, you may want transactional or merge replication, but you ought to read up on how they work and xfer data and then ask specific questions about your situation.
January 19, 2010 at 1:51 am
Vinay Thakur-585143 (1/18/2010)
see my comments in bold...I have multiple databases (with the same structure but the data may be different) distributed in many locations. Now, I want to collect all that distributed data into a central database for reporting purpose.
So, could you please help me if the replication approach is appropriate in this situation? ----"YES"
If the answer is "YES", then will the "central subscriber, remote distributors" topology be appropriate?
I intend to make the distributed databases be the "Publishers" and the central database (which I want to have the all data gathered from the distributed databases) will be the "Push Subscriber". Will this be good for this situation? --"OK -If your publisher is not heavy loaded"
I am new to SQL Replication so I'm not sure if there will be conflicts in data when using this tool (e.g. PK - FK conflicts).---> YEs, you have to take care of this otherwise you will be having issue. check this completely before implementing
HTH
Vinay
Thank you very much, Vinay. 🙂
Yes, the PK-FK conflict is the thing I concerned most. Infact, I've tried the solution and found some conflicts that caused the replication job failed to succeed. So I am going to develop an application to handle those conflicts manually.
January 19, 2010 at 1:57 am
Steve Jones - Editor (1/18/2010)
What are your PKs? Are they integers or characters? You could have conflicts. Typically if you are merging things, you want GUIDs to minimize conflicts.Depending on what you are bringing together, you may want transactional or merge replication, but you ought to read up on how they work and xfer data and then ask specific questions about your situation.
Hello Steve,
Many thanks for your reply.
The database have both table objects with Identity value (integer) and character fields as primarykeys.
Using replication tool, I'm getting stuck with maintaining the consistency between the Identity key tables and depedency objects (there's no problem with the table with characters as primarykey). Hence, I am gonna give up the solution using SQL replication and build a custom tool to handle this.
January 19, 2010 at 11:20 am
I have one more question:
Will the SQL2005 Replication handle the conflicts better than SQL2000 Replication? I'm considering of moving the databases to SQL2005.
Thanks 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply