Gentoo Archives: gentoo-user

From: "J. Roeleveld" <joost@××××××××.org>
To: gentoo-user@l.g.o
Subject: Re: [gentoo-user] SQL Server Advice for Small Business
Date: Tue, 30 Jul 2013 05:52:22
In Reply to: [gentoo-user] SQL Server Advice for Small Business by Randy Westlund
1 On Mon, July 29, 2013 22:22, Randy Westlund wrote:
2 > Hey guys,
3 >
4 > I'm planning to set up an SQL server for my dad's small canvas awning
5 > business, and I've never done this before. Most of my sysadmin-type
6 > skills are self-taught. I could use some advice.
7 >
8 > My dad needs infrastructure to allow ~ 15 of his employees to schedule
9 > appointments, track order status, and analyze random things about job
10 > status and customer base. I intend to set up a PostgreSQL server and
11 > write simple graphical front ends for the employees. I'll do most of the
12 > advanced customer base analysis for him. Eventually, I want to be
13 > generating heat maps of cashflow from cities and telling him where most of
14 > his materials are being used, etc.
16 For that, you could, in time, look into PostGIS (or similar).
18 > Operating system:
19 >
20 > I feel more comfortable on gentoo than anywhere else, so I'd like to put
21 > gentoo on the server. How often should I update packages? How often
22 > should I update the kernel? Any general management advice?
24 Will the server be internet-facing?
25 I would make sure you have a firewall and only open the port needed for
26 the front-end.
27 Don't update the kernel too often, keep an eye out for security fixes and
28 apply where necessary.
29 Keep a seperate machine/VM where you build binary packages. This will
30 significantly reduce the time needed to upgrade the software.
32 > I'm not really familiar with all the RAID options. Which should I be
33 > using? Should it be implemented in hardware or software?
35 That depends on your budget and requirements.
36 For databases, RAID-10 is generally considered the best performance. Also
37 avoid filling the disks and try to use the first half of the disk, rather
38 then the whole. (First half is faster then 2nd half)
39 RAID-10 in software (eg. Linux Software Raid in the kernel) outperforms
40 the cheaper RAID-cards easily. If you have the budget, you could invest in
41 a dedicated hardware raid card (but make sure it is 100% hardware and
42 doesn't use the CPU for the calculations)
44 > I'm also planning on using samba to give everyone a shared directory, but
45 > that should be easy.
47 Depends on how much you want in there. If just a simple share, then it
48 will be simple. If you also want the MS Windows machines to authenticate
49 against it, things get a little more complicated.
51 > Hardware:
52 >
53 > What kind of hardware should I be looking at? One of Dell's PowerEdge
54 > models? How much of the hardware will need to be enterprise grade? I
55 > believe the hard drives will be the most important, right? I installed
56 > one of NASA's servers in Antarctica once, but someone else spec'd the
57 > hardware ($6k PowerEdge) and put ubuntu on it.
59 How mission-critical will this be?
60 For my server (which has become quite critical over the years), I
61 currently use a self-build server with good reliable components.
62 TYAN-mainboard (with built-in iKVM), WD-RED drives, Areca hardware raid-card.
64 When I started running my own server, it was on a cheap no-brand mainboard
65 with simple desktop disks connected via IDE. (yes, ancient :) )
67 > Table structure:
68 >
69 > I'm diving into database design and normalization rules now.
71 You want to try to keep the database design optimized for the usage
72 pattern of the client-tools. Which usually means not too much
73 normalization. That helps with reporting, not when you need to do mostly
74 inserts.
76 > I'll need to
77 > store binary files (pictures of job site, scanned documents), and am
78 > currently planning on base64 encoding them (or something similar) and
79 > storing them in the database to keep it ACID compliant.
81 How big will those documents be?
82 Either, as already mentioned, store them as blobs, or on a (samba) share
83 and put metadata (filepath,name,description,...) in the database.
85 > Any other random advice or good resources would be much appreciated.
87 Advice:
88 1) Backup
89 2) Backup
90 3) Did I mention backup? ;)
92 A tip, when you decide to put the documents on a share, to ensure the
93 backups are in sync, do the following:
94 1) stop access to the database
95 2) snapshot the fileshare (LVM helps here)
96 3) backup the database
97 4) allow access to the database again
98 5) backup the snapshot
99 6) remove the snapshot
101 Total downtime with this should be less then 1 minute. A full backup using
102 the Postgresql tools is really quick.
103 Step 5 can then take as long as it takes. The environment will still be
104 running.
106 Also think about how to store certain types of data (like addresses) and
107 how to enforce data quality rules. Eg. everyone using the same way of
108 writing the names of towns/streets/people.
109 You'd be surprised how often I find various forms of:
110 - Mr, Mister, Sir, Msr, Mstr,... (All supposedly meaning the same ;) )
111 Or, like in the Netherlands, there are cities with multiple names that are
112 officially accepted:
113 - The Hague (as it's known internationally)
114 - Den Haag
115 - s' Gravenhage
116 (yes, those are the same city)
118 My advice, have either a translation table where various forms are entered
119 to allow easy identification. Or pre-fill a table with standard forms and
120 use select-boxes for those in the interface.
122 Let me know if you need any further help with this.
124 Kind regards,
126 Joost Roeleveld


Subject Author
Re: [gentoo-user] SQL Server Advice for Small Business Randy Westlund <rwestlun@×××××.com>
Re: [gentoo-user] SQL Server Advice for Small Business Andrew Lowe <agl@×××××××.au>