Re: [gentoo-user] SQL Server Advice for Small Business

[J. Roeleveld]

On Mon, July 29, 2013 22:22, Randy Westlund wrote:
> Hey guys,
>
> I'm planning to set up an SQL server for my dad's small canvas awning
> business, and I've never done this before.  Most of my sysadmin-type
> skills are self-taught.  I could use some advice.
>
> My dad needs infrastructure to allow ~ 15 of his employees to schedule
> appointments, track order status, and analyze random things about job
> status and customer base.  I intend to set up a PostgreSQL server and
> write simple graphical front ends for the employees.  I'll do most of the
> advanced customer base analysis for him.  Eventually, I want to be
> generating heat maps of cashflow from cities and telling him where most of
> his materials are being used, etc.

For that, you could, in time, look into PostGIS (or similar).

> Operating system:
>
> I feel more comfortable on gentoo than anywhere else, so I'd like to put
> gentoo on the server.  How often should I update packages?  How often
> should I update the kernel?  Any general management advice?

Will the server be internet-facing?
I would make sure you have a firewall and only open the port needed for
the front-end.
Don't update the kernel too often, keep an eye out for security fixes and
apply where necessary.
Keep a seperate machine/VM where you build binary packages. This will
significantly reduce the time needed to upgrade the software.

> I'm not really familiar with all the RAID options.  Which should I be
> using?  Should it be implemented in hardware or software?

That depends on your budget and requirements.
For databases, RAID-10 is generally considered the best performance. Also
avoid filling the disks and try to use the first half of the disk, rather
then the whole. (First half is faster then 2nd half)
RAID-10 in software (eg. Linux Software Raid in the kernel) outperforms
the cheaper RAID-cards easily. If you have the budget, you could invest in
a dedicated hardware raid card (but make sure it is 100% hardware and
doesn't use the CPU for the calculations)

> I'm also planning on using samba to give everyone a shared directory, but
> that should be easy.

Depends on how much you want in there. If just a simple share, then it
will be simple. If you also want the MS Windows machines to authenticate
against it, things get a little more complicated.

> Hardware:
>
> What kind of hardware should I be looking at?  One of Dell's PowerEdge
> models?  How much of the hardware will need to be enterprise grade?  I
> believe the hard drives will be the most important, right?  I installed
> one of NASA's servers in Antarctica once, but someone else spec'd the
> hardware ($6k PowerEdge) and put ubuntu on it.

How mission-critical will this be?
For my server (which has become quite critical over the years), I
currently use a self-build server with good reliable components.
TYAN-mainboard (with built-in iKVM), WD-RED drives, Areca hardware raid-card.

When I started running my own server, it was on a cheap no-brand mainboard
with simple desktop disks connected via IDE. (yes, ancient :) )

> Table structure:
>
> I'm diving into database design and normalization rules now.

You want to try to keep the database design optimized for the usage
pattern of the client-tools. Which usually means not too much
normalization. That helps with reporting, not when you need to do mostly
inserts.

>  I'll need to
> store binary files (pictures of job site, scanned documents), and am
> currently planning on base64 encoding them (or something similar) and
> storing them in the database to keep it ACID compliant.

How big will those documents be?
Either, as already mentioned, store them as blobs, or on a (samba) share
and put metadata (filepath,name,description,...) in the database.

> Any other random advice or good resources would be much appreciated.

Advice:
1) Backup
2) Backup
3) Did I mention backup? ;)

A tip, when you decide to put the documents on a share, to ensure the
backups are in sync, do the following:
1) stop access to the database
2) snapshot the fileshare (LVM helps here)
3) backup the database
4) allow access to the database again
5) backup the snapshot
6) remove the snapshot

Total downtime with this should be less then 1 minute. A full backup using
the Postgresql tools is really quick.
Step 5 can then take as long as it takes. The environment will still be
running.

Also think about how to store certain types of data (like addresses) and
how to enforce data quality rules. Eg. everyone using the same way of
writing the names of towns/streets/people.
You'd be surprised how often I find various forms of:
- Mr, Mister, Sir, Msr, Mstr,... (All supposedly meaning the same ;) )
Or, like in the Netherlands, there are cities with multiple names that are
officially accepted:
- The Hague (as it's known internationally)
- Den Haag
- s' Gravenhage
(yes, those are the same city)

My advice, have either a translation table where various forms are entered
to allow easy identification. Or pre-fill a table with standard forms and
use select-boxes for those in the interface.

Let me know if you need any further help with this.

Kind regards,

Joost Roeleveld