Both Oracle and SQL Server offer several ways to support disaster recovery scenarios. One of the simplest ways to handle DR in the SQL Server world is to use SQL Server log shipping. Oracle doesn’t offer log shipping as a part of the core product, but it’s possible to set up log shipping in Oracle.
I thought it would be fun to take a look at Oracle and SQL Server log shipping side by side, so let’s take a look!
Log Shipping Set Up
SQL Server wins this one. Reason: there’s a wizard. Even a novice DBA can get log shipping working using the wizard.
Congratulations Oracle professional, you’re setting this up by hand! If you’re at all handy with scripting, once you’ve set up Oracle log shipping, you can port your code to any other Oracle installation and be up and running in a few minutes once you’ve set up the log shipping scripts for the first time. Setting up Oracle log shipping the first time might just take you a while.
Although SQL Server wins for ease of set up, it’s worth reminding you – Oracle log shipping set up can be easily moved from server to server by deploying scripts and changing one or two variables.
Day to Day Operations
SQL Server’s log shipping adds additional requirements for database administrators. Log shipping sets up an extra set of transaction log backup jobs – existing jobs will need to be disabled or deleted. If a DBA takes a log backup outside of the normal log shipping jobs, that can potentially break log shipping. Switching SQL Server into
SIMPLE recover mode will break log shipping – and yes, this is more common than you’ll think.
Oracle’s log shipping works by copying archived redo log files. There are no extra backup jobs to add. Instead, the log shipping jobs copy archived redo log files from the flash recovery area. Rotating out active redo log files will move them into the archive redo log file area. DBAs can take advantage of their existing backup strategy. It is still possible for an Oracle DBA to break log shipping by using
NOARCHIVELOG mode or adding tablespaces or files without adding them on the secondary.
Of course, a DBA can also use the
FORCE LOGGING option to prevent users from switching to
NOARCHIVELOG mode and breaking the log shipping.
One other note – Oracle log shipping will move security between both instances while SQL Server’s log shipping won’t.
Reversing Log Shipping
In both cases, there’s a lot of work that has to be done when you want to reverse log shipping.
With SQL Server, you can gracefully reverse log shipping by using a “tail of the log backup”, but not a lot of people know how to do this properly so they can avoid the overhead of re-initializing running a full backup. In the end, people frequently re-initialize log shipping, lose data, or just get very upset until they stumble upon the answer.
To reverse Oracle log shipping, we can use the same set up scripts, but change the name of the primary and standby servers. Depending on how things are set up, it may be possible to rapidly start the Oracle instance using the fast recovery area to bring the standby online and immediately resume log shipping.
If you’ve set up log shipping using robust scripts, the Oracle log shipping approach can be easily reversed. If you haven’t, reversing log shipping poses just as many problems on one system as the other.
Querying the Replica
It’s not uncommon to use log shipping to provide a readable replica. This can be very helpful when users can tolerate stale data for reports, but you have not been able to develop a reporting schema.
With SQL Server, we have to put the SQL Server into
STANDBY mode. As Kendra Little explains in Reporting From a Log Shipping Secondary in STANDBY Mode, putting SQL Server into
STANDBY mode is necessary to read from the secondary, but it can have make recovery take longer which could, in some scenarios, put you outside of your recovery point objective.
With Oracle, this process is easy – at any point during log shipping, we just mount the database in read only mode using the command
ALTER DATABASE OPEN READ ONLY. Users will be able to read up to the last restored transaction. Once it’s time to start restoring data, you can start the database in recovery mode.
Licensing is annoying and complex. If I get this wrong, sound off in the comments and I’ll do my best to clarify and correct.
With SQL Server licensing, this may change depending on when you licensed your SQL Server. However, you get one “free” standby instance, as long as you don’t read from that instance. You do have to pay for software assurance on the log shipping secondary.
Oracle’s licensing is simpler: if it’s running, you pay for it. Also, you pay for support.
Oracle wins through simple licensing. If you’re using Oracle Enterprise Edition, you have many more options for disaster recovery, and much more entertaining ways to pay.
Compression and Encryption
When you’re moving backups across the network, compression can help meet your recovery point objective.
SQL Server can compress backups in the Standard Edition of the product. This can either be enabled as a default SQL Server level setting or in the log shipping jobs.
Oracle’s compression is only found in either Oracle Enterprise Edition or customers using Oracle’s backup to the cloud feature – Oracle Database Backup Service. However, it’s trivial to leverage in-flight compression when moving files between Oracle instances. In a recent deployment, we used
rsync to move files between primary and standby and enabled the
-z flag to ensure we got compression.
Starting with SQL Server 2014, SQL Server supports backup encryption. Oracle Standard Edition, on the other hand, does not have backup encryption. In the Oracle world DBAs are far more likely to use SSH, scp, or rsync to move files between servers – all of these support encrypting data transferred between servers.
There’s no clear winner here – both compression and encryption are possible with both products, but they are built in to SQL Server. The Oracle approach lets system administrators adopt the practices that are in use across enterprise.
Oracle and SQL Server both have log shipping options available. Both tools are built on different sets of technologies and they both have different strengths and weaknesses.
||Manual, but scriptable.
||Additional caveats to prevent breaking the log chain.
||Mostly automatic. Caveats apply, but preventative measures are available.
|Reversing Log Shipping
||Can be done with rocket science and custom scripts.
||Scripts can be deployed on both primary and standby – reversing requires switching several commands.
|Querying the Standby
||Yes, only in STANDBY mode. STANDBY can make restores slow. Mitigate with complex scripts.
||Yes, in READ ONLY mode. Oracle must be restarted to resume log shipping.
||Requires software assurance.
||Requires a second, fully licensed, Oracle instance.
||SQL Server backup compression.
||OS, storage, or network compression.
Brent Says: WOOHOO, SQL Server actually wins a couple of these!
Kendra Says: Ain’t nothin’ wrong with a wizard, especially when you’re just getting started with something.