Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I have a bunch of SQL Servers installed on Windows2008R2. Currently, each windows server has two NIC's, each connecting to separate networks. In other words, each server appears on each network. One network allows the applications to access the database, the other network is intended to be used for large volumes of data being migrated between the servers without impacting the network the applications use.

I have a job that pulls in a large chunk of data from one SQL server to another. I would like that job to take advantage of the second network as well, but I am stumped.

Is there a way to tell SQL to use a specific network device when communicating to another server?

Thanks!

share|improve this question
2  
The instance of SQL Server you are talking to would have to be listening on the other network. If it is you should be able to specify that IP for your connection to force it over that network. I would think, never done it so just guessing. – Shawn Melton Aug 3 '11 at 20:22

3 Answers

up vote 3 down vote accepted

Check on the TCP/IP listening address in SQL Server Manager, both servers have to be listening on the same network (probably both server will be listening on both networks) When connecting each server to another, you will have to specify the IP address and that should do it.

share|improve this answer
up vote 3 down vote

I often use that kind of setup with dbservers connected to two networks.

Usually I name them or call the networks something like this

dbnetwork - for communication inbetween dbs or whatever datasources i need to have access to. clientnetwork - her is the network from where my clientapplications may access the sqlservers.

You can steer what network will be used by using different ip at the very basic level. If you want to make it more and better you can name the servers with names like db1dbnetwork, db1clientnetwork. this name you can store in host file or dns depending of what you find convenient.

I usually do use as much as possible the dbnetwork for interactions and datatransfers inbetween the dbservers so that the clientnetwork wont be saturated from internal transfers in my dbservers. If you feel the the single NIc is a bottleneck for the amount of data you need to transfer do look into getting faster NIC or add a third NIC and pair it with the one already connected to your dbnetwork to increase datatroughput. Only do this if you are certain that it is the NIC that is the bottleneck ofcourse.

share|improve this answer
up vote 2 down vote

As others mentioned, each network connection should have its own IP address. If you can get to the machines that way, it would allow you to specify which network to use.

The only other option I can think of would be to set up a "third party"--something like a NAS or another computer that you could shove the data to temporarily and then move it to the other server. Of course that's not ideal since you'd be moving the data twice.

Try connecting via IP address. That's really the best way.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.