Follow Slashdot stories on Twitter

 


Forgot your password?
Close
typodupeerror
×
Software Hardware

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

Posted by samzenpus from the bang-for-your-buck dept.
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?"
This discussion has been archived. No new comments can be posted.

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

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

Comments Filter:

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

    by royallthefourth ( 1564389 ) <royallthefourth@gmail.com> on Monday June 25, 2012 @01:46PM (#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? (Score:5, Informative)

      by Anonymous Coward on Monday June 25, 2012 @02:04PM (#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? (Score:5, Informative)

        by spongman ( 182339 ) on Monday June 25, 2012 @02:42PM (#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? (Score:4, Interesting)

      by markus_baertschi ( 259069 ) <markus@@@markus...org> on Monday June 25, 2012 @02:10PM (#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? (Score:4, Insightful)

      by Bengie ( 1121981 ) on Monday June 25, 2012 @02:22PM (#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: (Score:2)

      by Bigby ( 659157 )

      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.

    • The importance of this question is not to be underestimated. Anything that adds availability also adds complexity, and if not done correctly will actually worsen your availability. More than once I've seen new "high availability" setups that were less reliable than the original "non-high availability" setup.

      If you want to protect against hardware failure, get a server with redundant power supplies and hard drives (can be had for under $2k), and monitor them. Now, redundant power supplies are a no-brainer, b

  • SQL 2012 (Score:5, Informative)

    by g0es ( 614709 ) on Monday June 25, 2012 @01:49PM (#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]
    • 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

      • don't pay extra for asynchronous mirroring. it's only useful for distant DR scenarios where c is a factor. if you're trying to do cheap fail-over, use synhcronous mirroring on standard edition with a decent network connection (cross-over 1GB works great). it's cheaper and safer.

  • 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

    • i'm guessing he wants failover so he doesn't have to be rebuilding machines and restoring backups just to get his POS systems up and running 10 minutes before the lunchtime rush.

  • Easy enough (Score:5, Insightful)

    by Stargoat ( 658863 ) * <stargoat@gmail.com> on Monday June 25, 2012 @02:00PM (#40442251) Journal

    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.

    • 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.

    • 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

      • 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.

        • Doing backups while online (still running) work just fine, and will run as fast as your little hard disk can write.

    • 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:

    • Re: (Score:2)

      by slazzy ( 864185 )
      Make sure staff can't use the computer to surf the internet with at least a password on the computer... this is a very common problem that causes a lot of server issues in this type of environment.
  • If you can't afford the infrastructure, rent a cloud server and charge it as recurring business expense.
  • 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 fo

  • MySQL cluster (Score:4, Informative)

    by Animats ( 122034 ) on Monday June 25, 2012 @02:12PM (#40442433) Homepage

    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.

    • mySQL is not MS-SQL Server. While mySQL may offer more elegant out of the box failover options, it's unlikely a restaurant PoS application supports mySQL as a data source. .NET programers with MS-SQL knowledge are cheaper than unix guys slinging mySQL.

      • In all fairness, all decent .NET programmers can easily switch to using MySQL, Oracle, DB2, or Postgress. They all have .NET connectors that pretty much make switching between them fairly trivial. Even more trivial if you know this might be required beforehand.

  • Ask the POS vendor (Score:2, Insightful)

    by Anonymous Coward

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

  • 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://ww [amazon.com]

  • If you can't measure it, you can't manage it (Score:5, Interesting)

    by gnetwerker ( 526997 ) on Monday June 25, 2012 @02:19PM (#40442569) Journal

    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.

  • 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 hi

  • really (Score:3)

    by spongman ( 182339 ) on Monday June 25, 2012 @02:27PM (#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.

  • 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 h

    • 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.

      That's true, but there are several shades of "not putting the money into infrastructure". For example, one can put PCs on a HA setup (a redundant array of inexpensive computers), that will increase the uptime when compared to a single PC, and if done well can even be more reliable that a server. Or, use RAID, hardware or even software RAID if the p

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

    • Don't you mean hot-swap RAID 1?

      Raid 5 puts pieces across multiple drives along with parity bits, no single drive has a complete copy.

      Raid 1 is mirroring where two drives have identical information. Each drive has its own copy.

  • why not SQL on the POS? (Score:3)

    by dutchwhizzman ( 817898 ) on Monday June 25, 2012 @02:42PM (#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.

    • What if his network goes down? He needs to spend a few hours figuring out a pencil/paper/calculator process and have that ready to go as a backup.

    • You realize there is nothing keeping that nifty windows based POS from using the cloud and if down, switching to a local copy of SQL Server to use as a cache until the cloud comes back up, right? Or always using the local, and having it replicate changes to the cloud as possible.

  • 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 app

  • Recommendation from the source (Score:5, Informative)

    by matthollingsworth ( 2670069 ) on Monday June 25, 2012 @02:57PM (#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 (Score:3)

    by AliasTheRoot ( 171859 ) on Monday June 25, 2012 @02:59PM (#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.

    • 10 minutes? manual intervention? you're doing it wrong.

      it takes about 5 seconds for our mirror to fail over and it requires NO manual intervention. i can pull the plug (literally) and after a short pause the application keeps running as if nothing had happened. the only change required to the application was specifying the failover partner in the connection string.

  • First of all whatever you do don't listen to those saying "to the cloud". When your ISP/Internet is not working it means your POS is not working which means your business is fucked -- at the total mercy of a working Internet connection. Most POS software is not optimized to minimize round trips either so expect pushing database to "the cloud" to be much slower.

    The lowest cost easy to configure and manage approach is to setup a periodic backup task to backup entire database every few minutes and copy to t

  • Frankly, if you don't value your uptime worth x why bother? There is no magical pill and if your current infrastructure fails to handle existing load there's nothing you can prudently do without shelling out additional money (or time - which IS money).

    If it all seems to work all right, just make sure you back-up and monitor your existing hardware for possible failures.

  • One of the hats I wear on a daily basis is MS-SQL DBA. I have experience with replication on 2003, 2008 and 2012 versions of Microsoft SQL server in enterprise environments.

    I've also worked for numerous large restaurant chains in a consulting capacity and understand the unique needs here.

    My suggestion within your parameters is to have two SQL instances going, and this can be on commodity hardware like desktop, and use transaction log shipping and DNS-based failover. You will have to script a utility

    • I believe you don't need Active Directory to do synchronous mirroring with failover. you can do it with certificates. I'm not sure why you need the shared storage for mirroring, though. the whole point of mirroring (as opposed to clustering) is to remove the need for shared storage.

  • Why are you worrying about the POS system? (Score:4, Insightful)

    by downhole ( 831621 ) on Monday June 25, 2012 @03:31PM (#40443861) Homepage Journal

    Restaurants POS systems aren't exactly cutting-edge technology. There's gotta be a kazillion commercial systems out there and lots of pros to install and manage them who have set up and maintained hundreds of these systems. There's probably even some sort of restaurant owner's organization that can recommend systems and consultants. Why are you spending time on a tech site wondering about how to roll your own POS infrastructure when it provides no competitive advantage to your business and any screwups could cost big money? Spend your time worrying and working on stuff that will actually help your business compete with all of the other restaurants out there. Not that I know much about the restaurant biz, but I'm going to guess that getting and keeping good cooking staff, waitstaff, etc, getting quality supplies at a good price, and marketing the place and other restaurant-y things belong much higher on the owner's worry list than what hardware and software the POS systems are using.

  • Here's what you want to do ... (Score:5, Insightful)

    by TechnoGrl ( 322690 ) on Monday June 25, 2012 @03:36PM (#40443939)
    By this time you have realized that 98% of Slashdot posters don't know a damn thing about the issue that you need to resolve (... the cloud? .... Really ??? ) and are just flinging buzzwords (monkeys... poo ... ) or asking questions that you won;t know the answer to in hopes that this will get then off the hook in actually answering your question.
    .
    Short and sweet - you want database mirroring with automatic fallover. You can set up a second SQL server on a separate machine (cost less than $500 for the machine) to be the mirror and if your primary machine fails then you are still golden. Here is an article that tells gives you an idea as to how to do this in MS SQL '08 :

    http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm

    Yes, you should hire a **competent** DB consultant to do this for you. Yes it will cost you another $800 - $1000 do have a **competent** consultant do this for you (figure 8- 12 hours work at 80 bucks an hour) - you will lose far more than that the very first time your database fails and/or you attempt to do it yourself and blow away your database because you made a mistake (you do have backups , of course.... right ??? ).

    You can try to do it yourself but I do not recommend it as it's risky.

    I've been doing DB work for 25 years - feel free to send me a Slashdot message should you desire to use my services.
  • I administrate POS systems, the systems I built have served 2 million+ people so far this year. In general as some other people have said, I don't think you are asking the right question. It would help to know which POS software you're using.

    If your SQL server is currently having downtime, WHY is it having downtime?
    Is it hardware? Buy a newer system, nothing very fancy is needed, preferably a dual-raid system, for OS and data, but one raid to rule them all can work too.
    Is it software? This is what
  • Use a "mirror" computer. A duplicate of the server that runs in parallel with the principal, in practice you have two computers operating as one. And if one fails you have the another one operating normally, giving time for you to fix what failed

  • Not gonna happen (Score:3)

    by gotpaint32 ( 728082 ) * on Monday June 25, 2012 @04:15PM (#40444533) Journal
    There is no cheap magic bullet, if there was, everyone would be doing it. You will either pay for licensing, pay for hardware, or both. Clustering is usually a nonstarter due to the expense of a SAN, you get a cheap SAN then you still end up with a lousy single point of failure. SQL replication may work but the POS software may or may not work under that configuration and the fail-over may or may not be automatic so its a real crap shoot. Your best bet is a single quality server, minimize the crap you install on it, preferably just SQL, get a solid properly rated UPS, and make sure it is all setup properly. You will get great uptime. A mismanaged cluster is much more liable to cause downtime than a properly cared for single instance server.

Slashdot Top Deals

You are always doing something marginal when the boss drops by your desk.

Close