SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pause and Backtrack


Pause and Backtrack

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
Comments posted to this topic are about the item Pause and Backtrack

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dave Poole
Dave Poole
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53646 Visits: 3911
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
www.simple-talk.com
pabechevb
pabechevb
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 172
I totally agree with you, Steve.
roger.plowman
roger.plowman
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7108 Visits: 1781
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
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874
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". BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95138 Visits: 13840
... 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.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
DBA_Rob
DBA_Rob
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1299 Visits: 382
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
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
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.


Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search