Hello everybody,

we want to use MobiLink 17 with Microsoft SQL Server 2014 SP2 as consolidated db - which recovery model of the MS SQL database is supported/recommended - simple, full or bulked logged?

Thanks for your help and best regards, Alex

asked 08 Oct '18, 23:55

Alexander%20Ilg's gravatar image

Alexander Ilg
346272739
accept rate: 50%


As MobiLink does not rely on the transaction state of the consolidated database, I don't think there is a MS SQL Server recovery model that is generally not supported. I guess it's a question of how often your ML clients will sync and how much data may be lost (and how much time a recovery will take) when you have to restore the consolidated database.

Say, if you would use the simple recovery model (which does not use transaction log backups, AFAIK), you could only restore to the last full or differential backup, meaning further transactions would be lost. If remote databases have successfully sync'ed after the last backup, their changes would be missing in the restored consolidated database, and you would have an inconsistent system (i.e. disagreements between the offsets in the consolidated and the remotes) and might need some fixing unless the remote databases can re-do their missing syncs automatically.

permanent link

answered 09 Oct '18, 03:39

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

converted 09 Oct '18, 10:43

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118

1

> unless the remote databases can re-do their missing syncs automatically.

I was in complete agreement with Volker up to that point :)

In reality, there are many different distributed database recovery situations. Each situation may have a "minimum effort" recovery process that will bring everything back to normal in the shortest time with the minimum amount of work and no further errors. The problem is, no human being can prepare all the perfect minimal-effort recovery process(es) in advance... and Murphy's Law stipulates the process you need will be one you didn't prepare.

Suggestion: Prepare a worst-case less-than-minimal-effort recovery plan that will work in all cases:

For example, after a disaster, (1) back up all the (presumably still working) remote databases to preserve any data they contain that must be re-sent to the consolidated, (2) recover the consolidated database as best you can, (3) stop synchronization, (3) re-extract the remote databases, (4) re-start synchronization, and (5) manually re-apply the remote database changes that must be re-sent to the consolidated... they will get uploaded in the next synch.

Murphy's Law says you have two choices: Prepare the worst-case recovery in advance, or start working on it after all the other methods fail :)

(09 Oct '18, 05:10) Breck Carter
Replies hidden
1

Well, I had thought that ML would always require a re-extract of the remotes (at least for those that have sync'ed after the last cons backup) after the cons was restored from a backup - exactly what has to be done with a SQL Remote setup after the cons has been restored. (Been there.)

But I guess the default ML sync mechanism would force the remotes to re-do the sync based on the progress offset known in the (restored) cons, so it might work without data loss if the remotes still have their logs from that offset available. (Of course, that would not suffice if the cons also misses transactions from local users and other sources.)


That being said, I fully second your suggestions for a worst-case plan. No disagreement here.

(09 Oct '18, 05:44) Volker Barth
1

If you recover your consolidated database back to time "X", then remote databases that synchronized against the consolidated after time "X" will have successfully uploaded data to a database that no longer exists and will have downloaded data from a consolidated database that no longer exists. While it's true that it is possible for dbmlsync to re-upload the data if the transaction logs still exists, what do you do with the changes that now exist on the remote database that were downloaded after time "X" that no longer exist in the consolidated? The data is out of synch between the consolidated and remote, and this will often result in requiring manual intervention at the remote site if you don't just want to re-extract the remote database and lose any changes in the remote database that were made since the last successful synchronization before time "X".

In a perfect world, the recovery of your consolidated database would be setup such that if a transaction is successfully committed in the consolidated database, your recovery process will be able to recover that transaction. If your consolidated database was SQL Anywhere, we'd look at setting up a high-availability environment with live backups of the active transaction logs occurring from a second machine, but I'm not familiar with the backup/recovery mechanisms for all the RDBMS supported by ML.

Reg

(09 Oct '18, 10:56) Reg Domaratzki
Replies hidden
1

> In a perfect world... your consolidated database... SQL Anywhere

There, now it's an Elevator Pitch :)

(09 Oct '18, 13:19) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×371

question asked: 08 Oct '18, 23:55

question was seen: 837 times

last updated: 09 Oct '18, 13:22