Move databases to a different drive.

Questions around Server admin, DB admin, email, printers, scanners etc related to Proclaim.
Mark_ACA
Posts: 23
Joined: Mon Aug 09, 2021 10:58 am
Has thanked: 9 times

Move databases to a different drive.

Post by Mark_ACA »

Our infrastructure runs on Virtual machines and I am setting up replication onto a spare host.

I currently cannot do this with the proclaim virtual machine becuase the database does not support VSS.

If I move the databases from c:\Prodisk\Proclaim\v3db to a seperate virtual drive then I can exclude this drive from replication, and have livebackup run regularly instead (~ every 15-20 mins). This way I can replicate the entire server, and if I have to failover to the backup host then I just need to create the seperate virtual drive and restore the latest livebackup data to it.

Currently backups are taken at night and the database has a scheduled task to shut it down befor eand bring it back up after. However I will be able to take regular backups and exclude the seperate virtual drive. I will be able to leave the database up 24/7 then too.

How do I go about moving the path to these data files please ? Is there anything else I need to move to the excluded drive ?

TIA
M

steve
Posts: 486
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 128 times

Re: Move databases to a different drive.

Post by steve »

You've a few options but best involve Eclipse if you are not familiar with Progress DB admin. A braver person than me would give you a step-by-step guide.

The below is just scratching the surface of the topic:

1) easiest - ask Eclipse to move the DBs for you. Is your job on the line should things go pear-shaped?

2) if you really want to move the DB yourself, then get familiar with ProEnv and OE Explorer, and then read this:
https://knowledgebase.progress.com/arti ... dge/P25954
Check that your BI/DB/AI extents are indeed in v3db folder.

But I think your real questions is 'what are my options for replication to a cold/warm/hot standby?'

1) Replicate the DB server at VM level, EXCLUDING the v3db folder (assuming all BI/DB/AI/tmp extents are in the same folder.) Use "probkup -online" to do nightly full backup. Script a nightly restore to your standby server. Enable AI and script AI replay to the warm standby server to keep in sync. The considerably poorer man's equivalent is online incremental backups through the day.

Read this:
https://knowledgebase.progress.com/arti ... cle/P11011

2)cheap: Use any OS-level backup and restore utility, again excluding the DB extents. If your DB server is just hosting your DB, then ask yourself how much is it changing each day? Use probkup -online and AI replay to keep the DB in sync.

3) pay a fortune for the Gold Standard in replication: OE Replication/+.
Clients seamlessly failover to the hot spare DB server. Offload your reporting loads to the replication+ target too.

4) read around the subject - there are many variations especially with the VM side.

Some gotchas:

1) you've thought about your docs archive with your VSS approach? What about this scenario: you use VSS keeps your docs archive folder up-to-the-second synced to your standby server. Your DB dies at 5pm and you restore to the backup taken at midnight. The DB is now 17hrs behind your docs archive. Plan for and manage this inconsistency in a way that suits you. There's the same subtle issue with syncing your Proclaim DBs too (proclaim, proacc, dyndb, gdpr etc backups taken at different times) but generally less inconsistency to handle.

2) Consider how you are going to failover your clients/taskservers onto this standby server/bring it into production - clients/users point to the DB box via server computername (the DB defined in the -H of databases.pf. )Where are your client executables located?. Are you going to update the clients to point to the new server or just swap out the dead server, with associated client cache issues?
You've got a virtual infrastructure, get VM advice as there are many ways to go about this.

3) Licensing issues need to be considered for the standby server best discussed with your Access account manager too...

4) practice practice practice your DR scenarios, then practice a bit more. Consider implementing a DEV environment in isolated VM infrastructure with a couple of licenses. Then run it all by Access / Eclipse to cover your back.

Please do a full BC/DR analysis for the sake of your business. Start by asking how much downtime and data loss can your business can afford and work up from there.

Usual disclaimer: don't blame me if things go wrong. I've told you to ask Eclipse/Access to do it!

Mark_ACA
Posts: 23
Joined: Mon Aug 09, 2021 10:58 am
Has thanked: 9 times

Re: Move databases to a different drive.

Post by Mark_ACA »

Thanks so much for that advice, all very useful.

I think I will keep the current 1:30am backup of the entire proclaim VM whilst the database is offline (DB stops at 1am until 4am) as a failsafe.
Backup VM's are stored on a NAS (isolated) in the DC, as well as replicated offsite to 3 different locations. 90 days onsite retention, 1 week offsite retention.

The clients are on a small RDS farm, the database itself isn't huge (about 7GB total)

Currently in a disaster I can restore all VM's from the last 1:30am backup.
This includes the RDS VM's to the same time to mitigate any client cache problems.
There is only one task server, this runs on the same serer as the proclaimdb

So our current disaster recovery = up to 24 hours of data loss, with ~4 hours recovery time.

-----------------------------------------------------------------------------------------------------------------


The goal is to improve this, but a cold spare DR solution is fine, even if it takes me ~1 hour to get it live again, and we lose up to ~1 hour of data its a massive improvement, but the closer to real time the better.

I fully test this manually every couple of months, the backup software automatically tests tha the VM's restore once per week.

I have already setup Hyper-V replication for the DC and RDS servers, I have used this before and am quite well versed in it.
I am not well versed in is ProgressDB / Openedge / Proclaim. (How I wish Proclaim used SQL!)

My interim solution I was thinking about was to enable DFS on c:\Prodisk (except the v3db folder, be sure to include the _docs and _DBBackup folders) to a file server, which is also replicated in hyper-v to the spare host. Then have the live backup run every 15 minutes rather than once per day.
This way I can restore the Proclaim VM from backup (~40-50 minutes), then let DFS replicate the _docs and _DBbackup to the latest version available and restore the DB from the _DBbackup folder (up to 15 minutes old).
At this point as I am new to Progressdb I wouldn't even know how to restore those backups at this point. I will learn all this, but in the worst case I would have eclipse restore them at this point.

