VB.Net - Connect to multiple SQL instance

Hello Experts,

I have multiple SQL server across the States that I need to connect to and pass data from one to another.  All the databases are the same scheme/Configuration/etc.  

What I had done was link all the other SQL instances to the main SQL server and connect to it that way.  However, it seems to be very slow.

Current Situation:


Is it possible to do something with connection string connecting directly to each database and passing data that way?

Dim conWI AS String = "data source=WI-SVR2;persisty security info=false;initial catalog=DB2;UID=sa;PWD=Password1"

Dim conNY AS String = "data source=NY-SVR1;persisty security info=false;initial catalog=DB1;UID=sa;PWD=Password1"

Not sure how to incorporate that so that it would open one database and pass data from one to the other.  Any ideas how to do this or can it be done?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
You don't say that you are using replication, only that you are linking the servers.

Why don't you use replication on the SQL Server side of things to propagate the data? That would at first glance seem to be more enterprise centric.

holemaniaAuthor Commented:
Because I'm not really replicating data.  Data is somewhat unique to each site.  I only need to transfer specific information base on the need of the user and then modify the rest to be as generic as possible.  My query was just a short simple version of what I'm trying to do.  

Each database has unique location, entity, etc that needs to be reset to a generic that exists in all databases before I do the transfer of data.  With the query example, I can do it fairly well except with performance.  It takes about 3 minutes or so to insert something across servers verse seconds if I have both databases in the same box.  

Just curious if there's a way to open both database similar to my sql query and then select from one database and insert it to another?
Bob LearnedCommented:
Are you talking about the full CRUD, or just inserts?  If you just need inserts, then you might be able to use the System.Data.SqlClient.SqlBulkCopy class to do a bulk insert from a source DataTable to the target server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Yes, that's what I was looking for.  Found an example online after you post about sqlbulkcopy.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.