Paul Karman

Oracle Certified Professional DBA

Home

Contact

Curriculum

Algemene voorwaarden

Tarief

Verhandelingen


Oracle, SQL Server and MySQL

Status of this doc: Still mainly comparing Oracle and SQL-Server in a extremely messy way. I will clean up later after I have gone through some more material. Adding MySQL will follow soon I hope. I get the feeling I might need to rewrite this doc several times since the first version will be from an Oracle perspective while it should be just as much SQL-Server as MySQL oriented.

These three database engines hold a large part of the populair RDBMS market. Sometimes it seems that specialists out of one camp is unable to find anything good in databases out of other camps. I believe each of these RDBMS engines have their own merrits. Each one has advantages as well as disadvantages over the others. The differences can be based on performance and reliability but also on license costs and support. We also hear functionality and scalability often pop up when comparing the different engines. I also realise there are many others that have done the comparisation just like I am trying to do here. I noticed that reading such a comparisation is one thing, "living" it is a whole different game. So while studying these three RDBMS engines decided to write the differences and similarities down. You know the method, learn once, do once, teach once. Putting it to paper, or html in this case, makes me think things completely through. And maybe this paper will help others too to determine when and why a particular RDBMS is the best for a particular environment.

I discovered that I had to dig into my favorite area (Oracle) quite a few times to find specifics on how things work. I think this is a nice confirmation of what we DBA's always have said; Having the knowledge that goes with an OCP certificate is only a minimum bit of knowledge you need to have.

While writing things down I am doing my best to describe differences and similarities as precice as possible. I'm afraid I did some generalistion here and there because otherwise I think the text would just become too much. If you feel or see things are incorrect please let me know.

The RDMBS systems I am comparing at this moment are Oracle 10g and SQL-Server 2008. Soon I'd like to add MySQL 5. I might touch subjects relating to different versions but I try to stick to "the basics", the issues that are of primary importance to a good allround DBA.

If you see subjects are missing, messy or unfinished know then that I am still in the middle of writing it. Check in next week. Or better yet, drop me a line if you would like me to give priority to one or another subject.


