November 11, 2015 at 5:00 am
I have a PROD DB and a archive DB which is a subset of the archive DB . I need ti merge the DBs to a single DB on a secondary server for reporting purpose . Will appreciate if you can tell me , which is the way to implement it . Thanks in advance.
November 11, 2015 at 5:09 am
Do they have to go into the same DB?
Could you not use union and 3 part naming convention
eg
select ... from ProdDB.ProdSchema.ProdTable
union
select ... from ArchiveDB.ArchiveSchema.ArchiveTable
Other methods would be to SSIS the data using a merge operator.
T-SQL scripts to merge the data
Replication
November 11, 2015 at 5:20 am
Assuming that they are both still changing and that these changes need to be reflected in the reporting server, I would keep them separate.
Consider using replication to keep the DBs in line on the reporting server. Change tracking and SSIS are possible alternatives.
Build UNIONed views/procs/ whatever is needed, possibly in a third separate database, and report from those.
November 11, 2015 at 5:54 am
Thanks Anthony and Phil for your suggestions !!
The prod server data gets moved to the archive DB. Now on the secondary server i only need need 1 DB . I can setup transactional replication without DELETE from the prod server . But before that i need to merge the prod db and archive DB .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply