Continuous database backups?

Question

I have the following scenario:

Our system is running a SQL Server Express 2005 database locally (on each users desktop, if you will). The system is storing a lot of production data from a machine. There are high demands on the safety of the data, and doing a backup each night, or even each hour is not enough. We need a backup strategy that will ensure almost instantaneous/continuous backup of the database.

Is there anyone out there that has successfully implemented a system similar to this, and/or has got some ideas of how to accomplish it? The only thing I can think of right now is to have mirrored drives (raid) to hold the data, but that would be complicated and expensive.

I would appreciate any and all thoughts on this, since it is a real issue for me and my company. Thanks in advance!

Update:

I was not clear enough in my description of the scenario. The system is storing data in a vehicle that has no connection to anything. A centralized database is therefor not possible. Neither can we use a standard/enterprise version of SQL Server, since it would be to expensive (each vehicle would need a license). Thanks for your input!

If your company wants high-tech solutions, but isn't willing to pay any costs, they are disillusioned. You have to pay for advanced solutions. If the data is so critical, paying for a solution shouldn't be an issue. If it is, your company is unreasonable and I'd personally, look for other employment — HardCode, Feb 19 '09 at 15:16

LicenseQ · Answer 1 · 2009-02-19T16:00:53.740

4

Switch your database into "Full" recovery mode. Do full backup every night and do delta backup after major user action. The delta backups can be done to the flash memory or different hard-drive, and all data can be synchronized with server when online.

Another simple way is to trace all user changes and important data in a text file that stored on a separate drive. If SQL database crashes the user or other operator can repeat steps to restore data.

edited Feb 19 '09 at 16:00

answered Feb 19 '09 at 15:45

LicenseQ

1,721
1
15
21

All of these are very good suggestions. The problem for us is that we need (almost anyway) "live" backups. So we're going to need something that can replicate the data almost instantaneous. But thanks for the advice! – tmatuschek Feb 23 '09 at 07:20

score 1 · Answer 2 · answered Feb 19 '09 at 13:45

1

One way I've seen this done is by using DoubleTake.

answered Feb 19 '09 at 13:45

Otávio Décio

73,752
17
161
228

That looks very promising! Couldn't find any pricing on it though. :-) And from what I could read on the site, it is once again created for a more "connected" scenario, where you are taking backups of centralized SQL Servers? – tmatuschek Feb 19 '09 at 14:07
As far as I know it works at a very low level and it applies both to file servers and database servers. It performs a low level replication, constantly. – Otávio Décio Feb 19 '09 at 14:16
That is very interesting. Any idea about the pricing? Have you used it in any project? – tmatuschek Feb 19 '09 at 14:23
From InformationWeek: Double-Take pricing for Windows starts at $2,495 per server for Windows Server, $4,495 per server for Microsoft Advanced Server, and jumps to $39,995 for Datacenter Server. I had clients using it, not us. – Otávio Décio Feb 19 '09 at 14:56
Ok, thanks. That was what I was afraid of. It's obviously not for the "desktop" environment. – tmatuschek Feb 19 '09 at 15:19

score 1 · Answer 3 · edited Aug 20 '15 at 04:40

I will assume that a central database on a server is not feasible because your systems are running standalone and are not connected to anything. So this is what I would do

Set up RAID on the computer. This insures you against simple disk failure.

Any SQLSever database can be recovered to the point of the last commited transaction if you have a full database backup and a set of transaction logs available. Basically you simply restore the last full backup then apply the transaction logs going forward. See these links.

http://www.enterpriseitplanet.com/storage/features/article.php/11318_3776361_3 https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=132

So what you need to do is set up a periodic full backup of both the database and transaction logs, and more regular transaction log backups (and ensure that your transaction log can never run out of space).

In the event of failure you restore the last full backup, then apply the transaction logs going forward.

