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

[J. Roeleveld]

On Tue, July 30, 2013 23:34, Randy Westlund wrote:
> On Tue, Jul 30, 2013 at 07:52:11AM +0200, J. Roeleveld wrote:
>>
>> 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.
>>
>
> No, it'll be LAN only.  I'll filter out external connections.  There's no
> wireless network and no adjacent businesses, so I'm not worrying too much
> about security.  The only thing I'll need from the outside is SSH.

In that case, make sure it runs stable and take time to test new versions.

> So your recommendation is to have a VM on the server with the same
> packages installed, compile things there, then move the binary package to
> the real server.  I might set this up at some point, but I think I'll be
> okay with updating things in place, so long as I do it at night.

I wouldn't put the VM on the server itself, but instead on your
desktop/laptop.
That way you also have a development environment where you can test new
features and fix the inevitable bugs.
The binary packages from there can then be moved to the server when you
are ready to update.
I always stop applications when I update them. To minimize downtime, I
always ensure I have binary packages available.

>> 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)
>>
>
> Okay, RAID-10 sounds good.  Thanks for the tip about the first half of the
> drives.

I got that from a book about Postgresql performance tuning :)
The start is quite generic on how to test and optimize performance on
hardware and OS level.

>> 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.
>>
>
> Should just be a simple share, I don't think I'll need any authentication.

I would still put in authentication. MS Windows can be set to save the
password. That way, you can also set up personal homedirectories and
enable tracing to see who does what.

>> 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 :) )
>>
>
> The server will be pretty important.  If all goes according to plan, every
> employee that uses a computer (~15) will be interacting with it throughout
> the day.  The goal is to replace paper records.  Aside from the hard
> drives, are there any other components that are especially important for
> databases?

Yes, memory. Databases are happy with lots and lots of memory for caching.
Other then that, most components should work, but go for stability. Ignore
boards that are designed for gaming/overclocking. Those are not generally
designed for 24/7 usage over a period of several years.
One of my mainboards is still 100% stable. Never had a crash. Only reason
I stopped using it is because it only holds 4GB of memory.

Tyan mainboards are, in my experience, rock-solid. Others on the list will
probably have their own preferences as well.
You can also go for a pre-build server from the likes of DELL, HP,
Supermicro,... Those generally use good quality hardware as well. And they
often come with (optional) onsite warranty.

>> 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.
>>
>
> From what I've read so far, it sounded like everything should be
> normalized as much as possible even if there's a slight performance hit
> because it makes the system easier to modify and expand later.  In my
> prototype, I have it divided into as many tables as possible, and each
> SELECT has mutiple joins.  Is this a bad idea?

JOINs are heavy for a database. Normalizing a database is nice, but I
don't see that often on transactional systems. (Like what you are planning
on making)

Modifying tables don't take much either, simply do an ALTER TABLE to
add/expand fields. (Do NOT reduce the size, or you will LOOSE data) and if
necessary fill the fields for existing records with default values.

I would suggest not to overnormalize the database. Start by seperating the
different pieces of information into parts like:
- customers
- orders
- order details (a line on the order)
- products
- invoices
- documents/files

and other parts that are of interest.
Then see if you can fit all that into seperate tables.

>> 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.
>>
>
> I'm expecting job orders to have at most a few images of the job site,
> blueprints, random things the customer/contractor emailed us, and a few
> scanned sheets of handwritten notes.  Storing them outside the database
> sounds like asking for trouble.  Binary blobs sounds good.

It's a bit more difficult to organize, if the application itself handles
the storing of the files and updating the metadata table, then that will
go fine. Look at some of the DMS-systems to see how that is being handled
there.

>> 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.
>>
>
> How often should a small database like this be backed up?  Once a day?
> Twice a day?  I'm thinking that I should backup to another machine on the
> network, then copy that to at least one off-side machine.

That depends simply on the following:
- How long a period of data can you afford to loose?

I have a daily backup, but that is because I can afford to loose 1 day of
data.

Ensure you can always restore the backups. If you have a machine where you
develop new versions, use that also to restore the backups.
That way you also have a backup machine handy where the employees can
continue working.

> Thanks for your help.

You're welcome.

--
Joost