Now that I have the number of sessions down to where I can run a user and my email subsystem on the freebie server I'd like to start looking at a backup job. Currently I leave the email subsystem running overnight and have Acronis do a backup. Seems to have worked on the few occasions when I've had to restore.

Question for those already using C/S and doing this sort of thing: what happens to my email subsystem whilst the backup is running?

The email subsystem checks every 5 minutes to see if there's anything to download and if so does and stores in the database, and then sees if there's anything to send and if so sends it and moves the email(s) from the the queued to the sent mailbox (means changing a field in the database). Do I have to program round the backup occurring or do things just freeze? I can pretty well guarantee that the backup will take longer than 5 minutes.


Roy Lambert

Roy

I have not done proper testing, but provided you are using C/S my experience is that it is possible to call "Backup" pretty much at any time and users can continue to access the DB, though access feels a bit slower.

My assumption is that Tim uses some of the magic in the replication engine of EDB to allow him to save a "state" for the DB, and backup the data up to this point. Additions to the DB subsequent to the start of the Backup Job seem to be ignored. Perhaps they are cached in some way ... I don't know.

On 10/20/2014 11:36 AM, Roy Lambert wrote:
> Question for those already using C/S and doing this sort of thing: what happens to my email subsystem whilst the backup is running?
> The email subsystem checks every 5 minutes to see if there's anything to download and if so does and stores in the database, and then sees if there's anything to send and if so sends it and moves the email(s) from the the queued to the sent mailbox (means changing a field in the database). Do I have to program round the backup occurring or do things just freeze? I can pretty well guarantee that the backup will take longer than 5 minutes.

Not really using this in production but i believe there is a write lock
during the backup. The backup is quite quick so try it out.

The other option is to use the publishing/replication (save updates) to
do form of an incremental backup on regular basis (write lock would be
short) and then you'd only have to lock the main db weekly or so.

Or to take it even further you could have a 2nd local database just for
taking backups and then use a frequent save updates from source db and
load to backup db and then you can spend all the time in the world
backing that one up.

However i'd first suggest you check how long the backup actually takes.

Raul

Adam

>I have not done proper testing, but provided you are using C/S my experience is that it is possible to call "Backup" pretty much at any time and users can continue to access the DB, though access feels a bit slower.
>
>My assumption is that Tim uses some of the magic in the replication engine of EDB to allow him to save a "state" for the DB, and backup the data up to this point. Additions to the DB subsequent to the start of the Backup Job seem to be ignored. Perhaps they are cached in some way ... I don't know.

That sounds good.  I shall go ahead and investigate jobs and how to create / use them

Roy

Raul


>On 10/20/2014 11:36 AM, Roy Lambert wrote:
>> Question for those already using C/S and doing this sort of thing: what happens to my email subsystem whilst the backup is running?
>> The email subsystem checks every 5 minutes to see if there's anything to download and if so does and stores in the database, and then sees if there's anything to send and if so sends it and moves the email(s) from the the queued to the sent mailbox (means changing a field in the database). Do I have to program round the backup occurring or do things just freeze? I can pretty well guarantee that the backup will take longer than 5 minutes.
>
>Not really using this in production but i believe there is a write lock
>during the backup. The backup is quite quick so try it out.

It was the effect of the write lock that was worrying me. I agree that backup is pretty quick but with the email subsystem running 24/7 I will guarantee a clash at some point and its what happens then I was worrying about.

>The other option is to use the publishing/replication (save updates) to
>do form of an incremental backup on regular basis (write lock would be
>short) and then you'd only have to lock the main db weekly or so.
>
>Or to take it even further you could have a 2nd local database just for
>taking backups and then use a frequent save updates from source db and
>load to backup db and then you can spend all the time in the world
>backing that one up.

I'm trying to avoid things like that.

Roy Lambert

Roy,

Why not have the EDB backup job set a flag when the backup job starts and clears it when it is finished. Same with your eMail process. Your email process will check to see if the backup flag has been set and will wait until it is over before it processes the email. Same with your backup job. It will wait until the email process has completed.

A flag can be as simple as updating a status record field like "Update ProcessStatus set ProcessRunning='EMAIL', Started=Current_DateStamp();" when the email process wants control. Or set it to 'BACKUP' when the backup process is running. Of course use BEGIN / FINALLY blocks in the job to clear the flag when it is done.

The Started field gives you an idea if the process has been running to long so the status can be overridden.

Barry

Barry


>Why not have the EDB backup job set a flag when the backup job starts and clears it when it is finished. Same with your eMail process. Your email process will check to see if the backup flag has been set and will wait until it is over before it processes the email. Same with your backup job. It will wait until the email process has completed.
>
>A flag can be as simple as updating a status record field like "Update ProcessStatus set ProcessRunning='EMAIL', Started=Current_DateStamp();" when the email process wants control. Or set it to 'BACKUP' when the backup process is running. Of course use BEGIN / FINALLY blocks in the job to clear the flag when it is done.
>
>The Started field gives you an idea if the process has been running to long so the status can be overridden.

In the Delphi controlled parts of my application I use LockCurrentRecord / UnlockCurrentRecord to watch what's going on. The advantage is if the program crashes it release the lock so its very handy. I don't know of anything like that in SQL and I remember dBase (might have been Paradox) where you could leave locks in place after a crash.

Roy Lambert