SQL Server and Multiple IP Addresses

This question is possibly mainly to do with networking and IP Addresses, but it is particulary with respect to connecting to SQL Server databases.

I am developing an Access 2007 "project" (.ADP) as a front end to data stored in a SQL Server 2005 Express database. I do the development work on my Windiws XP laptop at home (that runs both Access and SQL Server), then use "Remote Desktop Connection" to send an ".ADE" version of the Access program to my client's Windows 2003 Server computer, from where it is copied down to all their users' PCs.

I feel that an IP Address in the Access application's "Connection string" seems to produce a better performance than a computer name. ie "\SQLEXPRESS" seems better than "JC-SERVE05\SQLEXPRESS".

On my small network at home (my Windows XP laptop, a Windows XP desktop PC, and a Virgin Media "Super Hub" that acts as a DHCP server and connects with my cable broadband service to the outside world) I have named my own desktop PC (which also runs SQL Server) "JC-SERVE05" so that it has the same name as my client's Windows 2003 Server computer. This means I can easily create an ".ADE" file with a "JC-SERVE05\SQLEXPRESS" "Connection string" before I copy it across to my client's site.

This all works OK so far. When my client's users launch the ".ADE" file on their computers it connects with the SQL Server instance on their "JC-SERVE05" server. What I would like to do is start to use an IP Address in the "Connection string". The IP Address of my client's server PC in their ware house is but I seem to be unable to assign that IP Address to the desktop PC on my home network.

Before I ask their IT people if they can change their server's IP Address to something like so that it matches a value I can also have at my end, I thought I would investigate the option of assigning multiple IP Addresses to a single Ethernet card.

This appears to be possible using Windows XP's "Network Connections" utility but you have to disable the "DHCP acquisition" option. Adding two IP Addresses to one computer on my home network has mixed results. Although I can successfully "ping" both IP Addresses from the other computer, I can only successfully use one of the IP Addresse in a "Connection string". The connection to the SQL Server works only with one and not the other. Also, the computer that has been assigned the two IP Addresses can no longer access the internet (presumably still via my Virgin Media "Super Hub" that is still working as a DHCP server) despite retaining the same "Default Gateway" value which is the IP Address of the "Super Hub" itself, and also being seen as connected to the local network when I log on to the "Super Hub" and use its utility programs.

I am perhaps being a bit naive here, and hopefully someone can set me straight. Two specific questions I guess :
1. Why does the Access-SQLServer "Connection string" only work with one of the IP Addresses assigned to a host computer and not with them both?
2. Why is the computer with the two IP Addresses blocked from accessing the outside world?

Any help gratefully received. Thanks.
Who is Participating?
David ToddConnect With a Mentor Senior DBACommented:
Hi Colin,

I mean that your application shouldn't be ip specific, that it should use dns name - unless it is a system application in that space, or unless they specifically ask you to.

Normal practice is to use the name.

Many businesses will be moving to ipv6 over the next years, in fact in Windows 7 and Windows 8 you have to turn ipv6 off as its on by default.

Changing the network structure is something that is done occasionally - people will not expect the application to use the ip, they'll expect it to use the name and resolve that by dns lookup.

With 1Server/20PCs your client is ripe for a cloud based solution. Imagine all the infrastructure that goes into supporting 1 server - the ups, the tape backup, the closet, the air-con for said closet, the dr plan. A hard-coded ip address in your solution mitigates against that. This increases the chance that your solution will be thrown out and replaced down the track, and you've lost a client.

All that to say this: While using the ip address sounds great now, the future is not so great. Maintenance is a real when tripping over clever code, application/infrastructure is a real chore with things that are non-standard.

David ToddSenior DBACommented:

Suggest that you try to keep things as vanilla as possible.

That is, stay away from multiple ip's and connecting by ip if you can. Else just making problems for yourself (and others) down the road.

Ideally, SQL being a server service should be on a machine with a fixed ip - at least at the clients. If there is a marked delay for your client resolving the name to the ip and thence to connecting, that is their issue to solve.

Performance issues on your xp machines and super hub related to DNS should not translate to a problem with your clients connecting. I suggest you confirm your impression with the client.

If performance is an issue, I suggest you redevelop your code in dot net - either vb.net or c#.net - you can download the express versions of these for free I think - rather than using an access project.

colinasadAuthor Commented:
Thanks for the prompt response, David.
I take on board what you are saying about keeping things as simple and straightforward as possible.
I am hoping my client can assign a different IP Address to their server; one that I can also assign to a machine on my home network.
I just wanted to check out the options myself first and got led down this multiple address avenue.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

David ToddSenior DBACommented:

Depending on the size of your client that could be very disruptive to their infrastructure.

If your business user asked me to do that to infrastructure I managed, I'd veto your development. Please don't do this for no gain.

colinasadAuthor Commented:
Thanks again David.
Do you mean I shouldn't be asking my client to change the IP Address of their server at all?
I can fully understand that asking them to assign multiple IP Addresses might be pushing "normal practice" a bit.

Surely it's not too big a deal to assign a statitc IP Address to a server that is something like rather than I know I can do this on my own router (that acts as a DHCP server) and it doesn't seem to cause any problem. The only problem I had was that it would not then allow me to assign to one of my computers, which was my original attempt at making one of my computers the same as their server.

My application is the major one running at my client's site (one server and about 20 PCs). I'm not aware of another application they run that would be "IP Address - specific". I will of course be asking their main IT guy to OK it and actually do it himself.

Thanks & regards.
colinasadAuthor Commented:
Many thanks again, David.
I appreciate your wise words and will see what my client's IT guy says when he replies to my e-mail.

The performance improvement with IP Address over DNS name is perhaps not worth going "off-piste". I just feel that things are a bit faster (especially when I use "Test Connection" button after changing the Connection String - the "Test connection succeeded" message seems to appear almost instantly when an IP Address is used.)

Thanks & regards.
colinasadAuthor Commented:
Apologies for letting this question lie so long.
I haven't heard back from my client's IT people with their thoughts.
I am happy to have the points awarded to dtodd as suggested by LeeTutor.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.