By

A few years ago some colleagues and I ended up in a relatively heated debate around the relative merits of Oracle vs Microsoft’s SQL Server in terms of which product was the “best” database.

At the time, I seem to recall I was more on the side of SQL Server than Oracle – probably because I’d been all but indoctrinated into the world of Microsoft  through my developer’s background with limited exposure to other enterprise-level solutions, and at the time our largest database was probably 15-25gb or thereabouts.

These days, however, things are very different & the concept of a 25gb database being “large” has been somewhat blown out of the water by our newest system; which currently GROWS at 20-25gb a day….

When we were debating the technologies’ relative merits, I think we were probably focusing on an extremely small subset of the two products and perhaps not paying much attention to the differences in their underlying architectures or the suite of ancillary software they include.

Both databases have their advantages and disadvantages, but only Oracle offers ASM (Automatic Storage Management).

Consider a scenario..

You have a database which is around 2.5Tb in size, supporting a busy system which has a problem with contention on its underlying storage system.

The fix for the contention problems is to add more disks into the SAN supporting your database (or perhaps add another SAN) & migrate all 2.5Tb off your current storage system onto the new environment.

Downtime on the application needs to be avoided as the system has to be available for use nearly 24/7, and it’s not possible to secure a sufficiently large maintenance window to take it offline to move the data.

I’ve spent some time wondering how I’d do this with SQL Server, and in the end came up with the idea of adding new data files to the database provisioned from the new storage; and then trying to shuffle everything around to move data from the old files to the new. Where I got somewhat stuck was in how to move all the data in a timely fashion without downtime… and ended up concluding that at some point, the DB would need to go offline to move or remove the last data files. Most guidance on this subject suggests simply abandoning the idea of doing this online & requires the DB to be taken offline to use Detach & Attach to physically move the files. Wouldn’t fancy this much with 2.5Tb of data…. Nor would I particularly relish trying to get a 24-hour downtime window agreed to ensure the files could actually be moved in time!

With Oracle on the other hand, this potential headache becomes a pain-free operation which can happen with the application fully up & running, with little or no performance impact on the system. Steps include: provision new storage to your Oracle servers; add the new disk/s to your ASM diskgroup & instruct it to remove the old disks; sit back, wait for it to finish rebalancing (moving data); remove old disks.
Much, much better.

Yes, Oracle may be more expensive than SQL to acquire & tends to need more specialised DBA skills to properly manage & support; but if your application needs to stay up 24/7, your choices are a little limited..

Would I want to try managing 2.5Tb+ on SQL Server? Personally, no.

About the Author

 

Leave a Reply