finally, something worthwhile!

Most of you folks would have heard all the 11g hype: we've been bombarded with it for weeks now!

Almost all of it is just more marketing blurb, rehash of old functionality with a new gloss paint, same-old-same-old "you don't need no dbas" nonsense, etcetc.

However, IMHO this is truly amazing!

Thanks for pointing this out, Patrick. If it works indeed like this and Doug Burns says it does, it's the only thing I've seen so far in 11g that makes it worthwhile for me! It might indeed prove to be tremendously useful in convincing our development folks to look ait it for a future deployment.

For once, something that is truly useful! Instead of the usual "replace the dba with a ton of complexity" inane rubbish...





Anyways, been a while without some light stuff thrown in.

This little fella


showed up on our driveway this afternoon. For some reason I can't fathom it chased everyone else off including my daughter and her friends, yet allowed me to get so near I could literally touch it!



You might think they are harmless? Think again:


those two spurs can hurt real good, believe me! And yet, it just kept looking at me while I spent an entire roll of film on it and then followed up with some more digital images.
Go figure?


This:


was yesterday: Harry Potter mania at Borders in Maquarie Centre, one of my "bookworm haunts". I was lucky to go in very early: got out with the book long before this queue formed!


Fun weekend, this one.
Catchyalata, folks!

A vs B, the grand final

Sorry for the delayed conclusion folks, been incredibly busy with a Peopletools 8.48 upgrade. Did you know that DESC indexes are actually FBIs? Create one and then check out the USER_IND_EXPRESSIONS view!


Was quite surprised when suddenly nearly 8000 indexes sprouted an entry there with only the column name string as the expression: didn't even know that was possible. But yes, that is a DESC index: a special case of a FBI. Live and learn.


And boy: do they EVER break the 10.2.0.2 optimizer! Let's just say the warning is red letters, in the 8.48 release notes. Near the end, well away from the obvious place: "database dependencies". But still there, nevertheless.


One cumulative patch and one special patch later and I'm still fixing custom views that completely broke the optimizer when using DESC indexes!


And then Oracle's marketing gets stroppy when I call their latest software "incredibly fragile and buggy!". Seriously folks: I still have to worry about optimizer bugs, with FBIs and DESC indexes losing query rows in 10.2.0.2? You gotta be joking!...




Anyways: on to bigger and better things. Hopefully.



What else did SSr9 impress me with?



Well, it's hopeless at handling online reconfiguration of disk space - why oh why, Microsoft, did you stick with the "drive letter C:, D:, etc" nonsense? It sucks big time: it stops anyone dynamically reconfiguring a SAN, it stops one from effectively using a NAS for dbs, it frustrates and irritates dbas and sysadmins who have to constantly fight "drive letter starvation", etcetc. So much for HA right there, guys!


It also sucks - BIG TIME! - at space management inside the db. Again, sorry SS folks: but you got no clue about what one can do in Oracle - and db2! - to efficiently manage and handle large volumes of data that are hit by large volumes of updates. SSr9 and all prior releases are hopeless in this field. And yes: "rebuilding indexes" is a VERY POOR solution for anyone pretending to have a HA solution... I won't even go into the incredibly efficient and flexible partitioning in Oracle!


On the other hand: it EXCELLS at simplicity of administration, at ease of management of mixed development/test environments, at setting up multiple environments on each server, at integrating with a large range of other Wintel services. Mostly due to the incredibly good toolset included in the base price. But also due in great part to the very well thought out and balanced feature set.


It integrates so well with the Wintel architecture there is simply no hope in heck of anyone else catching up in this field. Seriously folks: you MUST give SQL Server Integration Services - SSIS - a look. It is so good, it hurts to even think about it: kid you not!


It is also extremely well priced: the base feature set of its various configurations is very well balanced and the EE version is not brain-damaged like Oracle's: it includes EVERY bell and whistle one can think of. Not like Oracle's EE, which will need the partition option - separate cost. Then the OEM/Grid ASM/ADDM/etc tuning paraphernalia - you guessed it: at additional cost. Etcetc: you get the picture...




