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.
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.
posted by Noons at 7/08/2007 09:20:00 pm
7 Comments:
Is there something like logminer?
there is no "official" logminer package like in Oracle. But there are indeed tools to go through the logs and fish out all sorts of interesting stuff.
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.
Well, with Oracle you can just have several instances running several databases. There are advantages and disadvantages but assuming you have same number of CPU - you don't need more licenses. Do you?
Noons
I am almost a new kid on the block and havnt used SQL Server. But I totally agree to whatever you said about ease of use (specially running OEM thing and detach,zip,unzip & re-attach the DB). Otherwise also (I mean out of this DB world), if we look at GUI things coming from MS, they are just awesome ! There is nothing beating that...
Though they say: Computer is like an Air Conditioner, its useless after you open Windows :D
Sidhu
Oracloid:
not really, running various copies of a database for example is a no-no.
I'll talk - again - about the problem in the next post.
Suffice to say the bug has been around since release 9 and Oracle has simply ignored its importance until very recently...
Sidhu:
You are absolutely right: they are not known for the most stable software...
But what they have done with SSr9 I'm finding surprisingly solid, relatively reliable and quite usable. The security could be improved, the space management definitely must be improved, the ability to run very large databases also needs to get much better.
But for small to mid-size shops? I'll take it anytime.
I know it's going to be a fuzzy answer, but where might you draw the line for mid-size? I've been able to pull the scalability argument for a while, but I'm wondering how true it is these days. I'm referring to MRP/ERP systems, I'm just beginning a major upgrade to the latest and greatest of one, and the instructions are saying things like "set open_cursors to 7000" and have 7.3.2 temp ts creation examples... a 4 processor N-class hp with a M1000 SAN is just about right for this case (SAN is sometimes bottleneck). Is there a commodity SS solution that is equivalent?
Jgar:
Yeah, things have changed, buddy.
The next post will have a lot more on this "what is SMB" thing. You are absolutely right, we need to establish first what is the definition of what we talk about.
It used to be fairly well defined. Not anymore. And disk capacity has nothing to do with it, although it is still a major factor overall.
Post a Comment
<< Home