I have an IP network and want to automatically find a free IP address to provision a new server. Due to frequent server additions/removals the used IPs might be quite fragmented over the whole address space. To effectively check if an IP is free I have to ping and ask the DNS system, which is considered expensive. So I want to minimise these lookups and store some information about used/free addresses in a relational database. This should give me good candidates and I only have to do a few such lookups.
The question is how to do so in a most simple and efficient way that SQL can handle?
One first idea was to generate all possible addresses and store them in the database. Used ones just get the use column set to true. Then just make a SQL query that returns like the first 10 addresses where the use value is false. Looks fairly simple to get a new address. But this approach could become a problem for big networks. Also I have to generate and remove unused addresses for every network change.
If I only store the used addresses I don’t see how to make a query that returns me free addresses. I have to retrieve the whole list and implement the logic on the client. But I was hoping I could off-load that to the database server.
Are there any better alternatives? Scan for free addresses regularly and only store a few ones? Sort the list of used addresses, maybe in a tree like structure where the non-leafs hold some information about free addresses in their child segment? These solutions seem rather complicated to me. I would prefer a simple algorithm without any extra running scanners/generators, and then in the worst case I have to fall back to some other method.
Is there anything SQL can provide me with? Like give me the smallest free element in a sorted list of integers?
Cheers.
8
An effective and efficient solution to this problem will most likely require both a business process and a technical component. The activity has two tasks: a) getting an IP and b) returning an IP.
In order for the solution to provide an IP it has to know which IP addresses are being managed and, of these, which are taken/available. When a previously received IP is no longer used by the user, it must be returned to the set of managed IPs.
You’ll probably have a few challenges:
- which networks (subnets, IP ranges) are being managed?
- how do you create a baseline of which IPs are being used?
- how do you enforce check-out and check-in of addresses?
- how are IPs assigned on the network (static, dynamic)?
Let’s make it a bit more concrete with an example:
Assume we have a subnet, say, 192.168.1.0/24. You might want to reserve the IP 192.168.1.1 for the gateway. The rest could be part of the pool. (Don’t forget to exclude the broadcast address 192.168.1.255 as well.)
Up until now people have just grabbed random addresses and tried it. If it worked they kept it, if not they tried another one. So as of today we have no idea which ones are being used and which ones are free. In order to get our baseline we could do any number of the things you mentioned: pings, DNS lookups, etc. Some systems might not respond to pings (e.g. because of packet filters, Windows firewall, etc), the DNS server might not be current for whatever reason (“it was supposed to be just a quick test, I didn’t need a DNS entry”), and so on. Probably the most reliable way to check if an IP is in use is to do a ping and/or a port scan. A ping is quick, but a port scan is quite slow, but doing both will give you some confidence about the state of the world. You could run this discovery phase for a week or two to account for hosts that are temporarily offline — assuming they’ll pop up in that timeframe. If they don’t, you run the risk of getting a duplicate on the network. If possible, find the old host that’s offline and pull the network connection — leave a note explaining why.
Prior to this scan, let all the IP requestors know what’s going on. Ask them to volunteer which IPs they’re using and start recording stuff. Furthermore, tell them to record any new IPs they check out from that day. Use something like a Google Docs spreadsheet or whatever — it supports simultaneous users so it’d be good for this.
All right, eventually the baseline finishes and now you’ll also have a peer-sourced spreadsheet with some data. Consolidate the two and that’ll be your truth copy. Now your business process kicks in. Any IP that is checked out must be checked in after use. Record whatever information is necessary to effectively manage the check-out and check-in tasks.
One way to phase out the old “junk in the trunk” (i.e. IPs acquired prior to this new process) could be to create static entries in your DHCP server for these. This set would shrink over time as they come off the lease. The rest of the addresses, the free ones, would be in the DHCP pool and could be checked out and checked in as per standard DHCP. If somebody can’t use DHCP for whatever reason, you’d simply create a static entry as with the old addresses. DHCP can also be automatically integrated with DNS so that they’re automatically registered and users would use names and not IPs to reference them. (pfSense does this with the stock DHCP server and DNS server/Unbound DNS.)
You can use any data store (including SQL) to manage the check-out and check-in, but there’s a tradeoff between how much meta-data you need (and the work required) versus how much you get for free from the DHCP/DNS server. But you can certainly write some script/program to automate this, especially on Unix since you’ll likely be modifying text files and calling scripts to reload the changes.