However moving the v3db folder to a different volume does away with needing DFS so I think it would be much tidier. I will follow your advice get Access Legal to do this to cover my arse, but I want to learn about what to do along the way too.

Thanks for the links :)




-----------------------------------------------------------------------------------------------------------------

The Gotcha's...

1) I would run the live backups more regular than once per day. More like 4 times per hour, this would still be a small issue with some small inconsistencies but much less of one!
I need to do some testing I guess to see what happens. The file system might be 15-30 minutes (at worst) newer than the restored databases. If we were to need to switch to host 2 and restore the DB backup I would tell staff to be aware of anything they did up to 30 minutes previous to the switch over being lost.
So lets say a case has a document letter1.001 and during that 15-30 minutes its revised to letter1.002
letter1.002 will exist it the Docs folder but not referenced in the DR failed over DB. I wonder how revising letter1.001 at this point will behave ? will proclaim error as it cant create the new file ? will it be fine and over write the existing file? Will it use a different file name and leave an orphaned file ?

2) its an RDS farm, and the replicated proclaim server will be exactly the same anyway. it will just be booted up on the spare host and the latest data restored.
I need to learn more about the client cache issues though? Currently the RDS farm can be restored from the same time the backup was taken, what are the client cache issues ? is there a script / command I can run to clear / reset client cache on the RDS servers ?

3) I don't think that I need to have the replicated data online, so having a second license for the DB and playing back changes periodically isn't required. The VM's can sit in a shut down state unless we need to fail over to them.

4) Absolutely, I currently practice what we have now, and I will practice whatever we adopt moving forward.

Please do a full BC/DR analysis for the sake of your business. Start by asking how much downtime and data loss can your business can afford and work up from there. - I'm already on it. The DR I have in place would suffice and we wouldn't sink based on 24 hours of data loss and 4-5 hours downtime. However it would be a massive PITA and everyone would be looking at me, so I am planning to improve that significantly.

If I was to do it myself I would do it out of hours and after a full backup I can revert to. However I think its best to have Access move the DB to offload liability for that.

steve
Posts: 486
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 128 times

Re: Move databases to a different drive.

Post by steve »

Mark_ACA wrote:
Mon Aug 09, 2021 3:23 pm

1) I would run the live backups more regular than once per day. More like 4 times per hour, this would still be a small issue with some small inconsistencies but much less of one!
wow - your DB is small but watch for a performance hit of so many full backups. This is where After Imaging or even incremental backups instead is perhaps more fitting.
https://knowledgebase.progress.com/arti ... cle/P19506

So lets say a case has a document letter1.001 and during that 15-30 minutes its revised to letter1.002
letter1.002 will exist it the Docs folder but not referenced in the DR failed over DB. I wonder how revising letter1.001 at this point will behave ? will proclaim error as it cant create the new file ? will it be fine and over write the existing file? Will it use a different file name and leave an orphaned file ?
I'm thinking more of your archive (case history of sent letters/scanned post etc). If your DB resets to an earlier time yet your archive store is further on, a) Proclaim will complain when it tries to use the next archive doc sequence number and it already exits, and b) you'll lose that hard work people have done. Have a play, but perhaps your DR workflow will move all archive docs created after the last full backup time out of the archive location and into a holding location. Users can at least then find the work they've done / post they've scanned, and re-drag it in.
Indeed letter templates you have created since the last full backup will also need handling, as the Proclaim version counters will be reset.
I need to learn more about the client cache issues though? Currently the RDS farm can be restored from the same time the backup was taken, what are the client cache issues ? is there a script / command I can run to clear / reset client cache on the RDS servers ?
In the metal hosting world, If you restore a server on different hardware with the same servername, the arp cache of clients needs a flush.
I'm no HyperV expert but this is probably handled somehow by HyperV?
3) I don't think that I need to have the replicated data online, so having a second license for the DB and playing back changes periodically isn't required. The VM's can sit in a shut down state unless we need to fail over to them.
It's a good practice to restore regularly to your backup server- it tests the integrity of the backup and your DR process


If you can get to the stage of using "-online" backup instead of shutting down the DB, then that's a big win in my books - higher availability of the DB and a performance improvement as your memory buffer is optimised with most active records

Mark_ACA
Posts: 23
Joined: Mon Aug 09, 2021 10:58 am
Has thanked: 9 times

Re: Move databases to a different drive.

Post by Mark_ACA »

Thanks for all the useful feedback.

When you talked about client cache I thought you meant something Proclaim specific, yes Hyper-V looks after all that on the VM's and Virtual switches, so once the VM's fail over there are no routing or connectivity problems.

Lots to be thinking about... lots of planning and testing to do.

steve
Posts: 486
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 128 times

Re: Move databases to a different drive.

Post by steve »

The main thing is that you *are* thinking about it, and that's better than most!

Mark_ACA
Posts: 23
Joined: Mon Aug 09, 2021 10:58 am
Has thanked: 9 times

Re: Move databases to a different drive.

Post by Mark_ACA »

I've got this pretty much dialed now to 15 minute incremental online backups, with online full backups 3 times daily.
I've fully tested it in several scenarios it works great.

I got some training from Access legal on restoring (rebuilding the structure and playing back) the databases.

For the file archives, All I do is search the entire directory for * and sort by date modified.

I cut out any files with dates after the database snapshot time, I could go through those and work out which files they came from, etc. but for the purpose of testing, I just remove them.

I tested this by generating a document seconds before and seconds after an incremental backup and testing proclaim behavior.