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 ««12

Best way to move data from one db to another and maintain relationships ? Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 11:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,917, Visits: 2,531
I don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1465852
Posted Thursday, June 20, 2013 11:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
sjimmo (6/20/2013)
I don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.


Yes, they are killing me. I had to add a [NewId] field to each table, iterate thu each table saving rows individually so I can capture the [NewId] value, then save that back to the staging table row. Then for related staging table data I have to join on old ids within the staging tables, but use the [NewId] value(s) when I insert them into the live tables.

It's straight-forward enough but is a very brute force methodology.
Post #1465855
Posted Thursday, June 20, 2013 1:49 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
William Plourde (6/20/2013)
So a person could move from db A, to B to C to D etc?


Yep, that is correct. Assuming 6 dbs out in the future. A person (and all their related data) can move from any db to any other db. Possibly more than once over time due to business logic.


Just curious, why different DB's? Why not partition the tables instead and save the headache?
Post #1465916
Posted Thursday, June 20, 2013 2:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
DiverKas (6/20/2013)
William Plourde (6/20/2013)
So a person could move from db A, to B to C to D etc?


Yep, that is correct. Assuming 6 dbs out in the future. A person (and all their related data) can move from any db to any other db. Possibly more than once over time due to business logic.


Just curious, why different DB's? Why not partition the tables instead and save the headache?


There are some business and potentially legal reasons behind the decision. I have not personally used partitioning but I know what it is and I'll brush up on the topic to see if it can help. But to make a long story short if our system gets large enough we can dynamically add a new database and auto-magically start loading data onto it,even if that db is on another physical server. So it's not quite the same as partitioning. Its more like home grown load balancing for data.
Post #1465940
Posted Friday, June 21, 2013 6:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
There are some business and potentially legal reasons behind the decision. I have not personally used partitioning but I know what it is and I'll brush up on the topic to see if it can help. But to make a long story short if our system gets large enough we can dynamically add a new database and auto-magically start loading data onto it,even if that db is on another physical server. So it's not quite the same as partitioning. Its more like home grown load balancing for data.


Ok, I understand what your trying to accomplish, but introducing changes to xn databases seems problematic at best. Not to mention setting up the required maintenance, backups etc.

Just seems partitioning would be a cleaner, less maintenance intensive way to go.
Post #1466157
Posted Friday, June 21, 2013 6:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,167, Visits: 12,017
No doubt it's too late to change this, but have you thought about using NewId rather than identity as your (nonclustered) PK in the various tables?

As far as I know, this is guaranteed to be unique across the distributed instances and could therefore become the 'permanent' PK for a row, regardless of database.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1466160
Posted Friday, June 21, 2013 7:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
Phil Parkin (6/21/2013)
No doubt it's too late to change this, but have you thought about using NewId rather than identity as your (nonclustered) PK in the various tables?

As far as I know, this is guaranteed to be unique across the distributed instances and could therefore become the 'permanent' PK for a row, regardless of database.


fwiw, I agree with you. I think using guid pks with NewId() for a default value is probably the best way to go. If this were the case I should be able to select all the data for a person from one db and just save it to another db. From what I understand guids are the way to go when consolidating data from multiple dbs.

For some reason our team is really trying to avoid guids as pks. In general we have some bad experiences with guids due to work done in the past where guids were used but were not necessary. I think that's made everyone here gun shy about using them.
Post #1466164
Posted Friday, June 21, 2013 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,167, Visits: 12,017
Well, if I were you I would suggest it, so that when the pain of maintaining your IDENTITY solution becomes too much to bear, you can always be the smart one who says: I told you so


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1466182
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse