Re: Best SQL Server 6.5 schema for RadiusNT?

Dale E. Reed Jr. ( (no email) )
Thu, 12 Mar 1998 14:16:36 -0800

Steve Rich wrote:
>
> Other than upsizing from Access the installation package 'radius7.mdb',
> can anyone give me a good SQL Server 6.5 .sql schema script for the
> RadiusNT database?
>
> The original Radius administrator (who has since left) upsized our
> current db from Access (using the triggers option). Since then, I've
> noticed some things that have me believe that we could (should?) rebuild
> our database using a better schema. Here are some questions:
> 1. Should the schema be based on DRI instead of triggers?

DRI

> 2. Should there be referential constraints (or triggers) on:
> - RadAttributes to RadConfigs (RadAttributeID)
> - AccountTypes to RadATConfigs (AccountType)
> - RadAttributes to RadATConfigs (RadAttributeID)
> - others?

There are lots of relationships between the above tables. In most
cases where you have a field ending in ID, it should be related to
another table, unless its the key in the table.

For example, in RadConfigs, RadAttributeID is related to
RadAttributes.RadAttributeID.

> 3. Can the MasterAccounts and SubAccounts table layout be based,
> alone, on the fields defined in the 'radiusnt.doc'?

Yes.

> That is, I want to split my registration database and apps from
> the RadiusNT database, and use RadiusNT on a
> radius only database/server.

I don't see why you shouldn't be able to do that.

> 4. Where are the definitions for the RadRoamServers and RadRoamDomains
> tables talked about in 'radiusnt.doc'?

Unfortunately, those tables where prematurely defined. They should
not have been mentioned. RadiusNT 2.5 includes support for them and
has full documentation of them.

> We have two tables 'RadRoams' and 'RadRoamServers' that have the
> following column definitions.
> Are these correct?
> RadRoams - "RadRoamID" "int"
> "DomainName" varchar (32)
> "Domain" varchar (32)
> "CostPerMinute" "int"
> RadRoamServers - "RadRoamID" "int"
> "Priority" "int"
> "IPAddress" varchar (16)
> "ServerName" varchar (32)
> "Timeout" "int"
> "Retries" "int"

The roaming support in 2.2 was not official and had many problems.
This layout has changed in 2.5.

> 5. Most important, what are the correct indexes for subaccounts and
> masteraccounts so that RadiusNT can process
> the authentication query without doing table scans? The query,
> taken from 'radius -x15', looks like the following:

As a general rule of thumb, all fields contained in a where clause
should have an index on them:

> Where (sa.Login='user' or sa.Shell='user') AND
> ma.CustomerID=sa.CustomerID and sa.Active<>0 and ma.Active<>0

-- Dale E. Reed Jr.  (daler@iea-software.com)_________________________________________________________________       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |   http://www.iea-software.com