question

Bab avatar image
Bab asked

import and export data from a database on a different network

Is it possible to import and export data from another database that is located in a different network? Can we use DTC for this purpose?
sql-server-2005sql
1 comment
10 |1200

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

Bab avatar image Bab commented ·
Is that mean DTC has nothing to do in this case? since database is huge and I need only 7-8 tables from a database and I need to update my database very frequently like every hour which method you guys think will be the best. Which replication will be best in this case? I also need to export data into the same database ( not in the same network) so which method will be the best for me? Is import and export is possible between two databases if they are on two different networks and the server can not see each other.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
If the servers can't see each other, then you need to either find an intermediate server that can see both, or figure out some way of moving files between the two. If you have an intermediate, then you could run an SSIS task from there, pumping data between the other two servers. Of course, this won't be as quick as a direct connection... How do you access the data on the remote system now? (or is this a new thing?)
2 comments
10 |1200

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

Bab avatar image Bab commented ·
Thomas, This is new to me. I have import and export data from/to two different servers but not from two different networks. Do you have some links where i can research on how to add the server from one network to another? and again can't DTC help to access the servers from one network to another.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
A few things to think about - what's the network path between server A & server B? Are there firewalls in the way? If so, then you'll need to speak to your firewall guys (assuming you want the direct link) - get them to open up TCP/IP ports for SQL Server & DTC - ports 1433 & 135 should do the job.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Depending upon how much data you are wanting to move around you have a few options (in the order I would go for): 1. Backup & Restore (either the entire database where the data resides, or push the data you want moving into a second "temporary" database) 2. BCP / SSIS (dump the data you want to move to flat files, then copy them to the new system and import using BCP/SSIS) 3. Replication (replicating only the tables you want using snapshot replication)
10 |1200

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

ThomasRushton avatar image
ThomasRushton answered
Import/Export from a second network? Assuming that the two servers can see each other, and it's not a huge amount of data you want to shift, you have a few more options over those that @WilliamD has suggested: - Linked Server - set up a direct link between the two servers, so you can issue queries from one against the other - [OpenRowset][1] - create an ad-hoc connection within a SQL Server query Otherwise, I would look at going with those options of @WilliamD's, with the following extra twist: - Reduce the size of the backup before transferring it - or, indeed, while backing it up. Look at Quest Software's SQL Litespeed for example (other products exist from Redgate, Idera and others) And if your network is slow, remember that it can sometimes be quicker to copy data using a USB hard disk... [1]: http://msdn.microsoft.com/en-us/library/ms190312.aspx-
10 |1200

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

Tim avatar image
Tim answered
Based on your updated information I would use a linked server with SQL accounts and issue a few queries to fresh the information in your destination tables. The first query would likely be an insert of all the records you need, then going forward use a WHERE clause to just get the updated records.
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.