Oracle vs SQL Server
There are different features that distinguish oracle and SQL server as discussed below
Oracle backups are self-contained and unconditional. A DBA has capability to reinstate oracle database from any condition as long has its backup capability is good. This is not the case with SQL servers because incase the server system is lost; a DBA cannot reinstate the system without getting an original CD that was previously used to install it. This means that SQL server is not autonomous.
It is also good to note that reinstating of application systems starts after recovery of master database (Minhas & Koltakov, 2008, p. 13). It makes the recovery of SQL server highly complicated and unintuitive compared to the Oracle where same operations can be completed using a recovery wizard. Even with DBA recovery functions, a down database instance, SQL server creates a serious manageability issue to its DBAs.
Oracle and SQL server also differ in performance. SQL for instance is easy to implement and it operates efficiently in databases with few gigabytes. Once the database goes beyond the gigabytes, the performance starts to slow down or degrades. Oracle unlike the SQL server offers different tuning options and features to enhance performance. Real Application Clusters (RAC) (Troels.arvin.dk, 2014, p. 2).
The feature helps different servers to operate on the same database concurrently thus, offering more processing power to the applications of the company. In short, Oracle server supports ‘’active-active’’ clustering and SQL server does not. Additionally, Oracle has a large database that makes it exceptional in terms of performance.
SQL server sustains vertical scalability. The only way that can be utilized to scale up a system dependent on SQL server technology is put in extra CPU and memory to the single server hosting the database. There is still a limit beyond which vertical scalability cannot provide compensation (Minhas & Koltakov, 2008, p. 23).
Oracle on the other hand holds up on horizontal and vertical scalability. Similarly, to SQL server one can design a system that is built on Oracle by totaling more memory and CPU to single server hosting the database. Once a threshold goes beyond a specific limit where there is no benefit achieved by adding memory and CPU to the single servers, Oracle server users then do not have a choice to upgrade to Oracle Real Application Cluster technology and have other servers that work as ‘’active-active’’. One can also decide to have additional number of Oracle Real Application databases that are essential thus offering enormous scalability.
SQL Server is limited to Microsoft Windows operating system and it cannot operate on any other form of UNIX operating systems (Troels.arvin.dk.2014, p.1). This puts the server at risk based on the fact that MS Windows operating system is at risk of crashes while other UNIX operating system flavors are known to be more dependable and stable. Oracle on the other hand is an ideal project solution because it can operate on Microsoft Windows, or other UNIX operating systems flavors.
Despite the fact that the products’ transferability is not necessarily related to performance, software performance on different operating systems as well as hardware helps individuals to change their software systems without necessarily restructuring their applications. Oracle database is also highly accessible on a large section of hardware and operating systems.
It also supports all other vital UNIX platforms including Linux and Microsoft operating systems such as OS/390 mainframes. People who use Oracle are also in a position to upgrade their hardware without necessarily changing their applications. SQL server 2000 on the other hand can operate efficiently on Microsoft operating system. Clients who wish to enhance their hardware are also restricted to use upgrade hardware platforms operating the systems and therefore, they must incur some costs for changing their systems entirely and especially if they outgrow, their platforms ability.
Concurrency control in multi-user environments ensures that data upgrade carried out by an individual does not have an effect on other users. Oracle and SQL server 2000 varies a great deal by putting their concurrency model into practice. The main differences include; first, Oracle does not have to read locks while SQL server 2000 employs shared read locks to stay away from dirty reads.
Oracle databases need multi-version read consistency while SQL server cannot be traced. Oracle database readers does not interfere writers while that of SQL server interferes with the reader. Locks in SQL server often escalate while that of Oracle database has a non-escalating row level locking.
Multi-version Read Consistency
Oracle multi version read consistency implementation offers accurate results. When there is an update in transaction, real data values are acknowledged to offer a read-consistent view of the table’s data and to ensure that the information version is continuous at the beginning of uncommitted transaction that can be taken back to any user (Troels.arvin.dk, 2014, p. 2).
SQL server on the other hand must choose a workaround to stay off concurrency problems, for example, locking data to prevent it from changing while it is being read. Even so, SQL server does not offer a multi version read consistency. It instead needs applications to either employ dirty reads or shared locks. SQL developers are therefore forced to negotiate in their application design to get data accuracy and concurrency that is acceptable.
Additionally, there is no blockage between readers and writers in Oracle. Strong multi-version read consistency accepts mixed workload environment to work efficiently without necessarily incurring performance penalty for the users.
Bitmap indexes & Bitmap Join Indexes
Generating bitmap indexes on index-organized tables is not possible. This allows for utilization of index-organized tables as fact tables in a given operating environment. SQL on the other hand does not support bitmap indexes and bitmap join indexes (Troels.arvin.dk, 2014, p.1).
Parallel Execution of Operations
Parallel execution of SQL activities can be enhancing performance of activities that involve large volumes of data. It also helps in reducing time involved to respond to data intensive operations on large databases typically related with the decision support systems and data warehouses. Oracle on the other hand executes update, insert, delete and merge.
It helps large database units to be combined to generate more convenient and smaller pieces even though it is widely seen as a manageability and availability option. Additionally, partitioning has various performance advantages (Minhas & Koltakov, 2008, p. 25). Oracle database also offers different table partitioning strategies that are designed to handle different application scenarios.
It also offers three types of partitioned indexes including local index, international non-partitioned index and international partitioned index. Oracle also allows for possible combinations of partitioned and non-partitioned indexes as well as tables. SQL server 2000 does not also hold up partitioning because it is described directly in the database field.
The most ideal server is SQL server. This is based on the fact that it is less capacious compared to Oracle. Oracle’s base product is also costly and to top up to the features that the server offers, it requires other add-ons. These additional features also increase the price thus, making SQL server more affordable compared to Oracle.
If you wish to use Oracle server, you will have to buy other features which are already incorporated for free in SQL Server Enterprise Edition. For example, in SQL Server Enterprise Edition, partition is included in the package while Oracle server, you are only required to pay an additional of 11, 500 per processor along with the price of enterprise edition.
There are also different add-ons available for Oracle and are available for free in SQL Server Enterprise or standard edition. Operating a SQL Server database is also cheap than Oracle database utilization. The licensing fee for Oracle is as little as one tenth. A company running its database on SQL server platform also incurs thousands in software costs annually compared to millions for those operating on Oracle. SQL server databases are additionally easy and friendly to administer and to install.
SQL Server database administrators are also cheap and cost the company 90-110k salary per employee annually (Minhas &Koltakov, 2008, p. 27). Administrators of Oracle database are also expensive amounting 110-200k in salary per employee annually. This means that Oracle datable is relatively expensive thus, it is not easy to get high quality Oracle database administrators.
Minhas, M. & Koltakov, S. (2008). Technical comparison of oracle database vs. sql server 2000: focus on performance. [E-book] [Accessed: 24 Feb 2014].
Troels.arvin.dk. (2014). Comparison of different sql implementations. [Online] Retrieved from: http://troels.arvin.dk/db/rdbms/ [Accessed: 24 Feb 2014].
Ullman, J. & Widom, J. (2014). Oracle sql. [Online] Retrieved from: http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html [Accessed: 24 Feb 2014].