Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Ask Slashdot: Low Cost Way To Maximize SQL Server Uptime?

samzenpus posted more than 2 years ago | from the bang-for-your-buck dept.

Software 284

jdray writes "My wife and I own a mid-sized restaurant with a couple of Point of Sale (POS) terminals. The software, which runs on Windows and .NET, uses SQL Server on the back end. With an upgrade to the next major release of the software imminent, I'm considering upgrading the infrastructure it runs on to better ensure uptime (we're open seven days a week). We can't afford several thousand dollars' worth of server infrastructure (two cluster nodes and some shared storage, or some such), so I thought I'd ask Slashdot for some suggestions on enabling maximum uptime. I considered a single server node running VMWare with a limp-mode failover to a VMWare instance on a desktop, but I'm not sure how to set up a monitoring infrastructure to automate that, and manual failover isn't much of an option with non-tech staff. What suggestions do you have?"

cancel ×

284 comments

Sorry! There are no comments related to the filter you selected.

Kill all your users (-1)

Anonymous Coward | more than 2 years ago | (#40442039)

Kill all your users. Then you won't have to worry about any uptime.

Obvious solution (-1)

Anonymous Coward | more than 2 years ago | (#40442043)

Dump SQL Server for MySQL, or even SQL Lite.

Re:Obvious solution (5, Funny)

Anonymous Coward | more than 2 years ago | (#40442361)

MySQL is not web scale. He should use MondoDB. That is web scale.

Re:Obvious solution (0, Insightful)

Anonymous Coward | more than 2 years ago | (#40442853)

LOL
MySQL is shit compared to SQL Server

If it a'int broke... (1)

Anonymous Coward | more than 2 years ago | (#40442061)

...don't upgrade.

Re:If it a'int broke... (2)

sureshot007 (1406703) | more than 2 years ago | (#40442117)

While I always agree with this, you can't expect a business that is open 7 days a week to just wait for it to fail.

Re:If it a'int broke... (1)

Lennie (16154) | more than 2 years ago | (#40442165)

I assume a Point of Sale system is mostly a closed system and network right ? Why would it break ?

Re:If it a'int broke... (1)

sureshot007 (1406703) | more than 2 years ago | (#40442225)

Because it runs software (that could crash) on hardware (that will fail at some point).

Everything breaks at some point. If your business depends on it, why wait until it actually breaks.

Re:If it a'int broke... (1)

sapgau (413511) | more than 2 years ago | (#40442615)

It can also break on the upgrade... so is not an easy answer.

Re:If it a'int broke... (4, Funny)

edittard (805475) | more than 2 years ago | (#40443217)

WTF? Software isn't subject to physical wear like an engine. Do you think friction will eventually turn a 1 into a 0 somewhere in the code?

Re:If it a'int broke... (0)

Anonymous Coward | more than 2 years ago | (#40443355)

in my world this is possible... multiple NAND flash reads can (and do) cause adjacent bits to flip! so we periodically pick em up and write em out again.

Re:If it a'int broke... (1)

CSMoran (1577071) | more than 2 years ago | (#40442237)

Roger Moore's law.

Re:If it a'int broke... (3, Funny)

Anonymous Coward | more than 2 years ago | (#40442509)

Roger Moore's law.

What does James Bond have to do with this?

Re:If it a'int broke... (0)

Anonymous Coward | more than 2 years ago | (#40442797)

He meant, "Roger" as in "10-4". So your question should be "What does Moore's Law have to do with this?

Re:If it a'int broke... (1)

TVDinner (1067340) | more than 2 years ago | (#40442941)

If it ain't stirred, it must be shaken

Re:If it a'int broke... (4, Informative)

sapgau (413511) | more than 2 years ago | (#40442767)

Agreed but. How old is the hardware?
If it's over 5 years then you should consider monitoring the following components that are exposed to physical degradation:
- Hard drive
- Cooling fans
- Power Supply
- Network equipment if your electrical current is flaky.

Your uptime might be more impacted by the level of maintenance you do on your equipment. I'm afraid to ask but if in any way your equipment is close to kitchen smoke then you could experience nasty greasy deposits in fans and air vents.

Your next step is have a disaster recovery at the ready with your most recent VM image backup.

Re:If it a'int broke... (1)

sapgau (413511) | more than 2 years ago | (#40442811)

Oh and... check free disk space!

What's your actual problem? (5, Insightful)

royallthefourth (1564389) | more than 2 years ago | (#40442063)

Why don't you have good uptime to begin with? I've SQL Server 2005 on a single unimpressive physical server with months of uptime. Is your restaurant open 24 hours? Is your current server flaking out? Concerns about uptime itself might be misplaced. What isn't made clear in the OP is why you think you need better uptime.

Re:What's your actual problem? (5, Informative)

Anonymous Coward | more than 2 years ago | (#40442289)

In addition to what the parent post said, recent editions of MS SQL Server have really nice mirroring capabilities in built in to the standard edition and you don't have to purchase a license for the "mirror" server. Even better, if you have enterprise edition, your .NET app can automatically fail-over to the mirror server. No shared disks, no windows clustering. It's pretty darn simple.

Re:What's your actual problem? (4, Informative)

spongman (182339) | more than 2 years ago | (#40443001)

if you have enterprise edition, your .NET app can automatically fail-over to the mirror server.

Actually, the Standard Edition will do this you just need to set up the connection strings to point to both servers.

The Enterprise Edition will do asynchronous mirroring, but it's not worth paying extra for.

Re:What's your actual problem? (1)

Anonymous Coward | more than 2 years ago | (#40442299)

Parent is completely correct. The question you need to ask yourself is not, "Do I need to upgrade?".

Here are the questions you should be asking:

What is the required uptime? Do I need 99.9999% or is 98% or even 90% acceptable?
What is are risks if the server goes down unexpectedly during a Saturday evening service (or other busy time)?
How long can you accept the server being down in said service? 15 minutes? 30 minutes? How long can your POS terminals cache information before it needs to be flushed to the database?
Have you had (to date) any significant problems with your SQL engine?

I know VM is the flavour of the month, but waving magic VMWare and creating virtual servers all over the place won't actually solve stability problems. If your system is memory bound, it will continue to be memory bound after installing VM (if not much more due to VM overhead). If your system is network constrained then it will continue to be networked constrained. If you system is processor bound, the system will continue to be processor bound after installing VMWare (if not much more because of VM overhead).

I can take a fairly standard desktop, create 4 virtual servers on it with SQL 2005, setup transaction log shipping, mirroring or clustering for high availablity but it won't do me any good if my power supply blows or the HDD motor burns out.

Re:What's your actual problem? (3, Interesting)

markus_baertschi (259069) | more than 2 years ago | (#40442401)

Good uptime is great, but unfortunately very expensive in terms of hardware, software and manpower. Questions you should ask yourself: - What is the maximum allowable downtime duration ? - How many outages can you tolerate per year ? - What is actual cost to you of a one day/evening outage ? - How many such outages did you have with your actual infrastructure ? I think the best option in your case is to have two identical servers/PCs of good quality with two mirrored harddrives each in hot-swap slots. If a harddrive fails, you can carry on for the evening and replace it the next day. If something else fails you swap the SQL server drives into the second server/PCs and fix the problem later. This is simple enough that you can instruct someone by phone to do that, when you are absent yourself.

Re:What's your actual problem? (1, Insightful)

Anonymous Coward | more than 2 years ago | (#40442407)

I think this may be a smart guy who is bored with the restaurant and is tinkering with the tech. Nothing wrong with that.

Re:What's your actual problem? (3, Insightful)

Bengie (1121981) | more than 2 years ago | (#40442629)

In his shoes, I would be put more effort into quick recovery than really high up-time features like fail-over. While similar, they're not the same, and recovery is much simpler to handle. This is how I plan to handle my own hobbyist network/servers.

I guess I'm saying I would rather spend money/effort on cheap/free virtualization(HyperV3 is coming out this fall-ish) to allow a quick recovery boot from another machine, than spending money on redundant hardware and real-time failover.

Probably a server and a desktop. The desktop could load the image and start his SQL image just fine. Not optimal, but would work while waiting for his server to get fixed.

Re:What's your actual problem? (1)

Anonymous Coward | more than 2 years ago | (#40442977)

Given that it's in a restaurant, dust, grease, water, and corrosion are the actual problems, unless his restaurant is substantially different than the hundred or so restaurants that I've done IT work in.

Re:What's your actual problem? (1)

Bigby (659157) | more than 2 years ago | (#40443163)

It sounds like they need to upgrade the software because of a license, contract, or support reason. Since there is a major change, it could make things more stable or less stable. The posting wants to make sure he gets the best dollar out of the upcoming refresh.

Not to get buzzwordy, but Azure...the Cloud! (1, Interesting)

Anonymous Coward | more than 2 years ago | (#40442093)

Seriously put the SQL server up in Clound service and let them worry about it. If its a Microsoft SQL server then Azure is the place to be. Hell put a full instance of your service up in the cloud.

Re:Not to get buzzwordy, but Azure...the Cloud! (4, Insightful)

Lennie (16154) | more than 2 years ago | (#40442191)

Azure had multiple 24 hour outages.

It's cloud so that seems like a great idea !

Re:Not to get buzzwordy, but Azure...the Cloud! (1)

Anonymous Coward | more than 2 years ago | (#40442423)

so has amazon...many times more.

Re:Not to get buzzwordy, but Azure...the Cloud! (4, Funny)

gmuslera (3436) | more than 2 years ago | (#40443227)

I don't understand why people complain so much about that service. 9.99999% of uptime should be good.

Re:Not to get buzzwordy, but Azure...the Cloud! (4, Insightful)

raydobbs (99133) | more than 2 years ago | (#40442197)

Sounds like an awesome idea - that way, you are TOTALLY FSCKED when your internet connection goes down. One thing if your online ordering for a business goes down - just take orders by phone. When it completed hoses your order taking system (offline and online), then your screwed.

Re:Not to get buzzwordy, but Azure...the Cloud! (0)

jellomizer (103300) | more than 2 years ago | (#40442281)

It sounds like the cloud may be good option.

You are already using Microsoft Products so it doesn't sound like you big into the ideals of RMS's rants against everything. The cloud is an affordable option, Your risks of loosing your data you can mitigate by downloading locally nightly.

Now the advertising for the cloud makes it seem like a magic bullet, it isn't, however if you are a small business you are usually better off running of the cloud then on your own infrastructure, especially if you cannot afford redundancies.

Check your contract to see if you can get your data off. Backup to you existing equipment or back up to the cloud.

Re:Not to get buzzwordy, but Azure...the Cloud! (4, Insightful)

gl4ss (559668) | more than 2 years ago | (#40442919)

sounds like a fucking stupid idea, the entire operation is local and can run easily on computers which can be bought 200 dollars a pop. two of them, set up mirroring and go - even azures pricing sounds stupid compared to that.

the cloud is a stupid idea because a) azure can go down and b) their link to azure can go down. in both cases they're screwed.

what they could need/want would be a totally separate backup, if they're american then maybe square - if they're european wtf does a restaurant need a complex POS system for when you can get self contained payment terminals that take chip cards and connect wirelessly(or cache those which don't need instant verfication) on the pennies? (ok, those don't take care of your actual orders and such, if the restaurant system is really fancy)

SQL 2012 (4, Informative)

g0es (614709) | more than 2 years ago | (#40442095)

You may want to look at what they are doing with avalibility groups. You can avoid the shared storage with avaliblity groups and could cut your hardware costs a bit. assuming you your software support SQL 2012. Link http://msdn.microsoft.com/en-us/library/ff877884.aspx [microsoft.com]

Re:SQL 2012 (-1)

Anonymous Coward | more than 2 years ago | (#40442667)

You may want to look at what they are doing with avalibility groups. You can avoid the shared storage with avaliblity groups and could cut your hardware costs a bit. assuming you your software support SQL 2012. Link http://msdn.microsoft.com/en-us/library/ff877884.aspx [microsoft.com]

You may want to look at what Microsoft is doing to lock you in with availability groups.. You can avoid industry standard practices, cut your hardware costs, and increase the fuck out of your licensing costs--assuming you throw-down a dump truck full of cash to a developer to re-write your app to take advantage of the new lock-in capabilities of SQL 2012.

Re:SQL 2012 (1)

Major Blud (789630) | more than 2 years ago | (#40442849)

Exactly. FWIW, he could also do asynchronous DB Mirroring with SQL 2008, and have pretty good fail-over if his POS software supports the necessary connection string options. I'm having a hard time understanding the option with VMWare. Is he talking about having two nodes with bare-bones ESXi (and no VMotion)? He's still going to need shared storage to bring the SQL guest up on the other node even without VMotion). But seriously, is he really having problems with uptime? I've seen single-node SQL Servers that have been up for a year or more without a single reboot....and that was even in the SQL Server 2000 days. A decent mid-range server with a few redundant systems (fans, drives, PSU, etc) should suffice.

Failover clustering (1)

Anonymous Coward | more than 2 years ago | (#40442101)

You are probably looking at failover clustering with Node and File share majority here (http://technet.microsoft.com/en-us/library/bb676490(EXCHG.80).aspx).

If you smb file share is not reliable, you should be using transaction log shipping, it's the cheap man's failover (http://msdn.microsoft.com/en-us/library/ms191233.aspx).

Linux and MySQL - DUH!!!! (0)

Anonymous Coward | more than 2 years ago | (#40442105)

Well I suggest immediately switching to Linux and MySQL - duh!

Advice. (2)

telix5000 (1298169) | more than 2 years ago | (#40442109)

You are missing some critical details about this. How many transactions with this database are going per minute/hour/day? If this is a fairly basic SQL instance, I don't see the point of your fail over scenario. Simply create some jobs to run backups every few hour or half-hour (storing transaction logs and such) and roll that over to the "desktop" on a share or something. Obviously money is the issue for you, so don't make it so complicated you can't afford someone else to come in and fix things if you get stuck during production.

Go to the cloud! (1, Informative)

Anonymous Coward | more than 2 years ago | (#40442113)

have you considered alternatives like:
- Amazon S3 (virtual)
- Azure Cloud (virtual or sql-node upto 50gb)
- Google App..something

They all, claim, to have 99.99% uptime or more.

Re:Go to the cloud! (1)

LordGr8one (1174233) | more than 2 years ago | (#40442581)

Outsourcing these kinds of things may also reduce your PCI Compliance profile, which would cut down on both your breach risk and compliance costs.

use cloud (0)

Anonymous Coward | more than 2 years ago | (#40442121)

if you wish to minimise infra cost + maintenance then EC2 + RDS might be a good option for you. If you have existed MSFT solution then migration to Azure platform is another option. I would prefer to go with EC2 but this is personal pereferenxe.

You are asking the wrong question (1, Insightful)

xavdeman (946931) | more than 2 years ago | (#40442129)

"I thought I'd ask Slashdot for some suggestions on enabling maximum uptime" - the answer: YOU don't enable maximum uptime, you get someone to do do it for you.
It seems to me like you are ill-equiped to handle server hosting. If you're asking these kinds of questions it may honestly be wiser to just let a third party handle this task for you, so you can handle your main job: serving customers. How about you host your SQL database at one of the many hosting providers willing to tackle this for you? I bet a simple Google query would result in thousands of hits. But as seems to be the trend with Ask Slashdot lately: you probably didn't want to hear this answer.
http://bit.ly/LaHOeu [bit.ly] (I know how Slashdot frowns upon URL shortners, but I assure you, the person asking the question definitely needs to learn that Slashdot isn't a damn search engine).

Re:You are asking the wrong question (0)

Anonymous Coward | more than 2 years ago | (#40442157)

man, you are grumpy

Re:You are asking the wrong question (1)

sapgau (413511) | more than 2 years ago | (#40442867)

And stay off his lawn, dammit!!

Re:You are asking the wrong question (5, Insightful)

Anonymous Coward | more than 2 years ago | (#40442257)

Man, why is every askSlashdot these days full of people who don't think anyone can do their own backend infrastructure (or anything else, for that matter) unless that's their only job? Look, this guy has obviously been running his POS system for some time, already. Just because he isn't a whiz at SQL Server failover doesn't mean he should just throw up his hands and hire a contractor.

Have you completely given up on learning new things and exploring your options, or do you just advocate that other people do so?

Re:You are asking the wrong question (0)

Anonymous Coward | more than 2 years ago | (#40442393)

This is the overall attitude where I work, it's a bit nauseating.

Re:You are asking the wrong question (2)

fmaresca (739871) | more than 2 years ago | (#40442745)

so +1

Re:You are asking the wrong question (0)

Anonymous Coward | more than 2 years ago | (#40442829)

There is something to be said for concentrating on what your business is. CAN he do it himself? Yes, probably. the agrument is SHOULD he. My vote, being a manager of small business computer systems, is no. Concentrate on your business and bringing more customers in; making better food; or, even marketing.

NOW, if he has all of that taken care of, I say go for it! Get a box with sufficient memory and redundant disk capability and run 2 VM's setup for clustering. A reasonably competent person can handle this using M$.

Re:You are asking the wrong question (2)

hazem (472289) | more than 2 years ago | (#40443023)

When running a business, you really have to draw lines between what you can/can't do, and what you want to/don't want to do. You also have to factor in what the cost will be if you mess it up while trying to learn.

If I were currently running a business, I would hire an accountant do handle my taxes. It's not that I couldn't figure it out, but it's something I don't care to figure out. I'd rather focus on what my business is. But then again, if I did it myself and screw it up, who badly could it hurt my business?

Here's a guy who knows some about running SQL Server and POS. But he has a business that's running 7 days a week that probably won't run if the SQL/POS is not operating. So what's the cost to the business if the POS doesn't work for a day or two because he hosed it up?

In this case, the risk is high of a failure (because he's in learning mode). He needs to balance the cost of failure with what he gains by learning to do it himself. If the cost x risk is too great, then yes, I advocate he hire someone to do it. If he can tolerate the cost x risk, then by all means, I encourage him to go for it.

If it were your business and you had a guy handling the mission-critical SQL/POS and you needed to add fail-over but your guy doesn't know how. Would you advocate him doing it on your production system? That's the situation this guy is in, except he's also the guy doing the SQL/POS work.

A key part of being successful in business is knowing what you can do well and what you're better of hiring an expert to do it for you. Good things to consider are whether the work is critical to operations and if it's part of your core business.

Re:You are asking the wrong question (1)

Anonymous Coward | more than 2 years ago | (#40442861)

No, you're answering the wrong question. You mention a trend on Slashdot of people not wanting to hear the answers...its possibly because roughly 75.3% of responses are answering questions entirely different than what was asked.

Just like hearing yourself type?

True HA costs money (1)

Anonymous Coward | more than 2 years ago | (#40442139)

"a mid-sized restaurant ... We can't afford several thousand dollars ... enabling maximum uptime ... with non-tech staff."

You don't need HA. You need a pencil and paper.

Re:True HA costs money (2, Insightful)

Anonymous Coward | more than 2 years ago | (#40442461)

Agreed.

I'm highly familiar with the POS system in US McDonald's restaurants. I'm sure they have more volume than you do.

If the server goes down, the registers can limp along on their own but it isn't pretty, and there's no high availability anything going on. Depending on what fails, the KVS might or might not work.

(For the uninitiated, KVS = Kitchen Video System. Those are the screens that tell the kitchen what food to make. Most busy restaurants - not just fast food - have them these days.)

What you need is for the staff to have some pieces of paper with the menu items and prices printed on them, so they can check stuff off and add it up. You might round prices down to the dollar to make the math easier. If you round up the customers will complain.

PS - If it ain't broke.... Getting the latest and greatest doesn't necessarily get you anything except the effort of getting stuff to work again.

Re:True HA costs money (1)

avandesande (143899) | more than 2 years ago | (#40443289)

Please mod- this was my initial thought

Which POS software? (0)

Anonymous Coward | more than 2 years ago | (#40442153)

Just checking as the offline modes of some allow for continuity of business if the server goes offline making it not as big an issue requiring failover, clustering, etc. Micros, Aloha, Focus?

You're an Idiot (1)

Anonymous Coward | more than 2 years ago | (#40442169)

My wife and I own a mid-sized restaurant with a couple of Point of Sale (POS) terminals. [...] We can't afford several thousand dollars' worth of server infrastructure (two cluster nodes and some shared storage, or some such), so I thought I'd ask Slashdot for some suggestions on enabling maximum uptime.

How much money do you lose if and when it does go down? How many times does it take for $5-10k (or whatever for a couple servers and storage, power backup, etc) to pay for itself? How much do you pay the guy who keeps it up? Or do you run back and fix it when it goes down?

If it takes years to pay itself off, then it doesn't really matter ... your servers don't go down enough, your time isn't worth enough, or the technology isn't benefitting you enough in the first place. If it only takes a few times, why hedge? And if you're working on razor-thin margins, maybe you should consider why you're spending even as much time and money on this solution in the first place... especially if it's costing you money when it goes down (presumably frequently, or why bother asking).

What's your SLA and why? (1)

Anonymous Coward | more than 2 years ago | (#40442175)

When people talk about redundancy they usually aim for the highest uptime they can think of without considering if they really need that.

If you're a mid-sized restaurant and can't afford the big-boy's solution to redundancy, just make sure you have disk mirroring and backup's ready. Keep a spare machine installed with the same version of Windows, .NET and SQL Server and document what needs to be done to move data over the dead machine's disk onto the new one. Is it that much data? Do you need it all to keep the restaurant running? Can you bootstrap with enough data only and leave the sync'ing for when you're closed?

It's business requirements first, technology second.

MICROSOFT SQL Server (1, Interesting)

Anonymous Coward | more than 2 years ago | (#40442177)

Could we please stop ceding generic terms like "SQL Server" to Microsoft? Oracle produces an SQL server, as does IBM (DB2), as do several other companies and open source organizations. Why does "Microsoft SQL Server" get to be "SQL Server"? Isn't it bad enough that we already given Microsoft the "Windows" name (how old is X Windows?) and "PC" has morphed from meaning "personal computer" to something that runs Microsoft Windows?

Re:MICROSOFT SQL Server (-1)

Anonymous Coward | more than 2 years ago | (#40442841)

Whiny linsux bitchass fanboi

Re:MICROSOFT SQL Server (0)

Anonymous Coward | more than 2 years ago | (#40443013)

Your rant may have been relevant 24 years ago. Please try again then.

Re:MICROSOFT SQL Server (1)

TheRealMindChild (743925) | more than 2 years ago | (#40443043)

Oracle doesn't call their DB software "Oracle SQL Server". As a matter of fact, I think you would be hard pressed to find any product named "*companynamehere* SQL Server". Just like "Windows" means "Microsoft Windows", and not "X Windowing System"

Easy enough (4, Insightful)

Stargoat (658863) | more than 2 years ago | (#40442251)

Get a decent server, maybe an HP. Dual CPUs, Dual HDDs, Dual Power supplies. Get a UPS.

Install Windows, SQL, and UPS controlling software. Install AV, but be certain to exclude AV scanning the SQL directory and SQL DBs and logs. You don't want AV killing your SQL server by accident. You might want to consider putting a firewall on the box and blocking all non-SQL traffic.

Patch as needed.

Install nothing else. No mine-sweeper, no restaurant food ordering software, no adobe. Nothing will kill a server faster than turning it into a desktop. Don't try to do anything on it. Just let it be a server running SQL and you'll be fine. Don't plug USB drives into it.

You should be able to back up the SQL db every so often stopping SQL and then starting it. Try to do this around the monthly patch cycle. Don't patch immediately upon one becoming available, but rather wait a week. This will give Microsoft time to correct any patch issues they have. You'll be much more vulnerable to patch issues than you will from viruses if you follow the "don't turn it into a desktop" suggestion.

well the restaurant food ordering software or even (2)

Joe_Dragon (2206452) | more than 2 years ago | (#40442377)

restaurant food ordering software or even time clock software may need a backend sever for there own data bases or some stuff like the food system may tie into the main data base.

Re:Easy enough (2)

sirsnork (530512) | more than 2 years ago | (#40442763)

Uhh, what.... stop the SQL server to back it up.... have you never used SQL before?

SQL can happily back itself up while running. In fact any decent backup program can backup the whole system while SQL is running and still get a clean DB copy

Re:Easy enough (2)

TheRealMindChild (743925) | more than 2 years ago | (#40443127)

A LONG time ago (think SQL Server 6.5 era), you COULD back up a live DB server, but if it was large enough, an online copy could take longer than a day, where an offline file copy of the DB was as fast as your system could read/write it.

Re:Easy enough (1)

Anonymous Coward | more than 2 years ago | (#40442835)

Have you done service calls in a restaurant?

I have done LOTS. Having RAID 1 for the HDD is good but beyond that, dust and grease filters on the cabinet and siteing it FAR from dust, grease, and water will go a lot further than spending money on "server grade" redundant hardware.

I absolutely agree on not useing a server as a workstation though.

Re:Easy enough (1)

bobcat7677 (561727) | more than 2 years ago | (#40443159)

Stargoat's answer is pretty good. I'll add the next step above that for when you just cant have stuff do down: hardware virtualization with clustering. You can do this with VMware, but it's expensive and you need a dedicated High Availability SAN which is $$$$$$. I have implemented a simple version running SAN software with failover on top of normal servers (Starwind does this and has some cool features). But the best solution would be the new fully integrated solutions that are coming out like this: http://scalecomputing.com/hc3/ [scalecomputing.com]

No matter how you slice it, it's expensive to do it right. That's just the nature of high availability. After many years of trying to do high availability "on the cheap". I can authoritatively say that there is no "silver bullet".

Amazon RDL (1)

wiedzmin (1269816) | more than 2 years ago | (#40442255)

If you can't afford the infrastructure, rent a cloud server and charge it as recurring business expense.

Re:Amazon RDL (0)

FlipperPA (456193) | more than 2 years ago | (#40442501)

SQL Server on Amazon Web Services starts at $0.629 per hour, for about $15.10 per day. That comes out to be about $5,500.00 per year.

http://aws.amazon.com/windows/

Re:Amazon RDL (0)

Anonymous Coward | more than 2 years ago | (#40442847)

If you're doing MS SQL in the almighty "cloud," the cheapest option is still to use Microsoft Azure, but even with only 20GB of storage and 100GB of bandwidth, you're looking at about $1,000 a year.

With this being a non-depreciable operating expense versus a capital expense, spending $1,000+ a year might not be your first choice, but considering the alternative - hacked-together internal VMware solution requiring additional software, maintenance, expertise, etc. - it might be a *good* choice.

Re:Amazon RDL (0)

Anonymous Coward | more than 2 years ago | (#40442551)

LOL, really? The uptime of their internet connection is pretty much guaranteed to be the weakest link. If he can't afford redundant servers, he sure as heck won't be able to afford an internet connection with a 99.99%+ SLA guarantee to his little restaurant.

The cloud isn't a catchall solution to every problem.

Re:Amazon RDL (1)

christianT (604736) | more than 2 years ago | (#40442877)

The cloud isn't a catchall solution to every problem.

Say it ain't so!

Quality hardware. (0)

Anonymous Coward | more than 2 years ago | (#40442287)

Seriously, number one issue is quality hardware. If your power supply isn't regular, that's an issue. If you don't have proper ventilation and cooling? That's an issue.

Easy solutions, what is the question (2)

Melakh (2670043) | more than 2 years ago | (#40442301)

Not enough information, you'll have something like failure possibilities in: the physical server, the VMs, the SQL Server instance, the Hard Disks, the hypervisor, the POS application, the queries, the backup process, the restore process, etc. All of them have tried and trusted solutions, but you need to establish what you're tackling first. If the answer is 'all of them' and you don't want to break it down and think about each item then you're better off pushing the problem out to an expert to manage it for you, you can take a range of hosted solutions or get someone to remote or local manage your infrastructure for you.

2-node cluster and a cheap NAS perhaps? (0)

Anonymous Coward | more than 2 years ago | (#40442321)

Need automatic failover? Use your two hosts to build a 2-node cluster and buy a simple NAS for iSCSI shared storage between them. They are usually cheap.

You could also skip the failover worries by setting up an active-active SQL cluster using hyper-v.
http://blogs.technet.com/b/meamcs/archive/2012/04/10/creating-an-active-active-sql-cluster-using-hyper-v-part1-virtualized-storage.aspx

If you build the servers yourself, you can keep the costs under $1k. (xeon 1230, asus P8B-M, iscsi capable nas)

Have you considered PostgreSQL? (0)

Anonymous Coward | more than 2 years ago | (#40442409)

While being a fan of pg myself I have read that some SQL Server admins have grown very fond of new PostgreSQL features. Master-slave replication might be what you need, though I haven't ever had a problem with pg not staying up (in contrast to my own applications).

Here's a nice intro/demo/presentation by Rob Conery: Five Things You Didn’t Know About PostgresSQL [databasetube.com] .

No idea how could you convert your installation into working with pg (all I know about sql server syntax that it's very ugly compared to what I consider standard SQL; microsofty language extensions etc), but you could find some better POS software which would support pg, hopefully making you some savings in the process with less licensing fees.

MySQL cluster (3, Informative)

Animats (122034) | more than 2 years ago | (#40442433)

You can run MySQL Cluster [mysql.com] on two machines. It's somewhat complex to set up. And your POS terminals have to be able to connect to either server. But it's available.

If you're getting more than one crash a year, you have hardware problems. Commodity hardware may be unsuitable for a restaurant environment. You may need an industrial-grade PC, with a broad operating temperature range and resistance to dirt, dust, grease, and water. There are PCs and enclosures for restaurants, and the fast-food industry uses them extensively. Every McDonalds, Burger King, and KFC outlet uses industrial-quality POS systems.

You wouldn't use a home-quality stove or a home-quality coffee maker in a restaurant. It wouldn't hold up. The same goes for a computer.

Check out this simple POS system (0)

Anonymous Coward | more than 2 years ago | (#40442469)

http://www.whentomanage.com/

Ask the POS vendor (2, Insightful)

Anonymous Coward | more than 2 years ago | (#40442523)

Stop. Whoever makes the POS software is the expert that you want to consult. Call up their support and ask them what they recommend.

Moving it elsewhere may not help (1)

Eivind Eklund (5161) | more than 2 years ago | (#40442545)

I would not immediately assume that moving it somewhere else will increase uptime; it puts uptime requirements on the Internet link(s) instead of on the server or software setup. Unless the present setup is quite unreliable or he has a surprisingly good link, I think that would likely be a worse problem.

Now, the idea that you can't afford multiple server nodes: Servers can be very, very cheap. For my home server I use an Acer Revo 3600 I paid 200 euro for; the closest available today seems to be http://www.amazon.com/Acer-VN281-2G-320-Linex/dp/B005WUXW1C [amazon.com] (at about $220 including shipping.) Assuming you don't have a license cost problem, this allows you to create a cluster for a very low cost.

Apart from that, I'd analyse what your costs are for a failure, and what the odds of a failure are, and whether your tinkering increase or decrease the odds. I'd assume the odds were fairly small to start with; in that case, it may not make any sense to tinker with the setup to create something that is supposed to be more available. I've easily had several years of uptime on single systems; introducing complexity makes that harder, and if you lack the experience with how to deal with these systems, that's likely to increase the risk. (What happens if somebody start your failover by mistake? What happens if both instances are running? etc)

For your particular use case, it sounds like I'd rather have a good alternative system for handling it if your system fails (pen and paper sounds good), and try to beef up the single machine - place it somewhere it won't have dust, vibration and heat problems, use multiple network cards to avoid risk of cable failure, use reliable disks & RAID, have a good UPS with monitoring, etc.

If you can't measure it, you can't manage it (4, Interesting)

gnetwerker (526997) | more than 2 years ago | (#40442569)

If you can't measure it, you can't manage it. You haven't taken the first and most essential step in analyzing your problem: measuring it. Is your problem caused by network failure? By power? By software failure? Hardware? If hardware, by server hardware, disks, or something else?

If software, by OS, database, or application software? All of these have different solutions. Going to the cloud won't solve a network failure, it will make things worse. Going to the cloud may improve persistent hardware failures. but the MTBF of most decent hardware is pretty good, so are you sure you have clean power and a good (cool, clean) environment?

If your software or system is crashing, then that's its own problem.

Re:If you can't measure it, you can't manage it (1)

TheRealMindChild (743925) | more than 2 years ago | (#40443155)

If you can't measure it, you can't manage it.

So all of us crazies are hopeless and should throw in the towel?

There are a couple options (1)

Vrtigo1 (1303147) | more than 2 years ago | (#40442641)

First, getting everything into a VM environment is a huge win as far as manageability as it buys you hardware agnosticism.

The product description for VMware HA is exactly what you are talking about, but you do need a license to use it. I think you can get a barebones license for about $2k, which is expensive for a small shop, but it does buy you automated failover.

There are plenty of desktop class machines out there that are compatible with VMware, so assuming your hardware requirements aren't too high you could buy two of them and a license and some shared storage and still be under $5k, which IMO is very reasonable for a small business wanting failover. Just keep in mind that your shared storage then becomes a SPOF so you don't want to go with openfiler running on an old PC with a single SATA drive.

Another option could potentially be putting the server in the cloud, assuming you are not bandwidth constrained and your Internet connection is stable. In the cloud you just pay for the server and let them worry about uptime. On the back end, they have massive redundancy and your VM would recover from a failure inside 10-15 minutes.

Backup with minimal downtime. (0)

Anonymous Coward | more than 2 years ago | (#40442707)

This is a shameless plug for my companies software...
http://www.storagecraft.com

This will keep the entire machine (desktop or server) protected with great backup; with a downtime of a few minutes. In the worst case if you have to go buy a new machine to run it on it will restore to dissmiliar hardware. Or, even to a VM. And, no need to take SQL down to backup. It takes care of all of that.

It will keep the machine safe and ready with a downtime of minutes, not hours or days.

Lease instead of buy (0)

Anonymous Coward | more than 2 years ago | (#40442713)

Yes, what you want might cost a few thousand, but you can easily lease that instead of an outright purchase, and spread it over 2-3 years with no problem. Do it right, you will be happier.

really (2)

spongman (182339) | more than 2 years ago | (#40442727)

Have you really ever had problems with SQL Server crashing before? What version? What kinds of workloads? Did you tell Microsoft?

Don't bother with clustering, use Mirroring [microsoft.com] or AlwaysOn Groups [microsoft.com] instead.

The most straightforward thing I can think of is (1)

SDrag0n (532175) | more than 2 years ago | (#40442743)

Backups with a powershell restore script can be pretty easy to use once you get them setup. As always, if you go the "backup" method, make sure you test your backups sometimes and your full restore procedure to make sure it works. It's not fun to find a problem with them when it really matters.

Replication is a pain to deal with. You could just use a failover cluster [microsoft.com] .

Why not just employ a proper consultant... (0)

Anonymous Coward | more than 2 years ago | (#40442857)

... instead of trying to do it on the cheap through slashdot?

Honestly (1)

Mullen (14656) | more than 2 years ago | (#40442903)

If you are not willing to put the money into the infrastructure, you are not going to get the infrastructure that you would have if you had put the money into it. There is no magic secret sauce that IT people have that turns low budget implementations that operate the same as well thought out, planned, paid for and implemented infrastructure. In other words, baring any greatness or incompetence of IT skills, you get what you pay for.

Plus, when you look at these infrastructure problems, don't look at it as how much money are you willing to spend, but how much you are not going to lose if your infrastructure is down. Make sure to be honest with how much it costs you if you are down and how often is it down? Through all of this, unless you are using low quality hardware, I am willing to bet that any downtime is caused by lack of power or the software failing.

hot-swap RAID-5 (1)

PJ6 (1151747) | more than 2 years ago | (#40442987)

I have never seen SQL fail all by itself. In my experience, by far the most likely point of failure is the hard drives.

why not SQL on the POS? (2)

dutchwhizzman (817898) | more than 2 years ago | (#40443019)

I've seen pretty nifty windows based POS systems that had the SQL server start on each POS, look for a master and if there was one already, try and be slave. If that failed, it would just fail and try to become slave every few minutes. Apart from that, the POS software would just connect to the master-of-that-moment. Once the master went down, the slave would promote itself and the fist POS that tried then became the slave. All POS terminals that were up would constantly replicate database files/dumps/backups whatever so they would never be too far behind the master. I don't know how this mechanism worked exactly, but it was pretty resilient against little restaurant accidents and power glitches.

I Wouldn't want to directly advise to go cloud. If your uplink dies, so does your POS system. You could put backups in the cloud to prevent theft or arson destroying your accounting and books, but I'd not trust a single uplink high latency service with your primary business myself. Even if you get business quality lines with a proper SLA, you still can be down for half a day easily and pay hundreds of dollars per month for just the single uplink. Getting two independent uplinks with this kind of SLA will be so prohibitively expensive that you could easily afford to do your own cluster for that kind of money.

Database Mirroring (0)

Anonymous Coward | more than 2 years ago | (#40443065)

Database Mirroring does not require shared storage and is fairly simple to implement. You just need a second instance of SQL Server installed somewhere.

Move to Azure, spend on connectivity (1)

Ivan Stepaniuk (1569563) | more than 2 years ago | (#40443079)

  • - Move your application to Azure if migration is possible, if not, evaluate Amazon costs. (Amazon will provide you with something more similar to your current server, but it's more expensive).
  • - Spend some bucks in a second backup internet connection from another provider and technology (ie: If you have Cable get DSL, Wireless, etc.).
  • - Get a decent SOHO router with WAN failover capability.
  • - Forget UPSs, your restaurant won't probably work without electricity anyway.
  • - Keep it simple! Restaurants existed since ancient Rome, it wont hurt training your people to use good ol' paper and pen if everything else fails.

Actually a big point is missing here (2)

pointyhat (2649443) | more than 2 years ago | (#40443113)

Firstly, you need to make sure there is a paper process which people can run by if the kit fails. Business continuity doesn't always require a massive DR strategy especially in your market area. If the kit does go pop, your staff need to be able to work instantly - paper is the best and did for years before computers came along.

However from a technical POV, speaking from 15 years experience running SQL Server instances, there's no cheap solution that works reliably. Hot standby (HA mirror) is the best approach for your scenario. It's probably a good idea to host it in another DC to isolate larger failures.

Info here: http://msdn.microsoft.com/en-us/library/bb934127(SQL.105).aspx [microsoft.com]

What *Edition* of SQL Server? (1)

themightythor (673485) | more than 2 years ago | (#40443223)

If I were writing a POS system for anybody smaller than Hardees/Carl's Jr, I'd be using SQL Express. There aren't a lot of sweet options for HA there. At the end of the day, what does your vendor suggest/support? I'd go with that.

Use... (0)

Anonymous Coward | more than 2 years ago | (#40443251)

Postgre.

ha network (1)

bobbutts (927504) | more than 2 years ago | (#40443257)

A couple cheap desktops ($500 each), raid 1 nas storage device (~$500), single drive backup for the NAS (~$200), and 2 gbps switches (~$30 each) you could have the bones for a low budget ha environment.

Don't rely on a technical solution! (1)

njvack (646524) | more than 2 years ago | (#40443269)

Yes, get some decent hardware that won't give you too much trouble -- but equally important: Set up procedures to ensure that when your database is down, you can still get work done. Test those procedures periodically; make sure your staff can run the restaurant when the system is down.

Hardware fails. Software fails. Unless you're willing to spend lots (and you've said you aren't), you're not going to build and test something ultra-reliable. You don't want your entire business on hold (with a restaurant full of customers) because some part of your POS has decided to crap itself.

Recommendation from the source (5, Informative)

matthollingsworth (2670069) | more than 2 years ago | (#40443281)

I was the Program Manager in the SQL Server team owning all of our availability products in Redmond (created the AlwaysOn program). My recommendation is to 1) keep it simple and 2) implement a layered approach. But first I have a basic question - are you trying to protect SQL Server only or do you also need to protect that application and hardware? Because before we dive into the details, it might make sense to take an entirely different approach than the lower level availability technologies. And you also want to consider what it is you are protecting against. If you want to protect against hackers as well as power outages, disk failures, etc (and I suppose you probably will want to do that!), then I recommend that the first thing you do is perform regular backups to a cloud provider. That gives you the ability to restore to a point in time prior to a malicious attack. And it gives you defense in depth. Then, if you want to protect the app overall, maybe you should consider making it something that can also be hosted in the cloud for the next layer of redundancy. That way if you completely lose the site you can direct people to the cloud enabled app. But this also retains the ability to run it locally as the POS solution. Next, I'd consider a way to keep the data synchronized between the POS local installation and the cloud solution running in the VM. The cheapest solution is to use log shipping which performs backup and restores into the secondary (here in the cloud). This is also nice since you need the backups anyway for the first reason stated and this automates it. You should consider using database mirroring (now called AlwaysOn in the latest incarnation in SQL Server 2012) for the data synchronization. It's integrated into the SQL Server engine and provides better performance and the ability to configure it for no data loss and auto failover using the synchronous option. It comes in Standard Edition (sync only) and Enterprise Edition (async and sync). Also cover yourself for the common failures locally. Use a battery backed UPS and consider RAID for your disks on the computer. RAID 5 is probably fine for POS. If you have any other questions, feel free to email me johnmatthewhollingsworth@gmail.com. Best. Matt

SQL Server experience (2)

AliasTheRoot (171859) | more than 2 years ago | (#40443335)

I recently inherited an application built on .NET, we're a Linux organisation. The devs had typically built it on SQL Server Express with not a care in the world, but it was a core business app.

We bought a single license of SQL Server standard, and put it in Master Slave replication mode. Not having touched Windows for years (as a server) it was a bit of faff to get an Active Directory setup going, but it actually works okay. You don't need to license the failover server for SQL Server.

If there's a failure, it's about 10 minutes on notification to flip the servers over and a bit of manual intervention. You can cut this down by buying a third box to use as an observer, but that seems to be another SPF.

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>