Tuesday, June 15, 2010

Developing Your DBA Skills Further Thanks to Experience with Multiple Relational Database Management Systems

Any DBA who really understands databases and SQL standards can make out with other RDBMSs quite well, and if they have not tried yet, I recommend it for the reasons I shall discuss below.

Specialists maintain, with reason, that you cannot be a Master/Mistress of multiple database management systems.  Fine, if you have decided that for your future, that there is not the possibility of being proficient in another system, then you have closed up your options before even trying perhaps(?).

Just in the same way as knowing multiple languages can help you know your own language better, I believe the same goes for database management systems.  This competition between vendors of varying DBMSs allows for great features to be passed from one database engine to the other and benefits us, the DBAs, greatly. The benefits of taking the deep step into another RDBMS can further enlighten your approach to resolving problems or creating unique solutions that embrace a resolution no matter what source they come from.  

After several months working back with Ellison's baby (trained on Oracle 8 in 1998), Oracle 11g and its solid architecture are impressive.  I especially appreciate the recovery approach Oracle takes, explicitly the redo/unlog log files: relying on the SCN (system change number, defined during a checkpoint) for bombproof recovery. The real issue with learning multiple backup systems running on MSSQL is that you could lose your backup recovery chain (unable to match LSN numbers) with mixed Tape/Disk backups, whereas with Oracle’s use of the SCN, this is not an issue.  The hardest part initially, was learning a whole new set of Acronyms related to Oracle's architecture for example: SMON, PMON, DBWn, RECO, ARCn et al.

Oracle Developer 2.1 is a pretty cool management tool also with easy result set export to XLS/CSV/etc., although I prefer SQL 2008's Management Studio. But also not to be ignored, is the excellent web-based Enterprise Manager from Oracle, which can take a bit of getting used to – with respect to finding your way around – but is very rich once you are familiar with how to take advantage of it.

Seasoned Oracle DBAs I enjoy working with have mentioned the lack of spooling in Oracle's developer (UPDATE, actualy spool does work fine in O.Dev 2.1), and after going back to using SQL+ for a while they have a very good point.  Oracle Developer and SSMS always have the code / results split up (unless in SQL DMO?), which doesn't always make it easy to associate the error with the section of code you are working on (unless you click on the error in SSMS report pane that is).  Setting the Spool on and off after working on production provides pretty bomber evidence for auditing operations on your production databases, or results during testing and development.

In a future post I will discuss some fun I have had with MySQL – and unfortunate tendency I have found also, that MySQL developers (who have worked very hard to build their system, and are naturally defensive) will ask the DBA for help in desperation, and as soon as the access is sorted, it only takes a short time before the real fixes become obvious and the developers’ knee-jerk reaction becomes to circle around their team in defence instead of accepting DBA intervention (we are there to help (!), and ultimately improve the bottom line for the company, or success of the respective project).  Anyway, that is par for the course often enough for DBAs, as mentioned to an extreme in my last post.  

PS enjoying a little work with PostgreSQL also recently, converting a schema to SQL Server and Oracle.

Really enjoying the tool SQuirrel SQL Universal SQL Client Version 3.1

No comments:

Post a Comment