AshishSharma avatar image
AshishSharma asked

Data Transfer Across Multiple Sites

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 )
replicationsql serverlog-shipping
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
You need to combine information from 9 databases into 1 database (which will reside in 1 of the 3 sites)?
0 Likes 0 ·
AshishSharma avatar image AshishSharma commented ·
Yes that is correct understanding.
0 Likes 0 ·

1 Answer

JohnM avatar image
JohnM answered
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!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.