So, what else is SSr9 good at? Well, I reckon it's a strong candidate for the title of small to medium size businesse - aka SMB - database of choice.


Joel Garry then asks the obvious and very pertinent question in the last post: "what is the definition of SMB?".



Yeah... You suck, Joel! :-)



I've been thinking about it and the naked simple truth is: I don't know! You see, in the universe of db users I don't think there is such a thing as a well defined concept of SMB.


Why?


Well, I'm reminded of the last site I was a remote dba for: three Peoplesoft/Oracle dbs. About 10 SSr8 and MSExchange databases. About 100 users online at any time. Is this a SMB? Peoplesoft is the base of their business and is quite large, their online user base is incredibly small. Their IT budget is HUGE: without the Peoplesoft/Oracle/San/Unix servers, they would be dead as a business.


Then where I work now: a large JDE AS400 db, a couple of small(ish) Peoplesoft HCM and EPM databases, a huge DW in Oracle. Then the rest is all either dev/test Oracle, MSDE or SSr8+9 small production databases and a little bit of Notes sprinkled around. About 4000 users possibly hitting everything. Heaps of cross-db traffic with staging databases all over the place for a multitude of departmental applications. Yet we are a small concern in terms of IT, certainly our yearly budget is. Heck, we have 2 dbas who manage to take care of everything between Lotus Notes to AS400 db2, Oracle 7 and 10gr2, SSr8 and SSr9 and MSDE!


And the place I worked at before: search engine marketing company with three main prod Oracle dbs, around 100 employees around the world, yet pumping, responding and collecting search click traffic - that's right: google, yahoo, altavista, inktomi, etc - at the rate of 20GB in less than 6 hours. All done with commodity servers, Linux 32-bit and SE Oracle! Is that SMB? Well: it resoundly beats very large telco dbs I was doing 6 years ago with VERY BIG iron...


Yeah: hard nut to crack, Joel. But I'll give it a go.



To me, a SMB is a company that can't afford the large - in-house or outsourced - development teams we see in larger concerns. Their database size is really not relevant, apart from the odd abnormal case.


These companies have small development groups mostly concerned with customizing off the shelf applications. And can't afford the incredibly expensive and grandiose "fusion architectures" that suddenly become "frameworks" - the lesser said about those, the better...


Nor can they afford the economies of scale to outsource/offshore everything: their volume of development hardly justifies the small teams they have.


What are the db solutions for these SMB companies then?


Well there is the "proprietary" route, with Wintel as the main solution and of course SSr9 as the main database environment. Any company with a large and established dependency on Wintel desktops will be looking at SSr9 primarily, and with very good reasons for doing so. Sorry mr Oracle but you had your chance in the Wintel arena and you blew it: the integration of SSr9 with the rest of Wintel is so well done and established that your "web architecture" just simply looks ridiculous by comparison. As for "grid", forget it with Wintel: no one wants that sort of complexity in that environment!



Then we have the "open-source" solution. This is the MySQL/Postgres territory. With a large base of development solutions to draw on, from the astonishingly efficient script+Perl+C solutions to the more sophisticated Python/php/ruby/web server. These companies will have small but very technically competent and experienced development groups. They might give Oracle a second look but odds are they'll pick MySQL or Postgres for obvious cost reasons.


And then there is the special case. Like the search engine marketing company above. Quite frankly for that sort of performance requirement there is only one solution: Oracle, backed by a large SAN or NFS/NAS storage solution. They will not have a large development group, so no j2ee there for sure: more than likely the team will be very competent in a specialized field like Perl/Apache/C integration. The results will show incredibly cost-efficient applications delivering amazing performance. But this kind of company is rare and as they say in fishing circles: "there is a lot of water in between them"...



