Solved

SQL Server and Multiple IP Addresses

Posted on 2013-01-25
9
488 Views
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 "192.168.1.1\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 198.162.1.1 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 198.162.1.5 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.
0
Comment
Question by:colinasad
  • 4
  • 3
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38820555
Hi,

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.

HTH
  David
0
 

Author Comment

by:colinasad
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.
Thanks,.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38821435
Hi

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.

Regards
  David
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:colinasad
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 198.162.1.5 rather than 198.162.1.1. 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 198.162.1.1 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.
Colin.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 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.

Regards
  David
0
 

Author Comment

by:colinasad
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.
Colin.
0
 

Author Comment

by:colinasad
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.
Regards.
Colin.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have done a reformat of your hard drive and proceeded to do a successful Windows XP installation, you may notice that a choice between two operating systems when you start up the machine. Here is how to get rid of this: Click Start Clic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now