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?
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.
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.
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.