I may be wrong but it'll be interesting to watch the constant "hassle" of Microsoft in the db arena - larger market share growth rate than db2 and Oracle COMBINED, for the last 5 years...


And see how Oracle will respond.


IBM is basically out of this race: db2 is simply a non-event nowadays outside of their own locked-in user base. It's finally showing very clearly in the market share figures but it's been bleeding obvious to anyone with half a brain for quite a while.


Remains to be seen how Oracle will react, but react they MUST! I've got a feeling Microsoft hasn't rested on their achievements and SSr10 - aka SQL Server 2008 - will have a LOT of improvements in obvious places compared to Oracle. It won't take much to start competing with the big O in the serious size/performance arena and looking at what they achieved so far, it's gonna be fun to watch!


Finally: some serious competition. Who knows, the big O might actually wake up and jetison all the "fusion" flights of fancy and start delivering quality and solid software?


Well, that's it! Apart of course from any comments. Hopefully, I'll be able to go back to my usual light mix of photos and technical stuff very soon. Got a stack of new material to show. As well as a lot to revisit, mainly to do with Kevin's incredibly interesting series of posts on SANs, NFS and NAS.



Catchyalata, folks!

A vs B part II

OK, so what is the average day for a SSr9 dba?



Among many other things, for us it starts with reviewing the email trail. Most of our SS servers are configured to send us a daily summary of errors if any, plus a summary of any scheduled jobs that have failed. Same with Oracle, db2-AS400 and Notes.



Our operation involves extensive extraction off AS400 and Oracle-based main servers into either ETL staging areas for the Oracle DW and/or reporting - and OLAP - server staging areas for senior management and the various marketing groups. Most of these are taken care of by scheduled jobs that kick-in at various times of the night and early morning, with a few lingering on to mid-morning. In all we have about 30 SSr8 and SSr9 instances, four main Oracle dbs and one AS400 DB2.



Now, where are the 200+ databases I talked about? Well, this is one of the interesting aspects of SSr8 and r9: you can actually run more than one database in a single instance.



By "database", I mean: datafiles PLUS logfiles! Each group of these constitutes one "database" in Microsoft parlance, and you can easily fit them into any db service in a box, anywhere and at anytime: all the metadata for each database lives in the database itself, not the instance. Ie, not in a global "system" tablespace, like Oracle's.



As a result, interesting possibilities open up. For example: the logfile itself is unique to each database. As such, you can certainly have a critical db with full log archival and recovery options plus standby and mirroring, sitting side by side with another that merely has token recovery and no HA whatsoever, all in the same instance and server box and without fear of each trodding over the other.



What is even more interesting: because you have separate logfiles, you can actually split them across fast disks/LUNs where appropriate and have no interference between the two or more types of database in that server, or fear of one starving the other of logfile access.



This redo log access starvation is a constant worry in Oracle installations running more than one application per instance. Just try to run two or more Peoplesoft applications in the same Oracle instance, with their 49000 objects potentially updatable in each! No way, isn't it? Matter of fact, do you know of ANY site running more than one Peoplesoft application per Oracle instance? Thought so, and for a great many reasons! More on that later...



This indeed is why we have 200+ databases to take care of: most of them are in those 30 odd SSr8 and r9 servers. With an Oracle setup, we'd have required a lot more instances and a lot more licences...



This is indeed one of the aspects of SQL Server I'm finding most endearing. The ease with which we can detach a database from a server instance, zap it across to another and attach it back is just something that has to be experienced.



It beats all the transportable tablespace paraphernalia from Oracle by a country mile! Yes, I know RMAN can do more or less this as well. Look, I'll take a right click in SSMS any day, over a session with RMAN+"find me enough space in a FRA somewhere+copy+RMAN again"!



As is, we are constantly being asked by developers and testers to provide yet another copy of a database here and there for their work. Piece of cake with SSMS: right-click to detach the database, zip it off to somewhere else, r-click again to reattach, unzip and do the same in the target system. As easy as that.



