April 14, 2004 at 9:03 pm
This will take a moment to explain -- and my question overall is "Can replication help us with this?" Thanks in advance for any help you can offer...
We have multiple databases on a single instance of SQL Server, each client devoted to a separate client. The data in each db essentially centers around a Personnel table, with lots of foreign keys to other tables. The Personnel tables and several others have identity columns (which are set to NOT FOR REPLICATION). Some other tables provide reference values for the Personnel tables -- but the combination of reference values can vary slightly from one client db to another.
Lately, a need has come up to occasionally move a handful of personnel records from one client db to another. The challenges are:
- Move the personnel table data over
- Move all data FK'd to those records in about 15 other tables
- Move over any missing reference values needed to maintain DRI
The identity values and their FKs are the most worrisome. Values from the source db might likely already be in use on the target db. So the max identity values in the target db need to be evaluated, and the values of the transferred records increased to avoid collision -- but that means the same values for all the FK's data as well.
We would like to handle this like so:
- During the workday, when a user determines they need to move a person's record to the other client they add that person's ID to a log table
- Each night at midnight, a job or the replication system (or something) kicks in, scans the log table, and transfers over each person's record and all their supporting records, and any missing reference values
I see that replication lets me use dynamic queries to select the records to replicate, and I seem to be able to perform a replication from one db to another within the same server. But my experiments keep indicating "success" -- but nothing new arrives in the target db.
So, all that said -- is what I'm attempting possible? Will the replication process handle all this horror of finding and setting appropriate identity values, updating them across all the FK'd records being transferred, etc.?
Thanks again, in advance...
- Tom
April 14, 2004 at 9:40 pm
One important clarification occurs to me to make...
We don't ever want to replicate all the data from one db to another, just the selected personnel records and the supporting FK'd data in other tables.
Imagine that the source db has 3000 personnel records, and the target has 4000 records (all different, none in common). Tonight, when the process runs, we want to move over just (let's say) 15 records from the source to the target. Those 15 will thereafter be the only records in common.
From what I read about replication, it's mainly about, well replicating. We want to replicate small batches of internally consistent data -- we do not want to replicate entire tables.
Okay, thanks again.
- Tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply