SQL Server

The only Oracle feature I actually still want

Introduction

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.

Advertisements

6 thoughts on “The only Oracle feature I actually still want

  1. for Oracle Flashback query, only drawback is that you need to have separate archived log thread, and large free space where that log thread will be placed (depending of size and change frequency on database in question). But, today , with huge storage technologies on the market, that is not a big deal.

    1. Yeah – unfortunately that’s just the price of doing business if you want to be able to rapidly recover. A happy medium might be some sort of configurable snapshot mechanism taking snapshots every 15 minutes (for example). I’ve heard people allude to the idea that thats at least theoretically possible with SQL Server snapshots, but I’m not sure what MS would say if you told them you wanted 96 snapshots on a large database. I could definately believe they would tell you not too!

  2. Nice, but easier to hang the developer or power user by his toes. 🙂

    I think I’d rather reduce risk by not letting users have that power, I know, very difficult, but usually a good first step.

  3. There’s a point missing: Oracle Flashback only works for DML data changes. If a user truncates or drops a table accidentially, the data is gone for all times. Apart from that i appreciate the flashback feature and use it nearly everday.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s