Pause and Backtrack

Question

Pause and Backtrack

Steve Jones - SSC Editor

SSC Guru

Points: 736524
More actions
May 22, 2018 at 8:40 pm

#346727

Comments posted to this topic are about the item Pause and Backtrack

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

To be honest, even though both options are great for what they do, they both suck at what actually needs to be done. That would be to backtrack through the logs on a copy of the affected table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David.Poole SSC Guru Points: 76035 More actions · Answer 2

For me its a case of "with great power comes great responsibility".

I have been having a similar debate on a related subject. It is basically a "How can we build something to stop people doing stupid stuff" debate. If you try and build an idiot proof system then apart from the problem of the universe instantly upgrading the idiot what you will end up doing is building an immense behemoth of code to prevent all sorts of eventualities. The sheer size of what you will end up building will introduce bugs and these may be worse than the thing you are trying to prevent. It will also mean that simple changes that should take 10 minutes will now take 6 months to deploy.

At some point you have to list your fears, assign risks and likelihoods and take the steps appropriate to those risks and likelihoods. Sometimes those steps are not code based, they are business process based. For example we used to worry about all the things that can go wrong in a large scale deployment. The engineering that used to go into the mechanism to deploy and roll stuff back was huge. Now we simply don't do large scale deployments. We design and engineer our systems to be deployed in relatively small incremental steps.

Then there is "Trust but verify". I look around my office and I can see people who are trusted with 6&7 figure mortgages, controlling 2 tonnes of metal travelling at 70ish miles an hour on a crowded road and bringing up children and sometimes children with disabilities. Yet such adults in an office work under a system that infantilises them.

Yes, the wrong restore can be disastrous but you would be better off making sure people know and understand when to do point-in-time recovery, when not to and what to check for before doing it. If only the priesthood can do stuff then you end up with single points of failure and a genuflection queue.

LinkedIn Profile

Pablo Echeverria Right there with Babe Points: 750 More actions · Answer 3

Pablo Echeverria

Right there with Babe

Points: 750

May 23, 2018 at 6:31 am

#1991593

I totally agree with you, Steve.

roger.plowman SSChampion Points: 10265 More actions · Answer 4

So...ideally what you want is a dependency tracking feature that allows changes to a (set of related) tables to be rolled back independently of changes in (unrelated) tables?

Sort of like a CVS for data?

That would be cool and all but it implies you keep a copy of every change you ever make to the data. You think the log file gets big now? :hehe:

But then again you do work for Redgate so...why don't you have your company look into that? Seriously.

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 5

David.Poole - Wednesday, May 23, 2018 1:49 AM
For me its a case of "with great power comes great responsibility".
I have been having a similar debate on a related subject. It is basically a "How can we build something to stop people doing stupid stuff" debate. If you try and build an idiot proof system then apart from the problem of the universe instantly upgrading the idiot what you will end up doing is building an immense behemoth of code to prevent all sorts of eventualities. The sheer size of what you will end up building will introduce bugs and these may be worse than the thing you are trying to prevent. It will also mean that simple changes that should take 10 minutes will now take 6 months to deploy.
At some point you have to list your fears, assign risks and likelihoods and take the steps appropriate to those risks and likelihoods. Sometimes those steps are not code based, they are business process based. For example we used to worry about all the things that can go wrong in a large scale deployment. The engineering that used to go into the mechanism to deploy and roll stuff back was huge. Now we simply don't do large scale deployments. We design and engineer our systems to be deployed in relatively small incremental steps.
Then there is "Trust but verify". I look around my office and I can see people who are trusted with 6&7 figure mortgages, controlling 2 tonnes of metal travelling at 70ish miles an hour on a crowded road and bringing up children and sometimes children with disabilities. Yet such adults in an office work under a system that infantilises them.
Yes, the wrong restore can be disastrous but you would be better off making sure people know and understand when to do point-in-time recovery, when not to and what to check for before doing it. If only the priesthood can do stuff then you end up with single points of failure and a genuflection queue.

Heh... love it. As the old saying goes, "If you make something idiot proof, only idiots will use it". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

... The service does this by keeping a log of all transactions for a set amount of time (up to 72 hours). When things go bad after you dropped the wrong table in your production database, you simply pause your application and select the point in time that you want to go back to. Aurora will then pause the database, too, close all open connections and drop anything that hasn’t been committed yet, before rolling back to its state before the error occurred...

It sounds like the old familiar point in time restore from transaction log backups, except that AWS has wrapped a user friendly process around it. Just replace the word "Aurora" with "The Grumpy DBA" and it could be a quote taken from a database operations manual back in 2000.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

DBA_Rob Default port Points: 1494 More actions · Answer 7

This just seems like the next evolution of the accidental DBA to me. Amazon and many other vendors are trying to offer these easy to use systems, and for many smaller companies these are acceptable solutions. For them the low cost of a somewhat idiot proof system is worth not having a skilled DBA on staff even if it means the occasional rework is needed to recover from a recovery. If these companies ever grow to the size they usually hope, that is when these types of solutions become more problematic. To answer your question Steve, out of the two options I would generally prefer the Azure method because it gives me more control and I think I might better understand the consequences of the Amazon method and the limitations of both methods.

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 8

Jeff Moden - Tuesday, May 22, 2018 9:38 PM
To be honest, even though both options are great for what they do, they both suck at what actually needs to be done. That would be to backtrack through the logs on a copy of the affected table.

Feel free to write this one. There are a few tools, but this hasn't proved to be commercially viable as a product. Maybe someone will tackle an open source project.

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 9

roger.plowman - Wednesday, May 23, 2018 6:34 AM
But then again you do work for Redgate so...why don't you have your company look into that? Seriously.

There's no real practical way to do this. Hence, I suspect this is what Amazon does. There are companies that do data virtualization, essentially allowing you a PIT view of your system on a rolling basis. A fancy way of PIT restores through log shipping, but giving you a "database" at that PIT. Of course, more of them are in the 6-7 figure range, so I guess magic is expensive.

We could do something, but it's not likely to be commerically viable. What's better is to teach people how to make backups and perform restores and then recover systems. What would be nice in Azure, and maybeSQL Server, is a button that restores a backup + logs, but to a new name, then renames the old and new so that the app can work, but we have a copy of data that we can move as needed.

Geoff.Sturdy Ten Centuries Points: 1234 More actions · Answer 10

As usual , we have someone pop up and present this as "the latest greatest thing" and yet its is another feature from the Mainframe days. Burroughs (latterly Unisys) Network Hierarchical codasyl dbms , DMSII , had the ability to do archival rollback back in the late 1980s - not for the first time was it useful to back out a data "misstep" by doing a RECOVER ROLLBACK rather than mounting the last tape (yep 6250 GCR) backup of the database and RECOVER REBUILD