If we switch our MS Access database to SQL Server, can transaction logs go to an external disk?

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.

Who is Participating?
KorbusConnect With a Mentor Commented:
I agree with Connectex, and most especially his cevat about regularly removing the drive.  I'd do  seperate copies, throughout the day, of the transaction log files to the take-home drive. (or at the end of the day, and grab both drives in a fire.)  

Regarding procedures:  Transaction logs will allow you to restore the work done to the database since the previous full database backup.  You need to make sure you configure the databases for full backup, in SQL manager.  You can configure these to run as often as you like, we use 15min.  It can be a lot of files with short times like 15min, be each file is smaller, so it all comes out in the wash.  You need to make sure you clean thes old files up.  You configure the jobs to do this cleanup, and the incremental and full backups themselves with the SQL manager GUI.

For data integrety you definately want to move from Access to SQL server, but you should be prepared for some development issues when doing that.   It's NOT going be 1-2-3, but it is definately do-able.
connectexConnect With a Mentor Commented:
Yes can place the logs on any drive. It's recommended the logs be on a separate disk then your database (.mdf) for performance reasons. But it doesn't always happen in small server setups. However you do not want the logs on a drive that's being removed regularly.

Larry Struckmeyer MVPConnect With a Mentor Commented:
To add to this for others who may read this, the relationship between a database, the log files and backups is often misunderstood.  When a transaction, (SQL or Exchange are treated basically the same way) is created it is committed to the database as soon as the "commit" sequence is issued.  Can be a direct command, or a built in sequence, as in Exchange.  If the database was last backed up a week ago, a log of every transaction since the backup are held in a special log file, or series of logfiles since the last "aware" backup.  (Images don't do this.  They just make images of whatever is there, but that can be a good thing.)

Now imagine that the server is stolen and all you have is the week old backup.  Then that is all you have.  But if you have the log files in addition, you can "replay" the logs and push/pull them into the database to bring it current up to the last recorded/logged transaction.

The above is most commonly thought of in the case of a drive failure.  If you run the "aware" backup every night, but initiate new transactions starting at 0600 and the drive containing the database fails at 1300 hours, you have lost the database.  If the log files are on the same drive, you will have lost them as well, and you will have to reconstruct all the missing transactions.  But, if the log files are on a different drive, you can replace the drive, restore the backup from the night (week?  month?) before and replay the log files to bring the database current. So long as you have a backup and the logfiles created after the last backup, you can bring the database up to date.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

rberkeConsultantAuthor Commented:

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.

Based on those responses, I plan to configure SQL to do incremental backups to Interal Drive E: every 15 minutes, and somehow flush them to ExtDiskX after creation.  

Korbus -- you said <<I agree with Connectex, and most especially his cevat about regularly removing the drive.  I'd do seperate copies, throughout the day, of the transaction log files to the take-home drive. (or at the end of the day,AND GRAB BOTH DRIVES in a fire.)  >>

I understand you are referring to two backup drives, a primary backup drive which is rarely removed would get a nightly full backup and SQL logs every 15 minutes and a "fast getaway" drive which gets copies of everything and is rotated offsite every night.

But, please clarify, are you suggesting they both be EXTERNAL drives, and in a fire you would grab BOTH? What about an environment that has random surprise fire drills?  The alarm goes off, and the participants don't know if it is a real fire or just a test?  I would assume you want to keep the primary backup mounted, and just take the fast getaway drive ??  Or perhaps you have a VBA script that can quickly force an up to the minute snapshot to created and flushed the fast getaway drive?

Finally, I know we are speaking theoretically. but how elaborate are your "real life" emergency procedures?  In my long ago CICS/IMS days we had serious procedures that were well documented and tested semi annually. I sometimes get the impression that very few SBS installations ever conduct fire drills.
Larry Struckmeyer MVPCommented:
Getting pretty intense.  Another option is to use your imaging software to image incrementally and use Symform to send that to the cloud.  If you have more than 10 mb up, you should be current to the could within about 20 minutes.  All the other ideas will keep you safe in case of hard drive or server failure, but the most important thing in case of fire is to get staff out of the building.  Unless that external drive is sitting on your desk, or in the hallway on the way to the exit, I cannot suggest you should go down the hall, into the server room, find a drive, and then find your way out.
rberkeConsultantAuthor Commented:

All sane people will agree safety is paramount, but our unlocked server room is 10 steps from the driveway exit. And this is a small office building with few staff and no industrial hazards. An orderly exit is important,  but a 1 minute delay to save a day of work from 10 employees is considered a reasonable compromise. A 3 minute delay is considered too long.  

If "intense" means thorough I agree that I like to be thorough -- that's the point of a disaster procedure.  But, our actual needs are modest, so I won't get carried away.  For instance Symform might not  be cost effective because there are recurring costs to backing up to the cloud.  The current budget for the SQL Server Pilot test is only $1000.  But, I understand Storage Craft ( ShadowProtect) has partnered with Symform, so it is not entirely out of the question.  But that is  beyond the scope of this question, which I hope to close soon.

 I just now saw your 7:32 post .  You are hitting important issues, but they will be covered in follow up questions that I will post next week.  But, since you mentioned them, I will make a few comments. You refered to "replaying" the transactions. That used to be called "forward recovery". Under IMS we could take a nightly image copy and re-apply the next day's transactions from a log tape. For large databases this might take several hours.

And, that is exactly my main goal. I want to have a single external hard drive containing nightly backup plus the next day's transaction.  I would then use forward recovery to recover as many transactions as possible.  
My secondary goal used to be called "Dynamic transaction  backout". This would be used for application aborts, or Windows crashes that caused inflight transaction to not complete. After the operating system is restored, all inflight transactions would be "undone" before the applications were turned back on.  this would usually only take a few minutes.

Larry Struckmeyer MVPCommented:
The intent of 7:32 was/is just for anyone who reads this thread and does not understand what we are going on about.  I have seen some pretty weird interpretations of log files and their relationship to the database.  Not aimed at you.
>>But, please clarify, are you suggesting they both be EXTERNAL drives, and in a fire you would grab BOTH?

This entirely depends on weather or not you copy these log files throught the day to your removable hard drive.  If you do NOT do that, then you'll want to grab both HD's (so they would both need to be external).  If you DO copy the logs to the removable drive throught the day, then you'd only need to grab that one (but dont forget to setup notifications, should those copies fail for any reason).

rberkeConsultantAuthor Commented:
Thanks for the interesting discussion.

I did my best to allocated points, but it was because some of the better discussions went beyond the scope of the question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.