I really like SQL Server. Early in my career I had more than one opportunity to take the Oracle path, but there was always something about SQL Server’s ease of use and “good enough” approach to performance and high availability that I liked. That and the fact that most of our customers didn’t have 250K to spend on a “basic” database setup pretty much settled matters.
Although it has become increasingly rare, I am very occasionally hit by an acute episode of database envy.
As of SQL Server 2014 I think there is really only one Oracle feature that I would absolutely love in SQL Server. There are other features I think are cool (genuine scale-out clustering for example). But this the only feature I want that could seriously make me more relaxed about using SQL Server in high availability setups.
Oracle Flashback Technology
Oracle Flashback Technology is best demonstrated via some very simple code:
SELECT * FROM Orders AS OF TIMESTAMP -- SAY WHAT!!!! TO_TIMESTAMP('2014-04-23 09:30:00', 'YYYY-MM-DD HH:MI:SS')
Now – allow that code to just detonate in your brain for a second… It’s allowing you to query the live system as it was at some point in the past.
In day to day development use that code could probably be described as “quite handy”.
In a VLDB, 24/7 environment that code is absolute feature dynamite.
Why does this matter?
To picture why this feature is bordering on essential in a VLDB 24/7 environment, imagine what you would be faced with doing in the following scenario:
- You have a 1 Terrabyte OLTP system that needs to be available 24/7
- A power user or developer advises you that they have accidentally modified a substantial number of some extremely important records
- Your primary 24/7 system is now offline or at best severely impaired
What are your options in SQL Server land?
- You have a highly available system using some clever combination of clustering, synchronous mirroring and maybe even asynchronous mirroring to a secondary data centre. However, by the time you find out about the problem the corruption has already been sitting replicated at the secondary location for half an hour – so you’re bang out of luck there.
- You have a policy of using database snapshots when applying critical database updates internally. However, this was not a scheduled update. It was a power user gone rogue and so there is no useful snapshot that can be used to help extricate us from the situation.
- You are backed up to the hilt and could theoretically identify the exact moment the corruption occurred. You could even theoretically recover to just before that point. But it’s a 24/7 system – you can’t just stop the business whilst you perform a restore over the live database.
- You could recover to a secondary location and repair the live system by hand using T-SQL. But, we are dealing with 1000 gigabytes of data. Do you have a terrabyte of SQL Server storage sitting around for just this eventuality? Assuming you do, do you have an abundance of time on your hands where you can sit back and wait for a restore to complete? It’s going to take many, many hours so I hope the answer is yes!
The bottom line is that SQL Server does not have any good options for this sort of scenario.
Off the top of my head, to have any sort of solution at all to this problem you could look at something like log-shipping-with-delay to give yourself at least a fighting chance. I’ve also heard of people taking hourly snapshots using schedules and scripting, just in case someone deletes something. That doesn’t make me very happy either.
Unfortunately the options available do not even come close to the simplicity and immediacy of Oracle’s Flashback feature.
I don’t know the particulars of how Flashback is implemented in Oracle. But however its done its smart as hell. It’s also the one remaining feature I secretly covet for SQL Server and I really hope MS do something similar one day.