If we switch our MS Access database to SQL Server, can transaction logs go to an external disk?
Posted on 2011-04-19
If we switch to SQL Server, can transaction logs go to an external disk?
We are a small business with modest data recovery requirements. If we were to lose a few hours of work, it would be painful, but we could live with it.
Here are some details that will help you to answer my question.
============ Environment =================
* SBS 2003 with SQL Server 2000
* Currently Ms Access front end databases link to an Ms Access backend database on the server.
* we have two Dell t300 computers. One is a production server and the other is kept at a “Cold Site” to be used in case of a disaster.
* We use ShadowProtect as our backup software to backup to one of 3 external disks named ExtDisk1, ExtDisk2, or ExtDisk3.
* Shadowprotect does incremental backups to ExtDiskX every hour on the hour (e.g. 7:00 pm, 8:00 pm etc).
* Before I go home at 7:30 every night, I safely remove ExtDiskX and swap in the next disk in the sequence.
* At 8:00 every night, shadowprotect notices the new external disk and does a full backup.
The above environment ALMOST meets our needs. If there is a fire at night I use the external disk, and only lose changes that occur between 7:00 and 7:30. If there is a fire at 4:45 pm during the day, our fire drill procedures call for an employee to grab the External Drive before leaving the building, so the worse we would lose would be 45 minutes of changes.
Yes, it ALMOST meets our needs, but not entirely. This is because a bigger worry is data integrity. What if there is an inflight transaction running when Shadowprotect does its hourly incremental backup? Shadowprotect claims that it can handle inflight transactions if we are using SQL Server, but it could never make any such claim about MS Access backend databases. As a result, restoring from shadow protected in our current environment is unreliable.
I don’t mind losing a few hours of work after a fire, but an inconsistent database can go unnoticed for weeks, then hurt us at the worst possible time, and be almost impossible to recover. For this reason, we are thinking about switching our MS Access Backend databases to SQL server. I have a high level question about recovery procedures.
My question is this: Should the SQL Server transaction log go to ExtDiskX? This sounds like a good idea because a catastrophic system failure could be recovered entirely using only ExtDiskX. In other words, a fire at 4:45 pm would NOT lose 45 minutes of data.
From my experience as a CICS/IMS database manager, I know this is a complicated area, but I don’t want a lot of details – I’ll probably be asking for them in later questions.
Also, I would like a good write up about configuring SQL Server log files. Hopefully something that would not take me 2 days to understand.