[gentoo-user] SQL Server Advice for Small Business

[gentoo-user] SQL Server Advice for Small Business

[Randy Westlund]

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.


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?

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

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


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.


Table structure:

I'm diving into database design and normalization rules now.  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.


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

Randy

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

[Mike Gilbert]

On Mon, Jul 29, 2013 at 4:22 PM, Randy Westlund <rwestlun@gmail.com> 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.
>

Heh, based on the capitalized subject line I was going to ask why you
were asking about Microsoft SQL Server on this list. ;-)

> Table structure:
>
> I'm diving into database design and normalization rules now.  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.

If you really want to store binary data in the database, use a binary
(BLOB) data type. Base64 will increase their size significantly.

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

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

[Randy Westlund]

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

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

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

[Michael Hampicke]

[-- Attachment #1: Type: text/plain, Size: 450 bytes --]

Am 30.07.2013 23:34, schrieb Randy Westlund:
> 
> 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.
> 

Depends on your needs. Can you afford to lose one workday of data? If
no, make backups more often.

15 people x 8 hours = 120 hours of work per day, that translates into
money :-)


[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 490 bytes --]

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

[Andrew Lowe]

> 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.
[snip]

Randy,
	I've read your original post and subsequently the answers. One question 
that nobody raised about your original post was why are you writing 
something for your fathers company in the first place? Why aren't you 
looking at ERP packages such as Compiere, Adempiere, Tryton, OpenERP etc 
etc? In other words, why are you reinventing the wheel? With these 
systems, you will get all of the data entry stuff already set up and you 
can then do your data analysis stuff, although these systems also do 
heaps of this stuff already. You don't mention what you do for a crust, 
but if you do a good implementation of one at your Dad's place, you 
could sell your services to other companies.

	Someone mentioned PostGIS - forget it. You want to generate heat maps 
based upon where business is going. This does not need a GIS. If you are 
generating "real", accurate maps, then a GIS would be what you want, but 
in this case, you just need a rough "mudmap" of the areas in question. 
This would just show that City A is north west, ie the top left side of 
the page, from you, which is in the centre, company B is east, the right 
hand side of the page etc and based upon this, generate your heat map. 
Scale, true orientation and position are not important. Even just grab a 
Google Earth screen grab of your area and then write something what will 
add heat, colours, to it in the appropriate places.

	You mentioned a "small database". Don't underestimate how big things 
can get quickly. If, at the moment when someone is spec'ing a job, and 
they take photo's, but subsequently those photo's are hard to access, 
they won't take the photo's in the first place. If you set up an easy to 
access repository for the photo's, people will start taking more 
photo's. If there are CAD drawings, what are they? 2D/3D, 
AutoCAD/MicroStation or full on Solidworks solid models? These get big 
very quickly. Scanned notes etc just add more and more. I have a feeling 
that your small db could get big quickly. Just plan for that.

	In closing, these are just my five cents worth, we no longer have two 
cent pieces in Australia, regarding the software. I have no idea on the 
hardware except as someone mentioned back up, back up and back up. Oh, 
also, the more RAM the merrier.

	Good luck,
		Andrew

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

[microcai]

2013/7/30 Randy Westlund <rwestlun@gmail.com>:
> 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.

spot a girl ....

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 457 bytes --]

On Tuesday 30 Jul 2013 11:31:05 microcai wrote:
> 2013/7/30 Randy Westlund <rwestlun@gmail.com>:
> > 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.
> 
> spot a girl ....

How old are you, sonny and how does your comment address the question of the 
OP?

-- 
Regards,
Mick

[-- Attachment #2: This is a digitally signed message part. --]
[-- Type: application/pgp-signature, Size: 490 bytes --]

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

[Bruce Hill]

On Tue, Jul 30, 2013 at 02:35:21PM +0100, Mick wrote:
> > 
> > spot a girl ....
> 
> How old are you, sonny and how does your comment address the question of the 
> OP?

He's Chinese in his early 20s...
-- 
Happy Penguin Computers               >')
126 Fenco Drive                       ( \
Tupelo, MS 38801                       ^^
support@happypenguincomputers.com
662-269-2706 662-205-6424
http://happypenguincomputers.com/

A: Because it messes up the order in which people normally read text.                                                                                                                                                          
Q: Why is top-posting such a bad thing?                                                                                                                                                                                        
A: Top-posting.                                                                                                                                                                                                                
Q: What is the most annoying thing in e-mail?

Don't top-post: http://en.wikipedia.org/wiki/Top_post#Top-posting