Hi Team I have 3 data Centers ( geographically separated ). Let me call it ( Site1, Site2, Site3 ) Each Site has 1 SQL Server 2008 Each SQL Server has 3 DB – DB1,DB2,DB3 I need to collate all data from 3 sites , 3 DB ( total 9 datasets ) into one BIG DB. The thing here is : • DB1 – I need 100% data, all tables. • DB2 – I need 50% data, that is just 2-3 tables out of 200 tables. • DB3 – I need just 10% data. • Data in BIG DB does not need to be 100% Online. What are the best way to collate this data any pointers will be help. On daily basis we get around 30 Million new records ( consisting Inserts, Updates )
A couple of thoughts come to mind, depending on your requirements. All have pro/con's of course so you'll probably have to evaluate them individually. You mention that the data doesn't have to be 100% online, are you meaning that the data doesn't have to be real time? That the data could be hours or maybe a day older than the source? I would look at an SSIS package that can load the data (probably the deltas if you can swing it) into a central database location of your choosing. I think this would give you the most flexibility to manage the data. BCP might be another option. It has it's quarks but definitely usable for copying data from one source to another. Linked Server (shudder) is a viable option although not my first choice. You could setup linked server and then use queries to fetch all of the data. Personally, this would be my last choice with SSIS being my first choice to investigate. Replication is probably an option. You could replicate (snapshot maybe?) the data down to a central server and then use some ETL process to load it into your central database accordingly (SSIS, BCP, queries, etc). With 9 databases, this might be a larger undertaking than what is really required. Backup/Restore process. Much like Replication, you could just simply restore the appropriate databases down to the central server and then use an ETL method of your choice to "migrate" the data. This would also allow you to verify that your backups are valid which might be a bonus to things. NOTE: BCP/SSIS would allow you to copy the data into the central database without having additional "copies" of the database. Replication/Backup/Restore would mean that you would have a secondary copy of the data sitting somewhere to facilitate an ETL process. Personally, I'd start with SSIS as I think it'll offer the most flexibility and would be relatively easy to manage. Hope that helps!!