SQL Server and Multiple IP Addresses

Posted on 2013-01-25
Medium Priority
Last Modified: 2013-02-24
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.
Question by:colinasad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 35

Expert Comment

by:David Todd
ID: 38820555

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.


Author Comment

ID: 38820831
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.
LVL 35

Expert Comment

by:David Todd
ID: 38821435

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.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 38822209
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.
LVL 35

Accepted Solution

David Todd earned 2000 total points
ID: 38822721
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.


Author Comment

ID: 38824029
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.

Author Comment

ID: 38905366
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.

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question