Database Merge onto single Server
Question is I have a backup Server with 2 years of data on it using MS SQL, I also have a production server database, I want to merge the two onto a single server but having conflicts because most of tables are the same.I having been doing research into data integration as well as SQL server Business Intelligence, but really can't get a grasp of it. Also I'm trying to weight out the pros and cons.
I know by having this much data on one database will take a long time to run reports.
Steps for a data migration:
- Decide if you really want to do this, for reasons of scale, difficulty, cost/benefit, politics, whatever.
- Figure out what the new design of the system will be.
- Design system to import old data from arbitrary sources into new server
- Design and make sure everything using old systems can use new system
- Make very very sure you're all set.
- Actually implement what was designed in stages 3 and 4, completing the migration.
You say "tables have the same name". That either means you need to have step 3 merge the data from both sources, or it means that you need to make the future tables have different names in step 2 (which will matter a lot for step 4).