What, you don't want to remove access to that database while you detach-copy? Well, just right-click on "copy database": all online concurrency issues taken care of for you, automagically! Or you may want to take a full online backup, just in case. Your choice. Once again, r-click once and all is done. Beat that, RMAN + grid + OEM + web server + java whatevers!



Call me crazy, but this little feature is such an advantage for an environment like ours that I can't but wonder what the heck is Oracle on about with their "replace the dba" crap.



How about making life EASY for the dba for once, mr Oracle?


How about a tool release that actually addresses the day-to-day needs of average dbas, instead of more "replace the dba" inane crap?


But I digress...



So, what else do we do? Well, SSr9 and earlier releases are miles behind what Oracle does in terms of kernel functionality. We are still stuck with stuff I stopped worrying about in Oracle since release 6, nearly 20 years ago...



What are these? Well, rebuilding indexes for example. Indexes being of such capital importance in clusters a-la Microsoft/Sybase, they do indeed get messed up quite often.



There are options to mitigate somewhat the effects of certain types of workload, and some application designers are indeed very cautious with the way they do cluster updates. But where that is not the case, or in mixed workloads, the effects are indeed bad. Let's just say we do spend an unusual amount of our time chasing up indexes gone crazy and fixing things up.



Once again, SSMS comes to the rescue: the rebuilding of an index is another case of trivial "swish-and-click". But to me at least, I can't help thinking in this day and age Microsoft could have done a LOT better than this! Let's just say that object space management is not SQL Server's forte at the moment! And leave it at that...



Other things now.



Optimizer:


Optimization is absolute child's play compared to Oracle's. Sure, with things like AWR - at extra cost, of course - and such plus the zillions of optimiser choices and tuning capabilities of Oracle, a lot can be done. I suspect however that very few sites out there would have invested as much as Jonathan did for example in understanding the Oracle optimizer! I know for sure very few do anything more than the usual "cookie-cutter" analyze-every-night. Which is wrong, but that is what they do...



Compared to that the SSr9 optimizer is sheer simplicity. Mostly because it is, indeed, very simple! And there is a virtue in that: not every site out there is amazon.com or needs that sort of performance. A casual "optimize db" is all they need. Nothing wrong with that, in my book.



This simplicity also carries another advantage: when you upgrade from say SSr8 to SSr9, I have yet to see one case where changes in the optimizer completely mess up running applications.



On the other hand, the lesser said about Oracle optimizer upgrades and their impact on running systems, the better...



Snapshots:



These are somewhat similar to Oracle's flashback. But with a lot less functionality. While the Oracle flavour allows us to flashback to any point in time covered by the disk capacity, the SSr9 snapshot is a simple once only event. In small words: once you establish a snapshot point, you can reset any object in the database to THAT point. NOT any point between that and present.



Basically, what it does is to create another (sparse) database in the instance where a single before image of a row is copied once it gets changed AFTER the snapshot point. Want to have various flashback points? Well, take various snapshots! They really don't take much space, the before image is only taken IF there is a change to the original row.



I've found this very useful for resetting test databases without having to refresh from an entire copy: just take a snapshot after the refresh and reset once they finish one cycle of testing. Your snapshot database will grow only by the number of rows changed and only once per row.



Security:


Well, let's just say that the legacy of years of neglect by Microsoft of the security aspects of its OS reflect very badly on what can be achieved with SS.


I don't know what drug that Litchfield guy was on when he claimed SS was more secure than Oracle, but it must have been something very potent!


Excuse me, but the guest account enabled in the master db, by default? Hellooooooooooooo, anyone home??????????


Unreal, eh? Well, it's like that all over the place.


Yes, of course: you can tighten it down. Wanna bet few will bother?



Let me tell you just this: I've had two security audits in our site so far. NOT ONE of them clicked into the obvious holes I can identify now, after ONE course. They will be patched up soon. And the security auditors will get a grilling next time they show up, if they don't detect these changes!



