Gentoo Archives: gentoo-user

From: Randy Westlund <rwestlun@×××××.com>
To: gentoo-user@l.g.o
Subject: Re: [gentoo-user] SQL Server Advice for Small Business
Date: Tue, 30 Jul 2013 21:34:13
Message-Id: 20130730213403.GC29567@artifex
In Reply to: Re: [gentoo-user] SQL Server Advice for Small Business by "J. Roeleveld"
1 On Tue, Jul 30, 2013 at 07:52:11AM +0200, J. Roeleveld wrote:
2 >
3 > For that, you could, in time, look into PostGIS (or similar).
4 >
5
6 Interesting, I'll keep that in the back of my mind.
7
8 > Will the server be internet-facing?
9 > I would make sure you have a firewall and only open the port needed for
10 > the front-end.
11 > Don't update the kernel too often, keep an eye out for security fixes and
12 > apply where necessary.
13 > Keep a seperate machine/VM where you build binary packages. This will
14 > significantly reduce the time needed to upgrade the software.
15 >
16
17 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.
18
19 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.
20
21 >
22 > That depends on your budget and requirements.
23 > For databases, RAID-10 is generally considered the best performance. Also
24 > avoid filling the disks and try to use the first half of the disk, rather
25 > then the whole. (First half is faster then 2nd half)
26 > RAID-10 in software (eg. Linux Software Raid in the kernel) outperforms
27 > the cheaper RAID-cards easily. If you have the budget, you could invest in
28 > a dedicated hardware raid card (but make sure it is 100% hardware and
29 > doesn't use the CPU for the calculations)
30 >
31
32 Okay, RAID-10 sounds good. Thanks for the tip about the first half of the drives.
33
34 >
35 > Depends on how much you want in there. If just a simple share, then it
36 > will be simple. If you also want the MS Windows machines to authenticate
37 > against it, things get a little more complicated.
38 >
39
40 Should just be a simple share, I don't think I'll need any authentication.
41
42 >
43 > How mission-critical will this be?
44 > For my server (which has become quite critical over the years), I
45 > currently use a self-build server with good reliable components.
46 > TYAN-mainboard (with built-in iKVM), WD-RED drives, Areca hardware raid-card.
47 >
48 > When I started running my own server, it was on a cheap no-brand mainboard
49 > with simple desktop disks connected via IDE. (yes, ancient :) )
50 >
51
52 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?
53
54 >
55 > You want to try to keep the database design optimized for the usage
56 > pattern of the client-tools. Which usually means not too much
57 > normalization. That helps with reporting, not when you need to do mostly
58 > inserts.
59 >
60
61 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?
62
63 >
64 > How big will those documents be?
65 > Either, as already mentioned, store them as blobs, or on a (samba) share
66 > and put metadata (filepath,name,description,...) in the database.
67 >
68
69 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.
70
71 >
72 > Advice:
73 > 1) Backup
74 > 2) Backup
75 > 3) Did I mention backup? ;)
76 >
77 > A tip, when you decide to put the documents on a share, to ensure the
78 > backups are in sync, do the following:
79 > 1) stop access to the database
80 > 2) snapshot the fileshare (LVM helps here)
81 > 3) backup the database
82 > 4) allow access to the database again
83 > 5) backup the snapshot
84 > 6) remove the snapshot
85 >
86 > Total downtime with this should be less then 1 minute. A full backup using
87 > the Postgresql tools is really quick.
88 > Step 5 can then take as long as it takes. The environment will still be
89 > running.
90 >
91
92 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.
93
94 Thanks for your help.
95
96 Randy

Replies

Subject Author
Re: [gentoo-user] SQL Server Advice for Small Business "J. Roeleveld" <joost@××××××××.org>
Re: [gentoo-user] SQL Server Advice for Small Business Michael Hampicke <mh@××××.biz>