Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server and Multiple IP Addresses

Posted on 2013-01-25
9
Medium Priority
?
543 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
[X]
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
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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