Comparisation of Oracle QL-Server and MySQL

  • Archivelog/online redolog - transaction log - innodb binary log

    How do they work and how does old data get cleaned up.
    Transaction log is like archivelog, online redolog and undo/rollback together.

    SQL-Server's Transaction log
    Change are treated as intended so the commit does the actual changes. The intended changes are first prepared by writing them to a separate file (the transaction log) so that a commit still needs to do all actual writes. At commit he changes are written to the datafile (.mdf file).

    Read consistency is maintained by reading the datafiles (.mdf files) that contain the unchanged data.

    Committed changes can be cleaned out of the transaction log either directly after the commit (simple recovery model) or after the backup (full or bulk-logged recovery model).

    Oracle's Undo/rollback segments
    Changes are treated as expected to happen so the actual commit is a formality. Change are first and foremost written to the online redolog files, the original data is copied from the datafiles to the online redolog files, then the changes are written to the datafiles. On commit the changes only need to be marked as permanent.

    Read consistency is maintained by reading the undo/rollback segments that contain a safety copy of the data before it changed.

    Changes can be saved for longer periods by configuring automatic archiving of the online redolog files to archivelog files. (Archivelog versus noarchivelog modus)

    Speed/bottleneck
    As you can read above Oracle writes data 3 times before it is permanent (in redolog, datafile, and undo segment) while SQL-Server only writes it twice (in transaction log and in datafile). This does not make Oracle necessarily slower however. As long as Oracle's bottleneck (writing to the redolog) is under control a commit is instantaneous. All other writing necessary can and will be done at the first convenient moment but it will be transparant to the user. I do not yet know whether SQL-Server has a similar system whereby the user can just continue work after the commit while changes in the transaction log are written to the datafile.

    For backup and recovery the difference applies too.
    To store changes for a longer period in time Oracle copies the online redolog files to archived redolog files while SQL-Server simply continues to add to the existing transaction log. Again the same principle applies: Although Oracle includes the extra step of copying files it does this only at quiet moments. After that consequent backup runs don't need to touch the redologfiles while SQL-Server's transaction log will still need to be read and cleaned during the backup.

    In short: Given thesame hardware and based on only raw IO performance Oracle will allow for a higher peak performance but a lower througput. In case of ulimitted hardware Oracle will allow for higher peak performance and equal throughput.

    Actual performance depends highly on how Oracle or SQL-Server handle other important factors like locks.



  • Backup, restore and recovery

    Subject : Oracle -> SQL-Server
    Logical copy: Exp/imp, datapump -> Export/import wizard. The system database "model" can take care of seeding functionality
    Importing exporting data: sqlldr ->
    Manual hot backup: alter tablespace .. begin backup, file copy -> Have not seen command line method yet.
    Automated hot backup: rman -> SQL-Server Management tool
    Scheduled backups: Enterprise manager or scripting -> Scheduling through SQL-Server management tool.
    Tool independend backups: Shutdown database, copy datafiles -> Take database offline and copy files

    The logical copy is not realy a backup. Tools used here are primary ment as a way of transferring data and other objects between databases of thout needing to replace the whole database. These tools are often also used for reorgansations and for seeding new databases.

  • Importing and exporting of data and objects

    All data in a database is typically not all entered by users behind screen and keyboard. Often large amounts of data need to be entered and extracted using other tools.

    The simpelest way to extract data out of an oracle database is just to start an SQLplus session, and selecting the data you want while formatting it in the form you want and dumping/spooling it into an ascii file.

  • Setup of initial database

  • How to move datafiles or database

  • Temp tablespace and temp database

    Oracle has a temp tablespace where SQL-Server chose to create a database to facilitate space to execute sorts and merges. Oracle's temp talespace and all objects within it are kept out of the redologging and archivelogging and backup while SQL-Server's temp database has the same transaction log that works on normal databases. This is responsble for some overhead of course and I wander whether this is the reason why SQL-Server DBAs and developers like temporary tables so much more then Oracle DBAs and developers.

  • The different interpretations of keywords like datafile, database, schema, role and privilege

  • char and varchar

    Both Oracle and SQL-Server know char and varchar alhough Oracle's has a variation on varchar called varchar2 which is thesame in 10 but might change slightly in interpretation in future releases.

    Technically char is a bit more efficiently in SQL-Server then in Oracle since it does not need 2 bytes to mark the end of a field. A char(3) in SQL Server uses 3 bytes of pace while the same char(3) in Oracle needs 5 bytes for the added housekeeping.

    Because there is no difference in space allcation between Oracle's char and varchar there is storage wise no reason to limit one selve by using char ever. The varchar datatype even has the chance to be more efficient. There is however another thing to concider. A string stored as char will be padded to its full length with spaces while varchar is not. This can become a problem when comparing a varchar string with a char string. After all "customer " is not the same as "customer". Even if the developer correctly truncates one or paddes the other there is still the chance that the use of the function to do so unintentionally takes an index out of commission.

    A great advantage of char above varchar that not should be forgotten is that on tables with high activity you can define your tables in such a way that you will have no row chaining.

    I hope that at a late stage I can tell what the expected behaviour of SQL-Server is in similar situations.

  • numbers

    Where SQL-Server has a charming choice between types and names of numbers Oracle basicaly limits itself to one: NUMBER(p,s). Only by defining precision and scale you determine its usability. To conform to ANSI Oracle also recognises INTEGER but this is technically just a NUMBER(38). Then there are the binary_float using 5 bytes and binary_double using 9 bytes and that's it.

    Compare that to SQL-Server who knows these types:
    bigint - 8 bytes
    int - 4 bytes
    smallint - 2 bytes
    tinyint 1 byte
    decimal(p,s) - 5 to 17 bytes
    money - 8 bytes, 4 decimals
    smallmoney - 4 bytes, 4 decimals
    bit - bit (and puts 8 columns in the same row together to form one byte)
    float - 4 or 8 bytes
    real - 4 bytes

  • OUTPUT DELETE, INSERT, UPDATE, Merge and audit trail, upsert.

    The OUTPUT functionality of SQL-Server can catch mutations done during execution of an SQL statement. The OUTPUT clause must be included in the SQL statement. This example statement deletes one record out of one table while inserting it in another:

    delete from mytable output delete.* into mydeletedtable where customer='acme'

    This statement can be used to audit changes in the database. Of course the weak point is that the realy dangerous users can and do change data by not using the standard application. The audit functionality of Oracle would catch that. If I discover SQL-Server also has interface independ auditing I will certainly change this entry.

  • Date and time types

    DATETIMEOFFSET vs TIMESTAMP(fracionalseconds) WITH TIME ZONE

  • Various things

    Null values: SQL-Server specialist seem to be devided about how to handle undefined fields. The discussion is a bit like this: Should we allow undefined/empty fields or not? Shall we put something in there like "-1" Should we make a second table for potentionaly empty fields where the master table just does not have a relation to that second table if we do not have a value?

    As far as I know this is a non issue in the Oracle world, undefined is undefined. In Oracle dealings queries we always catch the exeptions by the "IS NULL" and "IS NOT NULL" clauses and outer joins. SQL Server knows "ISNULL" and "ISNOTNULL" as well as outer joins. Maybe there is a problem further down the road that I am not aware of.

    Yes, there is a NVL function in SQL-Server too.

    Top and rownum relation
    select top 5 <column> from <table>
    or
    select <column> from <table> where rownum < 5;

    Get information about objects
    Oracle: describe <object name>
    SQL-Server: execute sp_help <object name>

    Starting point to get to know the database
    Oracle:
    select * from dba_tables; or
    select * from dba_segments;
    SQL-Server:
    select * from sysobjects <f5>

    Multiple row insert
    insert into mytable (first, last) values ('Paul','Karman') , ('Donna','Karman)
    or
    insert into mytable (first, last) values ('Paul','Karman');
    insert into mytable (first, last) values ('Donna','Karman');

    Views
    Views in SQL-Server and Oracle work mostly the same. There are differences in the extra functionalities.

    Updatable views exist both in Oracle and SQL-Server. I'm sure there are differences but I think at least most of them are not devastating.

    Oracle's Materialized views show similarities with SQL-Server's indexed views but Oracle has some more choices. An indexed view behaves somewhat like a "refresh on commit" materialized view but Oracle can also defer the refresh, schedule the refresh periodically, execute a full refresh or use a "materialized view log" to do a fast refresh whenever the need arises.

    Creating an Oracle materializd view with settings other then "refresh on commit" opens the door to ACID violations. There are however parameters configurable that determine whether an sql query is allowed to use stale data. For instance query_rewrite_integrity can have values as enforced, trusted and stale_validated where setting it to the last one clearly can lead to less reliable results.

    Different kinds of indexes
    SQL-Server's clustered index looks like Oracle's index organized table but need further study.

    Need to look into a few others like function based indexes, reverse key indexes and bitmap indexes.

    Operating systems
    This is so obvious I might just forget it while it is quite important. If ever for any reason an operating system becomes less populair its support will become equally less available. Oracle gets support on the widest range of OS'ses followed by MySQL. SQL-Server is of course only available on a Windows platform and we see Linux taking more and more market share in one form or the other. Geeks get educated not only on Linux on a desktop but also on various populair smart phones. Even Apple's products show a very large similarity with Linux. But it would be fair to say that even if in a future where Windows is less prominent people will find a way. SQL-Server might be rewritten for other platforms and Windows's life span might be stretched in a VM box.

    Attach/detach and transportable tablespaces.
    SQL-Server DBAs appreciate the ease at which it is possible to move a database from one installation to the other. One only needs to Detach the database, copy two, maybe a few more files to another location or machine and Attach the files on this other environment. This looks very simple compared to moving an Oracle database. There you need to find and copy all datafiles, controlfiles, online redolog files and not to forget the init.ora. Next to that a straight copy only works if the paths of all files stay the same. You can adjust those paths of course but there is some work involved, certainly more then SQL-Server's simple Detach/Attache routine.

    but

    Since Oracle and SQL-Server differ so much when looking at the definition of instance, database, schema and table owner one could also compare the Detach and Attach of a SQL-Server database to the move of an Oracle Transportable Tablespace. Just like with SQL-Server does the DBA leave several important ojects alone. The system and sysaux tablespaces are not touched just like SQL-Server's four System databases. And of course for both SQL-Server and Oracle the DBA expects all binaries to be available and compatible between the donating and the receiving environments.

    This is a nice item where one sees the difference in approach between Oracle and SQL-Server. A SQL-Server DBA is more inclined to keep all data of a whole business in one single file while an Oracle DBA is practically raised with the notion that one should try to spread the data over multiple files. Less chance on IO bottlenecks, less change on fragmentation, less work when something needs to be reorgnized, less down time if a file needs to be recovered.

    The most simple difference between Oracle's and SQL-Server's approach is that if confronted with a full volume, an Oracle DBA can just decide to move part of the database over by moving only a few datafiles over of all datafiles that make up the database. This is of course not possible if your database is in only one very large file.





  • Table types

    SQL-ServerOracleSQL-Server ExampleOracle Example
    PermanentPermanentemployeesemployees
    logical tempglobal temp#employeescreate global tempory table employees
    global temp-##employees-
    table variabletable variablein memory tablein memory table

    Opinion of "ask Tom": You rarely need temp tables in oracle. They are mainly there to make SQL-Server programmers feel more comfortable.

  • User defined Functions

    Transact SQLPL-SQL
    create function example
    (
    @a char(5)
    )
    returns char(3)
    as

    begin
    declare @r char(3)='Yes'
    if (@a='smoke') set @r='No'
    return @r
    end

    		 create or replace function example
    (
    a char
    )
    return char
    is
    r char(3) := 'Yes';
    begin

    if a = 'smoke' then r:='No'; end if;
    return r;
    end;
    /

  • Merge/Upsert

    Transact SQL PL-SQL
    MERGE Target AS T
    USING Source AS S
    ON (T.EmployeeID = S.EmployeeID)
    WHEN NOT MATCHED BY TARGET
    AND S.EmployeeName LIKE 'S%'
    THEN INSERT(EmployeeID, EmployeeName)
    VALUES(S.EmployeeID, S.EmployeeName)
    WHEN MATCHED
    THEN UPDATE SET
    T.EmployeeName = S.EmployeeName
    WHEN NOT MATCHED BY SOURCE
    AND T.EmployeeName LIKE 'S%'
    THEN DELETE
    OUTPUT $action, inserted.*, deleted.*;
    		 text column 2

  • The DBA's Dictionary

    Often an activity can be expressed using the same syntax for SQL-Server, Oracle and MySQL while at the same time each rdbms can have their own alternatative way of doing things. in such cases I will try to choose the syntax with the most similarities betwee the three.
    Subject SQL-Server Oracle MySQL
    From the command line, create an SQL script and then run that script and put the output into an output text file.
    		 echo select name+" "+file from sys.sysdatabases >script.sql
    echo go >>script.sql
    osql /U<user> /P<password> /i script.sql /o ouput.txt
    		Oracle
    		 MySQL
    Create a table with a foreign key to another table
    		 CREATE TABLE Orders
    (
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    PRIMARY KEY (O_Id),
    CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)
    )
    subject
    		 SQL-Server
    		 Oracle
    		 MySQL

  • Table mask (21 lines)

    Transact SQL PL-SQL
    text column 1
    		 text column 2

  • Security, for instance Oracle's OS identified compared to SQL server's mixed_mode authentication

    For starters, wih oracle we talk about user accounts to log in to, with SQL-Server we call them login's.
    The standard method of identification for an Oracle database is comparable with SQL-Server's "mixed mode" authentication. Th database can handle users comming in over the network as well as from within the server. The difference is in the application of the username. Create an Oracle user named "paul" and he can only log in using database authentication, create the same user with the prefix "ops$" and now this user can connect without needing to specify an username or password. So Create the databases user "ops$paul" and the OS user "paul" can log into the database without specifying an username and password.

    Be aware that the user accounts paul and ops$paul are two different users as fas as Oracle is concerned.

    In SQL-Server you would select a type of user while creating the user in sql-server management studio. While creating a database identified user you will have to specify a password too.

    Database identified
    Oracle: create user paul identified by paulspassword;
    SQL-Server: create login paul with password paulspassword;

    OS Identified
    Oracle: create user opspaul identified externally;
    SQL-Server: create login MYSERVER\paul;

  • SQL statement performance tuning: tkprof/statspack, SQL profiler and slow query log

    sql_monitor shows the top heavy queries and their execution plans.

  • Network configuration

  • Instance tuning, memory settings etcetera

  • Protection aginst corruptions, logical and physical

    Oracle "formats" its datafiles during creation. This formatting comes with a checksum that get checked while making backups with Rman. Because of that the chance that a corrupt database stays undetected is very small. There is even a init.ora parameter that increases the checks on the checksums even more for higher reliability. One exeption is formed by the temporary datafiles. They are not formatted. They are not backuped either. Do SQL-Server and MySQL have a logical formatting or do they rely on the filesystem checksums? I need to find that out.

  • Undo/rollback internals

  • Table and row locking / lock escalation

  • Resource manager and query governor

  • How to generate emails out of each brand of database

  • Execution plans/performance tuning

    Perfstat, sql profiles, slow query log

  • The bare minimum: console

    sqlplus / as sysdba -> sqlcmd -A -d master

  • Configuration files; where are they and what is the most important stuff you can find in them

  • System/sysaux/temp tablespaces and master/model/msdb/tempdb databases and where is undo

  • ACID -> implicit and explicit commits

    A transaction should be done completely or not at all, nothing inbetween. And while you are halfway making your changes other users should not get to see those changes until they are completed. That is ACID very briefly. Here is where the acronym stands for:

    Atomicity
    Concurrency
    Isolation
    Durability

    What statements make up one transaction is to be determined by the developer since he is also the person to write all check/restart functionalities.

    Oracle uses explicit commits , it only commits when you explicity say it has to. SQL-Server uses out of the box implicit commits and calls them maybe a bit eufemistic auto-commit, SQL-Server commits by default between statements, each statement execution implies there is a commit included. Note: SQL-Server's auto-commit is done at the start of the next transaction, not at the end of the original one.

    I suppose every person has their own opinion about wheter explicit or implicit is the best choice. Experience teaches me this: In this day and age many developers start in IT with a try first, read later approach.... Therein might be an argument for Oracle's explicit commit method.

    One can write large pieces perfectly well working code while using implicit commits. These programs can work flawlessly for a long period of time. However, when somewhere in the future part of a transaction fails the problem might not surfcace directly. For instance a customer could order a part on your business site but the payment might fail. Such an error might go unnoticed for weeks if not months and all that time your customers will get their packages while you do not get paid. The reason for the error is very diverse, it could be a programmatic exeption that was not captured but it could also be a foreign key messing up. Even further beyond the influence of the developer is a simple "out of space" issue.

    Oracle's explicit commit system demands a commit. If forgotten or neglected no change will be stored and if somehow forgotten long enough the database will protest. To be honest a Oracle developer will not be able to write 2 lines of code before he is made to think about when and where to commit data.

    Good SQL-Server Developers will have a lot of dicipline making sure to include begin transaction/commit transaction everywhere where it is necesarry. But as said, a slip can easily be overlooked and because testsystems that can "crash" a transaction between the statements hardly exist it would most likely go unnoticed. Implicit or automatic commits between transactions rely on a good dose of luck of the tester to find bugs.

    Note: Discipline is important. One should always try to hire developers and DBA's that have lot's of discipline and sense of responsibility. Next to that one should build an infrastructure that does not rely too heavily on either.

    How to switch SQL-Server from implicit to explicit Commit? I thought I would find out. So far it is unclear to me whether this can be done at system/database level. If so it might prove to be unusable. A "set implicit_transactions off" in a SQL session in SQL-Server manager can other sessions. An update in a table in the first session without a commit puts another session in wait for a select * on that same table until the first session commits the transaction.

    Anyway, the challenge of implicit transactions is so obvious that I am sure every serious SQL-Server DBA, Administrator and Developer knows it.

  • ACID again -> Transaction Isolation Level

    On an SQL-Server database you can set the isolation level of data compared to other data. This does not exist in Oracle databases. Oracle only has one set isolation level.

    Microsoft splits concurrency problems out in this way:
    1 Lost updates: More then one user updates the same data and the last committed data is stored.
    2 Dirty Read: Data changed but not committed is readable by other users.
    3 Nonrepeatable reads: a user can get different results when reading data again.
    4 Phantom reads: A user can get different result sets when reading data again.

    The Isolation Levels of SQL-Server are configurable in these 4 ways:
    1 Read Uncommitted: Problems 2,3 and 4 are not addressed.
    2 Read Committed (default): Problems 3 and 4 are not addressed.
    3 Repeatable Read: Problem 4 is not covered.
    4 Serializable: Everything is covered.

    There are an uncountable number of differences in implementation so that it is not easy to explain those differences. Both RDBMS'ses need correct usage of logical transactions as important last step but Oracle as explained above enforces this a bit stronger because of the necessity to specify commits. A number of technical choices in implementation also increase SQL-Server's chances to block or slow down overall processing.

    Two SQL-Server [session] settings to remember
    set implicit_transactions on -> turns autocommit off.(you need to commit yourself explicitly.)
    set transaction isolation level serializable -> Expect locking problems.



  • ACID for the third time -> undo tablespace versus the transaction log

    commits and checkpoints

  • Extend management and auto growth. Sometimes hot debates?

  • Tablespaces and datafiles compared to filegroups

    (This part needs some sturdy rewriting. Oracle does round robin but has many other factors to calculate in. Although RAID is already very normal and ASM goes even a step further we still seem to cling to old ideas of disks and spindles.)

    Oracle's tablespaces are very similar to SQL-Server's filegroups, both are a functional description like a Logical Volume in the storage world -> You give a storage space a name, add stuff to it that actually stores data and then start storing data while refering only to the name you invented. Oracle's tablespaces are made up out of one or more datafiles just like SQL-Server's filegroups are made out of one or more files.

    The difference is in the utillisation. SQL-Server will automatically start to spread it's data over the different datafiles within a filegroup as soon as it sees it has more then one file. Oracle will not. Creating multiple datafiles for the same tablespace is simply a way to create more storage space in an Oracle database. The difference is in the DBA's head. When an Oracle DBA notices an IO bottle neck he cannot just add another datafile and expect the database to divide his future data over the current and the new datafile. If confronted with an IO bottleneck the only tool in the DBA's hands is to create another tablespace (filegroup for SQL-Server) and move some objects to this new tablespace.

    Although this moving seems (and is) more work it does make sense in many cases because of the reasons for bottlenecks existing. For instance an index that needs to be rebuilt regularly would create gaps if left between all other objects. The file containing that index would get fragmented quickly. By moving this index to it's own tablespace the DBA can significantly reduce fragmentation and increase performance. To cope with fragmentation SQL-Server has it's data compaction jobs where Oracle, if correctly configured, does hardly ever need this maintenance.

    A SQL-Server DBA who added a file to the filegroup is not likely to have solved the probem either. Only new data will be divided over the old and the new file. This might stop the IO problem from getting any bigger but it does not reduce it. A SQL-Server DBA will too have to create a new filegroup and actively start to move problemobjects to this new filegroup.

    Because of the different way of thinking an Oracle DBA will already at the creation time of a database make a number of different tablespaces, one for tables, one for indexes, one for busy tables, one for large indexes, whatever the design of the application calls for. A consequence of this is also that the Oracle DBA will give the tablespaces and datafiles meaningful names that reflect their content.

    The same behaviour of functionally naming filegroups also happens with more mature SQL-Server databases.

    A note on RAID storage: Experts agree that raid 3, 4 and 5 configurations are not the right choice if it comes to the best cost/performance/redundancy trade off. Raid 0+1 (mirroring+striping) is still the best choice for high performance databases. If you like to read up on this I can recommend you to take a look at this site.

    A summary of the comparisation between Oracle's and SQL-Server's storage:
    * SQL-Server is set and forget, Oracle needs more work during initial set up.
    * SQL-Server attempts to tune IO by spreading the data whether it is necessary or not, Oracle expects you to spread the data when necessary.
    * Both RDBMS engines need manual labor somewhere in their lifetime.

  • Partitioned tables

  • Clustering, High availlability, Performance or, Load balancing?

    Cluster -> Cluster - Fail-Over vs High Availability
    SQL-Server Mirroring = Oracle Standby - process is manual and takes a couple of minutes.
    SQL-Server Fail-Over = Oracle Standby
    SQL-Server Acive/Active fail-over = Oracle primary and standby on each node crosswise
    SQL-Server Federate Database - Each node contains part of the database.
    Oracle RAC -> Cache fusion Database mirroring -> Logical standby database
    Log shipping -> Hot standby database
    Data guard




  • Education, Classes, certificates, examns

    OCA, OCP, OCM and MCTS, MCITP, MCDBA

    SQL-Server DBA education
    I have the impression that the certification path changes for the various SQL-Servers so bear in mind that the information on this website is mainly based on SQL-Server 2008. Similar things have happened too with Oracle's program. I remember RDBMS 8 OCP certification path consisted out of 5 modules that got changed to 3.

    There are 3 directions you can go with SQL-Server:
    1 DBA
    2 Developer
    3 Business Intelligence Developer

    Then there are 4 levels you can get to:
    1 MCTS (Microsoft Certified Technology Specialist)
    2 MCITP (Microsoft Certified IT Professional)
    3 MCM (Microsoft Certified Master)
    4 MCA (Microsoft Certified Architect)

    To call yourself a Certified SQL-Server DBA you would have to pass the two DBA exams for MCTS and MCITP These are modules:
    70-432 TS: SQL-Server 2008 Implementation and Maintenance and
    70-450 PRO: SQL-Server 2008 Designing, optimizing and maintaining a database server infrastructure

    To go a step further and become a MCM (Microsoft Certified Master):
    70-432 TS: SQL-Server 2008 Implementation and Maintenance
    70-450 PRO: SQL-Server 2008 Designing, optimizing and maintaining a database server infrastructure
    (Now you are a Certified DBA)
    70-433 TS: SQL-Server 2008 Database Development
    70-451 PRO: SQL-Server 2008 Designing database solutions and data access.
    (Now you are a Certified Developer)
    88-970 SQL-Server MCM Knowledge Exam
    88-971 SQL-Server 2008 Lab exam

    To go for the highest level, MCA:
    You will have to have your MCM and pass Board Examns conducted by Microsoft and industry experts.


    Oracle DBA edudation

    There are 3 exams you need to pass to get OCP certified:
    1 1Z0-001, 007, 047 or 051 - Pass some sort of SQL/PL-SQL fundamentals exam
    2 1Z0-042 - Pass Oracle Certified Asociate (OCA)
    3 Follow one approved and instructor led class. 4 1Z0-043 - Pass Oracle Certified Professional (OCP)

    You can go one (large) step further by becoming Oracle Certified master.
    For this you first need to be Oracle Certified Professional. Then you need to attend 2 instructor led advanced classes and lastly you need to take a 2 day practical exam.



    How do Oracle and SQL-Server certification compare?
    I'm not sure yet. At this moment (february 2012), I have worked my way through the MCTS material of both DBA and development and was busy with a T-SQL coarse I found. I am a Certified Oracle Professional (OCP) DBA and was (probably still am) a bit confused about the certification path. With the knowledge I have now my impression is that:

    * OCA does not compare to MCTS because for OCA one needs to have a good dose of SQL/PL-SQL knowledge too.
    * I do not yet know how OCP compares to MCITP
    * OCM might compare to MCM but OCM goes more in depth with DBA related knowledge while MCM expands more clear to Development.
    * There does not seem to be a comparable Oracle examn for MCA.





    Old notes
    70-432 SQL-Server 2008 Implementation and Maintenance (MCTS, counts towards MCSA and MCSE)
    70-433 SQL-Server 2008 Database Development (MCTS)
    70-229 MCSE SQL-Server 2000 Designing and Implementing Databases

    MCTS: Microsoft Certified Technology Specialist

    For the SQL-Server camp I suppose you could do the "Implementation and maintenance" module. However, after working myself through the material I did miss many of the subjects covered by the OCA/OCP route. Apart from the subjects being less in-depth, it did not explain anything about indexes and basic SQL or 3GL programming. PL-SQL was part of OCA but Transact SQL was not part of SQL-Server's "Implementation and maintenance". Because of that I think an Oracle DBA would feel much more equiped when doing SQL-Server's "Database Development" module too.

    Interestingly the Database Development coarse gives a very quick primer regarding normalisation. One might assume a DBA should know how to normalise before learning about any RDBMS but I did appreciate the refresher which was absent in the OCP/OCA coarses.

    While working through "Database Development" I discovered no T-SQL primer again. I guess I need to find a T-SQL class.

    Oracle DBA: OCP + OCA
    SQL-Server DBA: "Implementation and Maintenance" + "Database Development"





  • CLI and GUI

  • Scalability

  • Interchangability

    How easy is it to run forms or APEX on SQL-Server or .net applications on an Oracle RDBMS.

  • Documentation

    SQL-Server has it's documentation installed together with the RDBMS. You can just mouse from "start" to "SQL-Server 2008" to "books and tutorials" to "books online". The documentation has it's own style that seems to be different from the chm files we are familiar with. The reason for the different format is not yet clear to me but I will do by best to find this out. I am all for the "if it ain't broken, don't fix it" way of life and that is wy I am a bit sceptic about the choice Microsoft made here. I put very high value on documentation. It must be easy to find it and it must be easy use. Because of that I am not immediately charmed by the fact that this documentation seems to be hard to use on anything else then a windows compatible platform. Gone is my e-reader.

    All basic Oracle documentation is available in both HTML and PDF format. Below are links to a few most used documents.

  • Normalisation

    Now why would normalisation be a subject for differences between Oracle and SQL-Server?!

    You are right, normalisation is independent of what platform you use. Thing is, I came accross a few events that make me think Oracle and SQL-Server specialists think differently about normalisation. Example:

    Reminder: First normal form = no repeaing groups within thesame record.
    Suppose you have a customer with two phone numbers. In the not-normalized for it would look like this:

    table customers
    245 smith 865-1254-5832 512-4593-5932

    Now logically one would normalize this to two tables each containing one record looking like this:
    table Customers
    245 smith

    table Phonenumbers
    245 865-1254-5832
    245 512-4593-5932

    But what I have seen now in two different situations that SQL-Server specialists are inclined to go this way:
    table customers
    245 smith 865-1254-5832

    table Second_phonenumbers
    245 512-4593-5932

    See? Both solutions get you to the first normal form but the differences are vast. The second solution has the advantage that you need to search only one table to get a phonenumber and to if you want to get them both, the first solution will have you always search both tables. Now, that makes number 2 without a doubt the better choice right? Wrong!

    Solution 2 makes you do one thing to get the first phone number and an entirely other thing to get the other. Your T-SQL code will look like spaggetti to make sure you can cope with both situations at the appropriate moments. Often developers or end users even forget there is a Second_phonenumber table and consequently leave it out of reports! Now "Second_phonenumbers" ok, but "Other_payments_made" is a different matter.

    To be fair there are more ways to look at this.

    It could be that an experienced developer jumped a few steps and denormalized directly to get the best performance for an OLTP database. For that the solution looks great. Sadly in practice I see that it is more likely a junior developer or a time constrainted senior developer just needed to add a column to the datamodel without changing the overall model too much (with all risks involved) and because of that just decided to add a table.

    It goes without saying: Normalize before you Denormalize and only denormalize when it is well motivated.



  • Pronounciation of the word SQL

    This must be one of the ditziest subjects there is. But nevertheless VERY important as far as first impressions go. It all seems to rotate around the word SQL. Some pronounce it EsKewEll, others say Sequel. Now here is the scoop...

    * Walk into a group of SQL-Server specialists and talk about EsKewEl-Server and you are immediately classified as a rookie

    * Walk into a group of MySQL specialists, talk about MySequel and you are cast aside as a M$ groupie

    * Walk into a group of Oracle specialists and talk about SQL and the reaction is a bit more complex. Let me explain; Pre "Oracle Open world 2005" DBA's and developers were happy to converse in EsKewEl. Post 2005 a lot of good people suddenly converted to Sequel... What happened: Larry Ellison used the word Sequel in his opening speech at the convention and during that same day a lot of Oracles finest got confused. Many are still confused. (Imagine you being the next speaker in line after the CEO of CEO's in front of a 5000 people audience, how would you pronounce SQL even if the ANSI standard tells you it is EsKewEl.) Take advantage of it, if you hear a DBA talk about EsKewEl you can assume he has a good number of years of experience, if he talks Sequel he is of the younger generation.

    But seriously, the importance might be more about knowledge then pronounciation. I've met people that were able to write very decent SQL code whichever way they pronounced it.




Paul Karman

Webpage hosted by Goliath       System up: 429 days and 21 hours