Centralizing of data of 8 different locations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Centralizing of data of 8 different locations

Author

Topic

jaydeep_js
Starting Member

3 Posts

Posted - 2013-05-23 : 04:45:45

Currently we have local database server (MS SQL Server 2008R2) situated in 8 different countries. I have to pull data from those remote database servers and pump it into Centralize database server located in India. There is no any connection between those database server located in 8 different countries.

How I can achieve this. Any suggestion. Can SSIS help me in this case?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:48:26

what do you mean by there's no connection? Are they in different domains? Are those databases accessible over internet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-23 : 06:34:13

backup restore. <ENCRYPT THE FILES> -- then Send the files somehow.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

jaydeep_js
Starting Member

3 Posts

Posted - 2013-05-23 : 09:57:00

All this 8 places are offices having window application generating some data. There no any connectivity between them.

quote:
Originally posted by visakh16

what do you mean by there's no connection? Are they in different domains? Are those databases accessible over internet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:49:15

quote:
Originally posted by jaydeep_js

All this 8 places are offices having window application generating some data. There no any connectivity between them.

quote:
Originally posted by visakh16

what do you mean by there's no connection? Are they in different domains? Are those databases accessible over internet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Then your best bet is what Charlie suggested. At the end of a day or at a convenient frequency do a backup from these dbs, send the backup files by some mechanism like ftp (or sftp for secured access) to the centralised server. Restore them as current day copies and sync up the data from them to your central server. Once sync is done you may delete the restored copies and keep it ready for next day restore.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jaydeep_js
Starting Member

3 Posts

Posted - 2013-05-24 : 08:16:20

So restoring database process will be manual process or we can automate this process with SSIS.Please advise.

quote:
Originally posted by visakh16

quote:
Originally posted by jaydeep_js

All this 8 places are offices having window application generating some data. There no any connectivity between them.

quote:
Originally posted by visakh16

what do you mean by there's no connection? Are they in different domains? Are those databases accessible over internet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Then your best bet is what Charlie suggested. At the end of a day or at a convenient frequency do a backup from these dbs, send the backup files by some mechanism like ftp (or sftp for secured access) to the centralised server. Restore them as current day copies and sync up the data from them to your central server. Once sync is done you may delete the restored copies and keep it ready for next day restore.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:39:20

you can automate backup process by using ssis packages. there're backup database task available in SSIS. you can make it dynamic by using expression builder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-25 : 15:16:42

You may be better served by pumping a single day of data into flat files vis SSIS package, then compress the files and FTP them to the central server at the close of business every night.

Then a job runs at the main data center which unzips the files and pumps them into the target tables.

This type of process makes a lot more sense than sending entire databases every night.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources