SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Centralizing of data of 8 different locations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaydeep_js
Starting Member

India
3 Posts

Posted - 05/23/2013 :  04:45:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/23/2013 :  04:48:26  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/23/2013 :  06:34:13  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

jaydeep_js
Starting Member

India
3 Posts

Posted - 05/23/2013 :  09:57:00  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/24/2013 :  00:49:15  Show Profile  Reply with Quote
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
Go to Top of Page

jaydeep_js
Starting Member

India
3 Posts

Posted - 05/24/2013 :  08:16:20  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/25/2013 :  09:39:20  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/25/2013 :  15:16:42  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000