Myself, if these are critical systems, I would be inclined to add an additional drive to the system and make sure that the backups are copied over to that. This is because as good as raid is it does sometimes have issues - raid controllers fail, disks get wiped accidentally in parallel, disk failures go unnoticed so your just running on one disk etc. If you ensure backups are copied to a separate disk then you can always recover to the last transaction log backup. You should also ensure tape backups of course, but they are generally a last resort in the event of trouble.

If for some reason you cannot set up raid then you should still install a second disk, but place the database file on one drive and the transaction log on the other and copy backups to both disks. In the event of failure of the C drive, or some other software issue crashing the database you can still recover to the last commited transaction. Failure on the D drive limits you to the last transaction log backup (Oracle used to allow you to mirror the transaction log from the database, which again would completely cover you, but I don't think this facility exists in SQL Server)

If you are looking for a scheduler for SQL Server Express (which doesn't come with one) then I've been using SQLScheduler quite happily without problems, and it's free.

TomHastjarjanto · Answer 4 · 2009-02-19T14:03:23.653

0

Raid isn't that expensive, but it is also not the best option. If you really want high availability data you should upgrade to sql server standard on a remote server where each user connects to and use transaction based replication to an sql server (express) instance on another machine. Raid doesn't always protect you from dataloss. If the data is that important for you then the costs should not be that much of an issue.

Update in response to the question update.

If you can't use remote servers then there a couple of options:

You write a trigger which initiates a backup script on each insert or update and stores it on a seperate harddrive.
You use raid. But beware that if the raid controller fails that you still got a problem.

edited Feb 19 '09 at 14:03

answered Feb 19 '09 at 13:49

TomHastjarjanto

5,386
1
29
41

Thanks for the suggestions. I think that triggers would be too much of a performance issue, and also quite hard to get right. – tmatuschek Feb 19 '09 at 14:57
raid protects only from hardware failure. what about human-erasing-data failure? :-) – LicenseQ Feb 19 '09 at 15:32
Any instant backup solution doesn't protect against human-erasing-data, unless you store the backup in increments. – TomHastjarjanto Feb 19 '09 at 16:38

score 0 · Answer 5 · answered Feb 19 '09 at 13:50

0

The most obvious answer would be to ditch SQL Server Express running locally and use a single source for your data (such as a standard SQL server install on a central storage location). Unless your system requires individual back ups of every single person's own individual instance of SQL Server Express.

If your requirements are so stringent as to call for instantaneous backups on every operation, you should definitely think about a different method of storage than local instances of SQL Server Express.

answered Feb 19 '09 at 13:50

TheTXI

37,429
10
86
110

Thanks for your fast answer. Please note the update I added to the question. We can't use a centralized solution, and the standard/enterprise versions of SQL Server would be too expensive. – tmatuschek Feb 19 '09 at 14:02
Why? Not snippy, but these day you have 7mbit from a moving vehicle for little cost, at least in europe (UMTS mobile phone link). – TomTom May 06 '10 at 15:10

score 0 · Answer 6 · answered Feb 19 '09 at 13:52

0

Wouldn't it be easier to just use one centralized SQL Server and back that up every hour or so? If you truly need instantaneous backup, your company (which seems not to want to spend money by installing Express on each machine) will need to spring for two servers and two SQL Server Enterprise licenses to implement Mirroring.

answered Feb 19 '09 at 13:52

HardCode

6,497
4
31
54

Thanks for your fast answer. Please note the update I added to the question. We can't use a centralized solution, and the standard/enterprise versions of SQL Server would be too expensive. – tmatuschek Feb 19 '09 at 13:59

score 0 · Answer 7 · answered Feb 19 '09 at 16:13

RAID is not expensive. Use RAID to protect against hard drive failure. You also need monitoring though. No point in having this if you let both drives fail.

Also, implement hourly incremental backups, then daily incremental backups and finally weekly full backups.

You need all of these strategies working together because they protect against different things. RAID does not protect against human or coding errors destroying data. Hourly and weekly backups don't protect against hard drive failure.

Continuous database backups?

7 Answers7