Oracle Database to SQL Server Comparisons

No, this isn’t one of those “why my RDBMS is better than your RDBMS” comparisons. One of the initial stumbling blocks a DBA on one system encounters is learning or trying to figure out where features of your system live or reside on the other (less familiar to you) system. Let’s approach this feature comparison by taking SQL Server and mapping its features back into Oracle. By way of time and constraints, this comparison will not be 100% inclusive of either system, but it will show (as much as possible) the SQL Server to Oracle mapping using what you can see in SQL Server Management Studio (SSMS).

The Object Explorer pane in SSMS serves as a good frame of reference to do the reverse mapping. I could have used Oracle and mapped over to SQL Server, but two reasons negated that. The first is where do you find a convenient one-stop interface in Oracle? Definitely not in SQL*Plus, and it would be hard to argue that Database Control’s interface is anywhere near as utilitarian as SSMS. The other is that most readers of this article are coming from an Oracle background, and we want to take what we know about Oracle and see how that fits or works in MSSQL.

The versions used here are Oracle 10gR2 (platform does not matter) and SQL Server 2005 on Windows Server 2003 (also R2). There are some slight differences between MSSQL 2005 and 2008, but as far as the Windows OS is concerned, it won’t be an issue. Oracle 10g is still in wide use as is MSSQL 2003/Server 2003, so the comparisons made here will work for most of the people most of the time.

Database Engine

Let’s start with a fully collapsed tree in Object Explorer.

Much like what SQL*Plus can show at the command line interface with respect to who you are logged in as, the same appears at the top showing which database engine I’m connected to. The database engine (SID) is named WIN2003, which also happens to be the name of the Windows server. A one-to-one match like this generally implies that the default instance name chosen during installation is “dot” or the name of the server. The version of MSSQL is also shown, as is who is connected in this particular session. By looking at the connected user or account (and assuming someone wouldn’t create an account explicitly named this way), we know the login was validated via Windows authentication. Can you tell by what is shown if SQL Server authentication is enabled? No – you would have to go into Properties>Security to see which option (Windows only or both) is selected. The authentication mode is analogous to letting Oracle maintain security via its internal mechanisms or using OPS (operating system authentication).

What else could you safely assume about what the connected user can do? By default, the Administrator (the user who started the SSMS session) will have “sa” privileges (sa being analogous to a combination of SYS and SYSTEM). If you look at the various MSSQL-related groups on Windows, you won’t see Administrator explicitly listed. In Oracle, looking at the ORA_DBA group, Administrator is listed as a member of the group.

Right-click the connection and select Properties.

Many of the settings buried in each “page” correlate to parameters in Oracle; not so much on the General page, but definitely so in the rest. One setting of particular importance has to do with how much memory MSSQL is allowed to use. Click Memory and the display changes to what is shown below.

The value of 2147483647 MB is for all practical purposes unlimited. You would definitely want to set this to something lower than what the OS has available. Does this memory setting represent ALL of the memory used by MSSQL? No – it represents the buffer cache, which is typically the largest memory component of MSSQL. MSSQL has other things going on outside of the buffer cache, and here is where Oracle and MSSQL are similar: it can be somewhat difficult to determine how much memory overall, each system uses. Microsoft has performance counters, which can be selected in a management console, and the information presented therein can help you come up with an answer to “how much memory is SQL Server using?”

Several items on the Advanced page will also be of some interest.

Although there are some Net Services-like comparisons within MSSQL’s Surface Area Configuration, the Remote Login Timeout is the closest to what is found in the sqlnet.ora file for connection timeout settings. This may be one of the first places you go to if remote clients (across a slow network) are experiencing timeout issues while trying to connect.

Within Oracle, parallel query or parallel execution operations can greatly help – or hinder – a database’s performance. The same holds true in MSSQL. Two factors come into play: the cost threshold and the max degree of parallelism. MSSQL also has an optimizer, and if it determines the cost is above 5, then parallelism will be considered. Okay, if parallelism is considered, how much is allowed? The zero setting means unlimited (up to the number of CPUs, and is also constrained by the edition, Standard versus Enterprise). Unfortunately (or not, depends on your viewpoint), this is an across the board setting. We can open up Oracle for maximum use of parallelism, but it can also be throttled overall and at the table level.

Databases

Alright, we understand what “database” means, and as far as tables and indexes are concerned, the two systems are not that different. Looking at the list of databases shown below, what do we know about WIN2003?

Right off the bat we know that at most, what we are seeing is a slightly more than default installation. The reason we know this is because of the ReportServer (and its temp DB) database being present. SQL Server Reporting Services (SSRS) was either selected during installation or installed during another “setup.exe” session.

The System Databases are of more interest to us right now.

Four databases are always present in MSSQL: master, model, msdb and tempdb. The easy analogy between systems is that master and msdb correspond to the SYSTEM and SYSAUX tablespaces in Oracle. Oracle does not have a model database, but it does have templates, so model in MSSQL is quite similar to the templates you see in Database Configuration Assistant.

The tempdb database lives on as the TEMP tablespace in Oracle, matching many of the same features and purposes. The tempdb is re-created whenever the MSSQL instance starts.

How do you create a database in MSSQL? One way is to use the wizard (right-click Databases>New Database…). Once created, add tables, indexes and programmability (triggers, stored procedures) and off you go. Another way is to plug in a database file (and possibly a transaction log file) via an attachment process or from restoring a backup. An attached or restored database isn’t necessarily new (they had to have come from somewhere else), but they may be new to your instance. This process also leads to the idea of cloning.

How would you clone a production database into something else (e.g., a development database)? MSSQL offers several ways to clone a database. One of the simplest methods is to take (or use) a backup of the source and use the BAK file for a database restore (right-click Databases>Restore Database…). Database Control in Oracle offers a Clone Database feature (at extra cost). You can also use RMAN or some type of backup (even export/import, traditional or datapump).

The database level is where a huge departure between Oracle and MSSQL takes place. In Oracle, we are archivelog mode or not, and that applies to everything (minus read only tablespaces). In MSSQL, a database runs in one of three recovery models (full, simple and bulk logged). Instead of having archived redo logs filling up an archived log destination and hanging the instance, with MSSQL you can grow the transaction log (LDF file) to such an extent that you fill up a disk and cause the same problem. Taking periodic backups of the transaction log (and then truncating the log) prevents this problem in the FULL model. It then becomes analogous to a more granular point in time recovery capability.

Database files between the two systems are somewhat similar. Many, if not most MSSQL databases will have two files: the database file (MDF file extension) and a transaction log file (LDF file extension). You can have more than one MDF file and even have file groups. The drill down path to find file information in MSSQL (and yes this is also easily done via T-SQL commands) is found in the properties of a database (on the Files page, shown below).

By default, we get the PRIMARY filegroup. Other filegroups can be used/created, and one reason for that would be for use of partitioned tables. For a production database, the Autogrowth rate/setting can be important. You can almost think of this as a percentage based extent growth setting in Oracle. The options for growth are similar to what Oracle offers.

As you can see, MSSQL can grow files in one of two ways, cap the size or not, and determine who should grow a file. Just as users can spend time waiting for file growth in Oracle, users (or the system itself) can spend time waiting for file extend operations in MSSQL. If a file is going to grow a lot, then grow it in larger chunks at a time, but not too large because then you spend (too much) time waiting for that too. Lastly, do you want a file to be able to eat up all remaining disk space? If not, then restrict the file growth, just as you would in Oracle.

Source: databasejournal.com