Copying Live/Locked Paradox Files

[RazzleUltra]

Not sure if this belongs in programming or troubleshooting but I'm sure the mods will move it if necessary!

My problem is as follows:

I have 26 PCs in remote locations (ADSL connection into our head offices 2MB leased line) which all have a local Paradox database that they use to record sales on through our point of sale software (and before you ask, they're not interested in this problem - beyond the scope of the software / their technical expertise).

I am looking to copy some of these database files back to the office on a five minutely schedule for importing into a SQL database so I can get live reports on sales through the Christmas period. The files themselves are around 50KB in size so copy time is only a few seconds.

Now I can get this bit done fine from my end, but what seems to be happening is that I am getting the occasional bit of corruption on the databases in the remote location though this happens without any error messages at all.

For example, sales that went through the system and generated receipts now have no record on the database. I would guess that this is something to do with the file being locked whilst it is being copied and the program failing to write the new records / timing out whilst waiting.

I've tried looking into locks / low priority copying, but all the ways in which I would copy the file back here appear to be quite dominant, i.e. when they're copying, every other process can just p*ss right off until it's finished.

Has anyone had a similar problem, or failing that, can anyone suggest a way around this?

All suggestions gratefully received!

[stdRaichu]

Databases are a beatch to back up because you have no guarantee that the dataset you've just started backing up isn't going to change 2 seconds later - by the nature of DB's, data at the start of the file can be directly linked to data at the end of it, so the only option for a "simple" backup is to lock the entire DB from any writes.

You can get specialist backup programs or specific DB options that will copy a static copy of the DB while writing data to a cached copy, and then merging those changes back in once the backup has completed; IIRC mysqldump does something like this.

How often do you need to make incremental backups? We gave up on hourly ones for our accounting sofware at work (flat file DB, ugh) because it was next to impossible to back up reliably, and resorted to doing it out of hours. What made it nasty is that the Sage people refused to support it unless it was hosted on a windows box (where you can't read a file that's locked for r/w, which is utterly bone-headed IMHO - quite why MS thought their way of doing things was better than the UNIX locking mechanism I'll never know) so we had to hack a script that kicked off any clients still accessing the DB after allowing them a 10 second grace time to sync any writes.

Crux of the matter is if paradox doesn't support backups natively you'll either need a dedicated backup solution on each machine hosting the files, or resort to only backing up the files when no-one is using them. Is volume shadow copy an option for you?

[RazzleUltra]

Thanks for the reply, unfortunately that's pretty much what I thought you'd say!

The file operations aren't actually backups, it's a case of me retrieving these 26 local paradox tables from the branches in question for DTS importing into a SQL table at our office. For this reason, we can't stretch to getting proper backup software, and I think it would be overkill to use it anyway to get five minutely backups of files from around the company which don't actually require high security backups.

I suppose I could try to get our SQL server to try importing the data directly from the remote location and rely on MS's RBDMS not to lock the database... I'm a little loathe to try this though in case of knacking the remote DB anyway (might be difficult to cobble together a realistic test system, certainly in time for Christmas!).

The PCs hosting the DB in branch are running W2K Pro, so volume shadow copies is not an option... well this financial year anyway!

[thpthial]

I suppose I could try to get our SQL server to try importing the data directly from the remote location and rely on MS's RBDMS not to lock the database... I'm a little loathe to try this though in case of knacking the remote DB anyway (might be difficult to cobble together a realistic test system, certainly in time for Christmas!).

hi,

isnt this the wrong way round? you would want the paradox table exporting to the sql. rather than the sql importing.

i havent used paradox for years... but doesnt it have something like stored procedures?

you can use odbc to connect to the remote sql database, and then set the stored procedure to copy all the info for the last order over when it has finished (maybe hang the procedure off the last field to get altered in the paradox db) locking shouldnt be an issue then.

copying files seems excessive and overly complex for real time...

hope this helps.

th.

[RazzleUltra]

When I say it's using Paradox, it's a paradox database but the branches are running a proprietary piece of point of sale software (Futura) which itself uses the Borland Database Engine (BDE), so I don't really have access to stored procedures at that side of things which is why I'm focussing on the SQL server (which I do have access to!).

Plan 1 was to copy the files across and then let SQL import them through a DTS job.

Plan 2 (from this thread) would be to try and let SQL import them from where they already are....

Might give it a try tomorrow and see how it goes... :)

[thpthial]

Might give it a try tomorrow and see how it goes... :)

mutex's (locking) will mess you up then. personally i think you are biting off more than you can chew with this project.

:/

i can think of many ways to do this, but all except the most hardcore require admin access to the database machines.

sorry,

th.

[RazzleUltra]

Well in the interests of tempting fate, how about you share some of these ideas!

Though I pretty much agree with you, I don't fancy messing things up...

[jesush++]

Ok, so if its a db, its bound to have a query language.

So u connect to the db, query it to get a dataset, and then write it to a paradox db on the machine the sql server can access, and import them that way.

Would this not get round your problem of half written data, as any good db will lock records if they are being written, so you cant do anything to them.

[RazzleUltra]

I think my problem is a total lack of knowledge on Paradox / BDE. If it was a SQL database (or even MSDE) in the remote location, i'd be laughing. As it's Paradox I really don't know where to start on this.

The plan sounds fine in theory though, so presumably I'd have a scheduled task (set to run hourly) which outputs the contents of the table in question, to a new table that is only for my use which I can then copy in the old way?

Just a case of figuring out what the scheduled task is...!

[neil.h]

hey i have no idea about this stuff....but is there not a way to create a buffer while its backing it up....Say on a partition or something.

[stdRaichu]

Not unless the DB (or the app that sits on top of it) supports it, no.

As jesus+= says, the easiest way to do all this would be to tell the database to do a dump to \random\location or even directly over the net to give you something to import from, but a) I don't even know of that's possible and b) that would probaby require local admin access.

Disclaimer: I spend half of my life fighting with cruddy database problems like this. I just wish everyone would switch to PostgreSQL, it would make my life *so* much easier! ;)