SQL Server Integration Services (SSIS):



This would deserve an entire blog entry. Let me tell you folks, there is NOTHING like it in Oracle and its sorry-arsed web-based dba tools!



Let me try to explain: fully functional process-object editor where you can pick objects, drop them into a map and interlink them to perform as simple or as complex a function you might want - without a single line of code anywhere - and put it into a scheduled job. Or convert it into code that you can stash away for future use/editing!



All done with "swish-and-click". All easy to pick and totally intuitive. Took me half an hour to go from "duh?" to a fully functional "transform/extract data into flat file csv", with full recovery actions, fully scheduled, full email/pager alert interface, without a SINGLE line of code written anywhere except the SELECT statement.



Given that most of the inter-database work we do is precisely this sort of stuff - transform/extract then load into file for further processing - I can see exactly what will be used in future in our site for this type of task! Hint: it won't be an Oracle tool...



And it's not just "flick data into a file", there are hundreds of .NET publishers we can engage through this interface!



Mind-blowing stuff. Microsoft is definitely on a winner with this. It's THAT good, folks! And I don't say this very often about anything...



Oracle Java folks, your web-based "tools" are just NO MATCH for what can be achieved with this. In fact, it would be nice if you pulled your heads out and gave it a long look: you might finally learn what ease-of-use is all about...





More to come later, mostly on some of the glaring flaws of the SSr9 engine itself. And some of the other good points.



I haven't touched on schemas yet, for example. Yes, SSr9 has schemas and they are done PROPERLY, not that half-baked rubbish we got in Oracle.



Plus what our roadmap on databases looks like in the foreseeable future.

A vs B, part I

Just finished the first SQL Server dba training.


There is a lot to be said, so I'll have to split this into multiple posts. And I still NEED to catch up with Kevin's important stuff on SAN's and NAS/NFS! I need 48 hour days...


Anyways: those of you who know me from the c.d.o.s days will recall I didn't use to take lightly to Microsoft's wild claims about SQL Server. And that was definitely the case until SQL Server 2000.


That was then. Now, there is SQL Server 2005. Or, as I prefer shorter monikers: SSr9. Because it is indeed release 9 of this product.


And to me at least, it looks like Microsoft has for once actually LISTENED and done quite a few useful things with the product. And it has done so in a way that is not only easy to use but also DIRT EASY to learn!


Instead of the usual marketing patina it uses to mask clear inadequacies of its product range.



So: what is it that strikes me as so much better?



Many things. I will resist as much as possible extensive, exhaustive "A vs B" comparisons: it is really not applicable as in my view SQL Server is targeting a different market than Oracle is in.


To Oracle's clear detriment, but that will be the subject of a later post.


Although of course the marketeers from both sides will claim the "same universe" and all sorts of "competitive advantages" for each side. Please, just ignore that mob: they are truly useless, in this day and age of mixed supplier data centres.


The naked truth is: at the base kernel product level, Oracle walks all over SQL Server. Simple as that, folks.


It's the block size tuning, it's the fine memory tuning, it's the fine optimizer nuances, it's the much better control over disk space, it's the much, much better space management and maintenance, it's the...


like I said: I'll refrain from extensive item by item, point by point comparisons.



HOWEVER...


Life is not just the base product, folks. It's much more than that.


Fact is: Oracle's fixation with the "web paradigm", java-based and web-based technology for its ancillary toolset has left them at a clear disadvantage in what regards the flexibility and ease of use of things like OEM, grid, rman and all such ancillary tools.



Which you MUST learn in detail in order to effectively extract all the power out of all the base product advantages!


This is compounded by the much more complex administration and maintenance required by Oracle's advanced features, compared to the much more basic SSr9 ones.


Don't get me wrong: if you invest the time - and money: never forget those two are closely linked! - to train in the somewhat cryptic and ultra-complex Oracle db management tools and their quirky UIs, you can definitely take advantage of the power inherent in Oracle's superior implementation.


