May 24, 2011 at 7:26 am
Hi,
I need to create something that can export all the data of one table to a file and then, this file will be picked up by a person and sent to other computer where i have to insert this data on the same table of other DB server.
It's like replicating a table of one database to the other database (all the data of the table).
There is no internet on the place where the two differente servers are, so i need to export the data to a file and then , some person as to send this file to other computer where the file will be imported.
This is SQL Server 2000 but very soon ( 6 months) it will be migrated to SQL Server 2005.
What is the best thing to do in this situation , when we know that we will need to migrate to other database engine?
I was thinking in creating a stored procedure thar executes a bcp comand to export the file and on the other computer i was thinking in importing by using bulk load.
What do you think?
May 24, 2011 at 7:31 am
Or is it DTS and they SSIS better?
May 24, 2011 at 8:51 am
DTS doesn't exist in SQL 2005 except as a legacy hook up. I do not recommend using this.
I'm not quite understanding your comment about no internet access. I'm not sure if you mean that literally or if you mean that there is no network connection between the servers. If the later, SSIS won't help you. If the former, SSIS is a good way of doing that without any manual interference by people
EDIT: If there's no network, SSIS is a good way to just dump the data in a file. You'd need a separate package to import the data in the new server, though. Really, it's better if you have a LAN or network connection between the servers, though, because then you don't need a file necessarily. You could just direct transport the data.
May 24, 2011 at 8:56 am
There is no connection between the two servers.
May 24, 2011 at 9:04 am
How often is this data exchange going to be taking place?
Are both servers SQL Server (or will they both be SQL 2005)?
How far away, physically, from each other are both servers?
Is there a business case for them not being connected?
May 24, 2011 at 9:06 am
bcp makes the most sense to me. Easy to use, upgradeable across versions.
May 24, 2011 at 9:13 am
How often is this data exchange going to be taking place?
R: One time at the end of the day
Are both servers SQL Server (or will they both be SQL 2005)?
R: The two Servers are SQL Server 2000 Servers, within 6 moths the two will become SQL Server 2005 Servers.
How far away, physically, from each other are both servers?
R: Diferente Citis
Is there a business case for them not being connected?
R: There is now network or internet in that place of Africa
May 24, 2011 at 10:20 am
BCP is definitely the easiest option, given Steve's note on compatability.
But now I'm curious. If the servers are in different cities, and there's no internet / network connectivity, how do you plan on transporting the data once a day?
May 24, 2011 at 10:27 am
Differente city but only 3 km away.
By Car.
May 24, 2011 at 10:30 am
Okay. I recommend you encrypt the data, though, before you transport it. If there's private information on that data, and someone steals it, you have a serious problem on your hands.
May 24, 2011 at 10:41 am
How can i encrypt it?
May 24, 2011 at 10:56 am
Encryption is a huge subject. The easiest way is to encrypt the media the data is stored on, as opposed to encrypting the data itself. I'm not even sure you can use SSIS or bcp to encrypt data being sent to a file.
The only other way I know is to encrypt the database, export the encrypted data, and decrypt it on the destination database. You can find database encryption information here: http://www.sqlservercentral.com/articles/Security/3058/%5B/url%5D
And if you have any input on the SQL Server version being installed, recommend they go straight to SQL 2008 instead of 2005. Official Microsoft SQL 2005 support is being phased out this year.
May 24, 2011 at 11:08 am
Search encryption programs, or check for your OS. Win 7 has some built in options, including the ability to encrypt flash drives. Same for some of the Norton/Symmantec products.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply