23

We have PCs running SQL Server (2008 SP4 and 2016 SP1) which regularly lose power. Obviously, this sometimes leads to (index) corruption of the SQL Server database, which we need to restore afterwards.

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss (more on that below, if you are curious). Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

Background: The "PC" is a Windows tablet mounted on a forklift. When the user turns off the forklift, the tablet loses power. We have tried to teach the users to properly shut down Windows before turning off the forklift, but failed (probably because just turning it off "works" most of the time). We are also currently investigating other options, such as adding a UPS which signals the tablet to shut down on power loss.

Improve this question
0

5 Answers 5

Reset to default
31

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss […]

Actually it's designed to deal with power loss, that's why there are things like write ahead logging (WAL) and crash recovery upon startup (or whatever you want to call it). One of the ways this is done is by choosing to not cache writes which it seems is what the tablet is doing, hence the corruption.

Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

No, SQL Server is doing what it should. You should look either outside SQL Server (windows settings for drive caching [which SQL wants to be off but we can't force you], hardware/firmware updates, etc.) or as Eric has said, buy an external power supply for relatively cheap which could solve the symptoms (the actual issue is probably some type of caching or battery backed write that isn't actually backed).

Improve this answer
2
30

If the tablet has a working battery, you can configure Windows to shut down when the battery power is low.

If the tablet has a non-working battery, consider replacing the battery. (I've had laptops like that - you'd be surprised how inexpensive replacement batteries can be on eBay. They don't work as well as OEM, but hey, anything's better than nothing in this situation.)

If the tablet does not have battery capabilities at all, consider adding a small uninterruptible power supply (UPS) with USB outputs that can communicate with Windows to tell it when it's running on battery power. (For example, I have my own desktop configured to shut down when the UPS is low on battery power - that way it'll shut down on power outage even if I'm not at home.)

If none of those are an option, you're kinda out of luck. It's an old white paper, but the SQL Server 2000 I/O Basics from Microsoft basically explains that you need an I/O subsystem that can handle power outages gracefully.

There are options you can use to increase risk - like Delayed Durability or memory-only (non-durable) tables - but by default, SQL Server is already doing its best to maximize reliability with every write to the transaction log. If even transaction log writes can't be guaranteed due to random power outages, spend the $100 on a UPS battery.

Improve this answer
0
6

Assuming you have a local DB on the forklift rather than a server because of spotty wireless connections? Obviously getting SQL off the forklift would be the preferable solution.

Anyway, like Brent suggested, set the tablet to power down on its own after x minutes on battery power or some similar criteria.

Failing that, a small UPS that can initiate a normal shutdown is probably going to be your best bet in that case. Relying on users for things like that is asking to fail.

Improve this answer
1
  • 1
    "Assuming you have a local DB on the forklift rather than a server because of spotty wireless connections?" Yes, that's exactly the case. The application keeps the local DBs and the server DB in sync, which allows the forklifts to leave the area covered by WLAN and still use the application.
    – Heinzi
    Commented Jul 18, 2018 at 7:49
2

The underlying os has to guarantee either a successful write or an error is returned. The os in turn relies on drivers which in turn rely on firmware which relies on the hardware If either the drivers,firmware oe hardware lie there is nothing windows or sql server can do about that.

This is why you need to check with the driver/firmware/hardware manufacturer.

Also write ordering has to be guaranteed across all the layers so that needs to be checked as well.

Even battery backed caches can fail eg during New York storms some data centres were not accessible for days and batteries would have run out, potentially losing commuted writes

https://www.postgresql.org/docs/devel/static/wal-reliability.html

https://brad.livejournal.com/2116715.html

http://rhaas.blogspot.com/2010/10/wal-reliability.html?m=1

Improve this answer
1

To expand on the other answers:

First, try getting the SQL off the forklift if at all possible. Think recovering from a power loss is bad, try doing it after the laptop got ran over by 7,000+ lbs. With hours of warehouse activity on it, not backed up...

Second, a mechanism for the laptop to do an auto-shutdown after x time on battery should be in place anyway.

Third, would connecting the laptop to a non-switched power feed on the forklift be an option? Be sure to consider safety regulations (the environment may require everything off with the forklift key), and how long the forklift sits between uses (esp. over weekends and holidays) to avoid draining the machine battery.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.