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
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.
Summary
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.
| SQL Server | Oracle | |
|---|---|---|
| Set Up | Wizard. | Manual, but scriptable. |
| Operations | 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. |
| Licensing | Requires software assurance. | Requires a second, fully licensed, Oracle instance. |
| Compression | 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.
10 Comments. Leave new
Hi Jeremiah! Congratulations by this article! Very good content.
I would complement the “SQL Server” side with an option to Script the Log Shipping Creation!
I have one customer with hundreds of databases with Log Shipping. If I had to recreate some of them, I probably would need expend some time, executing the Wizard for each database. Other problem on the Wizard is the possibility of doing something wrong.
To simplify my work, I scripted the entire Log Shipping creation on the SQLCMD format! This way I can execute everything that is needed on each server, completing the Log Shipping creation, just changing some parameters, like the DatabaseName.
Best regard,
Hi,Alexandre!
Something you could share how to do ?
I got over 1000 DBs that I thinking of setup logshipping on.
Thanks, Magnus
Hi Magnus,
The entire script I can’t share, sorry. But follows my advice:
– Fill the entire Wizard and use the Script button
– On Management Studio, go to the “Query” menu and check the option “SQLCMD mode”
– Verify parts of the script that you need to execute on each server
You basically will use “:setvar” to declare variables on SQLCMD mode, and “:connect” to change your connection between primary and secondary servers.
Maybe in the future I’ll create some channel to share this kind of script.
I expect my advice helps you in some way.
This is great advice for anyone – if you want to automate anything in SQL Server, complete the task in the GUI first, but before you click implement it, click on the “Script” button instead. SQL Server will happily spit out the T-SQL to a new window and you can automate that until you’re blew in the face.
Thanks for sharing this, Alexandre!
Setting-up and managing an Oracle standby database is a lot, lot, easier these days, thanks to Oracle Enterprise Manager: http://www.oracle.com/technetwork/articles/oem/havewala-odg-oem12c-1999410.html
But, even at the command line, it’s still pretty easy, owing to Data Guard Broker: http://docs.oracle.com/cd/B28359_01/server.111/b28295/concepts.htm#i1005777
Indeed, for customers on Enterprise Edition, this stuff is only a click or two (or even a simple script) away. And even without EE, setting up log shipping isn’t that difficult, especially once it’s been scripted for the first time.
On sql 2014, std ed, it is not completely clear that, via wiz, logs can be encrypted. (Actually… It is clear… They are not encrypted.)
One advantage Oracle has is that their “log shipping” (aka “Data Guard”) can be near real-time (similar to SQL’s “always on”). You don’t have to wait for archived logs to be generated to “ship” the log. Just create standby log files on the secondary and when you start up log apply services using “recover managed standby database…”, change it to “recover managed standby database using current logfile…”. Now the log writer process will be used to ship transactions in real time as opposed to waiting for the archiver to do it every 30 minutes or so.
We “log ship” a moderately busy Oracle database between two data centers 1000 miles apart using “Maximum Performance” mode (the default) and rarely see the standby fall behind the primary by more than 1 second.
You can actually set up the Data Guard using Oracle Enterprise Manager. Its not as simple IMO as using the SQL Server wizard but it’s much easier than scripting it all by hand.
OK. Article is OK. But…. try some experiments, I’ve tried in older version of Sql server and oracle.
For example:
1. Add one (or more) file to file group (MS SQL Server) or tablespace (Oracle)
2. resize file/shrink file in oracle and in ms sql server.
I don’t know, how it is now, but about year 2010 in this example situation with sql server you would be in a stress situation.
In Oracle rdbms HA solutions are avalible for many years and used by many people.
regards,
If you’re choosing your database based on how quickly you can shrink files, you certainly have some interesting priorities.