But if like so many others you have two or three management layers above you breathing fire down your neck every time you can't flick the mouse and solve the world's problems with a few "swish and clicks", you will absolutely, positively and completely LOVE SSr9's management UI: the SQL Server Management Studio.


For starters: it's a true GUI. With a functionaly rich and truly friendly user interface. Not the rubbish "web paradigm" we've had to cope with in Oracle for the last 5 years.


This may come as a surprise to the web-boffins inside Oracle, but the vast majority of the web is - most definitely! - NOT a good example of user friendliness!


In fact, the opposite is much more likely to be the case!


I'll take the ease of use of the Windows or Linux GUI over the "web paradigm", any day or night. Without even flinching!


For example: there is a HUGE difference - for the better - between generating a command-line script equivalent of my current dialogue box with a single click, and the "click here while holding that and remembering to go back three screens - oh, did you remember to flick the checkbox and the radio button two pages ago?" confusing and unfriendly interface of the web-based OEM and grid.


By the same token: in Oracle, you know of course how many process steps I have to follow to backup a given database online. I have to check first if it is in archive log mode, don't I? And then make sure that I remember which and where the archive redo logs are, taken during the backup.


Don't I? Ah yes: RMAN, I hear you say?


You mean that quaint command line stuff with "connect target as /" - ain't that user-friendliness?... - before I can do anything else?


What was that again? Ah yes, the ORACLE_SID. Sorry, I forgot to set it first. Darn...


And have you checked the RMAN manuals of late? Four. No less!...


Well, it's again one single right-click with SSr9. Simple as that, folks. r-click, backup. Up comes the dialogue. With EVERY SINGLE POSSIBLE option available to control backups. Cross-checked and automatically consistently set, based on the current settings of my instance. Available for me to fine tune via radio buttons, drop-down lists and all such. Then we just click the OK button and away it goes. Or we just click it into a schedule. Which can be fine tuned right then and there, no need for other screeens. And associated with alert emails or pagers, should it fail for some reason. All right there.


Yes, it includes all the options for the equivalents of mirrored dbs, ASM, dataguard AND replication as well! In other words, I don't have to wade through half a dozen disjoint screens and 14 manuals in order to be able to handle ALL that possibly relates to the subject of backups!



Or - and this is the REAL corker! - if I'm still a command line afficionado, I can then click the dialogue box title button and bang!: up comes the script to do exactly the same in the command line! All options toggled in the gui, ALL OF THEM, including any necessary scripted and/or coded cross-checks, all written down for me to keep as a project, a script, a history, a teaching material!



Oh, and to get this fine UI, all I have to do is double click on an icon in the desktop. I don't need to start a web server on a cryptic port and a java-based agent residing in "/whatever/get/the/heck/out/of/1.2.4/my/life/blahrkljkajsdkf.jsp - but just for this particular version, because for the next one we'll change the whole directory structure again just to derail your knowledge!"



How come I didn't know all that, you say? Oh, you mean I didn't have time to read the umpteen install guides and release notes? Tsktsk, a dba that doesn't have time to read all day, where has that ever been seen? One would think you'd have to take care of >200 databases, no?



Well, yes: I DO have to take care of >200 databases, thank you very much! And their users and their user's managers! I simply do NOT have time to read 100 manuals for every new point release of your software, mr Oracle!


Sorry folks, but there is simply NO COMPARISON!


The Oracle marketeers look surprised when I tell them the "web-based paradigm" is nothing but disguised complexity.
You see, they are constantly watching "well oiled" presentations. Of course that feels like "easy"!


As someone I know used to say:

"Oracle's software runs perfectly!
And is easy to use!
In Powerpoint..."


But there is more. Much more, folks. I'll continue soon with part 2, where I'll look at the average day-to-day tasks of a SSr9 dba in a typical mixed supplier site.