1

I'm very new to writing SQL scripts and I would very much appreciate a sanity check of what I've come up with.

The tables will back an IP Management solution. There are a total of two tables.

AddressRanges

This table is pre-populated with a large amount of IP ranges that are available within the networks in different regions. A range is considered available if the value in the column VirtualNetworkId is NULL.

VirtualNetworks

This table is populated on demand based on calls made to an API. The call includes any number of desired ranges (e.g. 24). A call will fail if we cannot find any available range with the desired size in the table AddressRanges. Once the preconditions are fulfilled, the id of the virtual network will be inserted into the FK column of the AddressRanges table.

Queries

Finding available ranges would by done like this:

SELECT * FROM AddressRanges WHERE VirtualNetworkId IS NULL AND CIDR IN (24) AND Location = 'euw';

Reserving a range would be done like this:

UPDATE AddressRanges SET VirtualNetworkId = 'id-of-virtual-network' WHERE NetworkAddress = 'address-received-from-select-query' AND Location = 'location-of-address';

Freeing a range would be done like this (or should I use CASCADE somehow?)

UPDATE AddressRanges SET VirtualNetworkId = NULL WHERE NetworkAddress = 'address-reserved-by-virtual-network' AND Location = 'location-of-address';

And finally deleting the network entirely:

DELETE FROM VirtualNetworks WHERE SubscriptionId = 'subscriptionId' AND ResourceGroupName = 'resourceGroupName' AND VirtualNetworkName = 'virtualNetworkName';

If a virtual network is deleted, the FK column in the AddressRanges table should be set to null again.

Script

GO
-- A full resource identifier for a virtual network is:
-- subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Network/virtualNetworks/<VirtualNetworName>
CREATE TABLE [dbo].[VirtualNetworks]
(
  -- We use the unique network id as a foreign key
  [VirtualNetworkId] UNIQUEIDENTIFIER UNIQUE NOT NULL,
  [SubscriptionId] UNIQUEIDENTIFIER NOT NULL,
  [VirtualNetworkName] VARCHAR(64) NOT NULL,
  [ResourceGroupName] VARCHAR(64) NOT NULL,
  -- A virtual network is unique based on its name within a resource group
  CONSTRAINT AK_VirtualNetwork UNIQUE(VirtualNetworkName, ResourceGroupName),
  -- The virtual network id is globally unique
  CONSTRAINT PK_VirtualNetwork PRIMARY KEY (VirtualNetworkId)

);

GO
CREATE TABLE [dbo].[AddressRanges]
(
  -- This is null until the ranges is taken by a virtual network
  [VirtualNetworkId] UNIQUEIDENTIFIER,
  -- The first address in this range
  -- This is always the start of the range +4 reserved
  [FirstAddress] VARCHAR NOT NULL,
  -- The last address in this range
  -- This is always the end of the range -1 reserved for broadcast
  [LastAddress] VARCHAR NOT NULL,
  -- Number of available addresses, excluding 5 reserved by Azure
  [Count] INT NOT NULL,
  -- In which region this range is available
  [Location] VARCHAR NOT NULL,
  -- The staring address including CIDR
  [NetworkAddress] VARCHAR NOT NULL,
  -- The CIDR
  [CIDR] INT NOT NULL,
  CONSTRAINT FK_VirtualNetworks_VirtualNetworkId FOREIGN KEY (VirtualNetworkId) REFERENCES dbo.VirtualNetworks (VirtualNetworkId),
  -- An address is always unique within a location
  CONSTRAINT AK_VirtualNetwork UNIQUE(NetworkAddress, [Location])
);

Based on the information given, is this a sensible design? If not, what would you change?

New contributor
jokarl is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

0

Your Answer

jokarl is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.