April 14, 2010 at 9:14 am
We're trying to avoid throwing a home grown application in between two databases. We do not have file sharing enabled between the two servers and it won't happen, so we can't use DTS Export and Import to move the data back and forth.
We need access to both databases so transactional replication using programs like Double-Take are out because the SQL Server services are required to be stopped.
So I am hoping that there is a method of using T-SQL to transfer data between instances. I know you can use it to transfer data between databases on the same instance and I've had to do that in the past a bit with 2000, I also have to admit that I do not know much about 2008 which we are now using.
Any ideas before I have to break out Delphi and write an app?
April 14, 2010 at 9:41 am
you can use SQL Server Integration Services to do this.
April 14, 2010 at 9:55 am
The best solution depends on how much data you need to transfer and the frequency with which you need to do so, as well as the bandwidth between the two locations.
A couple of options are:
Set up a linked server connection and then just use a 4 part naming convention (<servername>.<databasename>.<schemaname>.<tablename>) to query data from another database in the same way as you would on a local server.
Replication - No idea what Double-Take offer, but replication is a powerful, native product in SQL Server to copy tables from one server to another, either transactionally (near real-time) or through snapshots (periodically). It's got very little performance overhead and can cope with very large volumes and both databases are read/write.
You could also use SSIS without needing to physically place files on a file system to pull data from one end and (bulk) insert on the other.
April 14, 2010 at 10:06 am
I like the linked server option, if you are willing to create linked servers. Otherwise, why not use an ad-hoc query?
INSERT INTO localdb..localtable
SELECT *
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=RemoteServer;UID=YourID;PWD=YourPass',remotedb..remotetable)
_________________________________
seth delconte
http://sqlkeys.com
April 14, 2010 at 11:25 am
First of all I would steer clear of DoubleTake, my opinion. I've been subjected to nothing but pain by that package.
As far as data movement. I am generally against linked servers. If you are movie just a very little bit of data then *maybe*. Otherwise SSIS is almost certainly the way to go and it will almost certianly be FAST.
Replication is an idea as well, depending on how current you need the servers to be, it might make sense to setup transactional replication.
Database mirroring is also an issue but one I am not a fan of. To get true transaction consistency the system has to do what is called a two-phase commit, which means it has to commit it on the remote side and the local side and both take time. This is before the next statement is executed. If you turn that off then they don't guarantee that the changes will make it to the mirror..
Also, you could consider Service Broker, but that would depend on data volume.
I'd probably push SSIS.
CEWII
April 14, 2010 at 1:35 pm
I'm not sure that replication is the answer.
We have a database with all of our customer's data ServerA, and we have another database with just one customer's data ServerB.
The data is coming from an antiquated system being output to a directory on ServerA. We have a routine that runs approximately every minute it outputs the file in tab delimited format, and executes a batch file that runs sqlcmd to import the file into the database.
We are using the same command to output files for just the one customer, only the problem is, we can't move the files onto ServerB, we can't map a drive in either direction, but we can talk to the database on either server from either server.
So we could just export the data from one server to another, but the command would need to be executed from a batch file, meaning a stored .SQL script, or a stored procedure.
I considered DTS, but like replication, I don't know enough to stop it from moving ALL of the data instead of just some of the data.
I'm playing with OPENROWSET right now, I guess I was not searching for correct terminology. If this doesn't provide what we want, I will look into the linked server option.
As far as SSIS goes, I did look into it and I beleive it to be exactly what we want and need, but we're on a fairly strict timeline for this project, so tweaking and efficiency will have to come later, functionality first. (And I hate that).
April 14, 2010 at 1:40 pm
You can filter what is set, it is little more than a WHERE clause. As far as SSIS (NOT DTS) you might even be able to use the query that is used to generate the flat files as the source. I might be able to help you with this.
CEWII
April 14, 2010 at 4:25 pm
Thanks Elliot, in the end SSIS is probably what we will go with. Can you point me in the general direction of getting started with it?
Do I need to install Microsoft Business Intelligence Development Studio to use it, or is there a seperate download for it?
April 14, 2010 at 10:11 pm
You can do the install from the SQL Server disk. If you have VS 2008 already installed you will need to apply VS SP 1 first or it won't install. BIDS is just an addition setup on top of VS.
CEWII
April 15, 2010 at 6:25 am
You need BIDS installed to create SSIS Projects.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply