From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from lists.gentoo.org (pigeon.gentoo.org [208.92.234.80]) by finch.gentoo.org (Postfix) with ESMTP id 936831381F3 for ; Tue, 30 Jul 2013 21:34:13 +0000 (UTC) Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id A6942E0A6C; Tue, 30 Jul 2013 21:34:08 +0000 (UTC) Received: from mail-qe0-f54.google.com (mail-qe0-f54.google.com [209.85.128.54]) (using TLSv1 with cipher ECDHE-RSA-RC4-SHA (128/128 bits)) (No client certificate requested) by pigeon.gentoo.org (Postfix) with ESMTPS id 929D6E09F8 for ; Tue, 30 Jul 2013 21:34:07 +0000 (UTC) Received: by mail-qe0-f54.google.com with SMTP id 1so3447082qee.27 for ; Tue, 30 Jul 2013 14:34:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=date:from:to:subject:message-id:references:mime-version :content-type:content-disposition:in-reply-to:user-agent; bh=NAD7v2AGd5tpcXF16Wkz8sKX+iy4/+74qY/Ok5jfIJE=; b=w89wVffUsGOBJS9rxxry3Ij4ksL1onT8NLQ/ZvXv72YCXrOODnrYsh0BEuUMYuuA9i R+MWZc7AvwqbhZ3UdqAZWlHFMPuW1f//OcgT6V2VuN+PpmgZuqsydNKN36IdXArpGxnU e5PGu4MEQSibSVf5aUSBBHGG+NIXSgjGF4M3VTYC+SZfP/yuo+mPprOSYQFpKL0fU1wc MTLv45REKqhD8ahmie7HZBO8+EBVtHLUqzOhtEdUYjV2bzmd5OMQKAKS6qGcw+K0kCSZ wCTlAx4SBaVYpkX7lQEIAXwsKIVWJAWKBasKXZic3Ach6dq/UdrwWIdAj2mlRjmhhiFk IsSA== X-Received: by 10.49.47.50 with SMTP id a18mr45117149qen.61.1375220046822; Tue, 30 Jul 2013 14:34:06 -0700 (PDT) Received: from smtp.gmail.com:587 (c-98-216-247-110.hsd1.ma.comcast.net. [98.216.247.110]) by mx.google.com with ESMTPSA id lk8sm23078701qeb.3.2013.07.30.14.34.04 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 30 Jul 2013 14:34:06 -0700 (PDT) Received: by smtp.gmail.com:587 (sSMTP sendmail emulation); Tue, 30 Jul 2013 17:34:03 -0400 Date: Tue, 30 Jul 2013 17:34:03 -0400 From: Randy Westlund To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] SQL Server Advice for Small Business Message-ID: <20130730213403.GC29567@artifex> References: <20130729202238.GA23113@artifex> Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@lists.gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.21 (2010-09-15) X-Archives-Salt: 09fb62ad-acdb-448b-988e-3e38666b0528 X-Archives-Hash: 0e69be4ada97f43a630a67c912b697b8 On Tue, Jul 30, 2013 at 07:52:11AM +0200, J. Roeleveld wrote: > > For that, you could, in time, look into PostGIS (or similar). > Interesting, I'll keep that in the back of my mind. > 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. 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. > > 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. > > 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. > > 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? > > 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? > > 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. > > 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. Thanks for your help. Randy