September 11, 2008 at 2:15 am
Hey,
Say I have three database servers lets call them PrimaryDB, RemoteDB1 and RemoteDB2.
RemoteDB1 and RemoteDB2 have a database table which has an integer primary key with identity set, lets call this ReplicateTable. The tables schema and database is the same on both RemoteDB1 and RemoteDB2.
What I would like to do is perform push based transactional replication from RemoteDB1 and RemoteDB2 (Both acting as distributors) for table ReplicateTable to PrimaryDB. So the target table on PrimaryDB contains contents of both ReplicateTables on RemoteDB1 and RemoteDB2 respectively.
The keys do not need to match when replicated so it shouldn't try and perform an identity insert. From what I understand of replication thus far this would pose a problem as the primary key identifies the row and in this scenario the keys wouldn't match.
Further to this I do not need to keep the data on the remote databases once it has replicated. I really just need something which pushes the data to the primary server and then after a period of time this data can be culled.
I am starting to think that replication is not the correct solution to this problem and that instead we should have a script or an application which bulk copies the data as a job and then another process which removes old data. Before embarking on this I thought it best to first ask the experts.
I hope that made sense! Thanks in advance
Regards,
Mark
September 11, 2008 at 5:10 am
Hey all,
Ok after some thought I really don't think replication is correct for this scenario.
I have created a stored procedure which copies data using linked servers and runs as a job in the sql server agent. This is working fine.
However if you disagree I would be interested to hear any views.
Cheers,
Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply