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

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?"

SQL 2012

[g0es]

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]

Go to the cloud!

[Anonymous Coward]

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:What's your actual problem?

[Anonymous Coward]

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.

MySQL cluster

[Animats]

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.

Re:If it a'int broke...

[sapgau]

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:What's your actual problem?

[spongman]

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.

Recommendation from the source

[matthollingsworth]

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 [e-mail]. Best. Matt

Comment removed

[account_deleted]

Comment removed based on user account deletion