Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 13, 2010

Mapping SQL Server Management Features to Oracle Database

By Steve Callan

DBAs often need to learn where features of their system can be found on other systems. Steve Callan explores mapping SQL Server replication and management features back into Oracle Database.

Oracle as a Data Source, Setting up Oracle as a Data Source for SQL Server and Configuring Oracle as a Data Source for SQL Server address the use of replication within SQL Server where Oracle was used as the data source, and many of the comparisons or mappings back into Oracle are addressed therein, so I’ll pass on repeating this material and move on to the next category.


Continuing on in SQL Server Management Studio’s (SSMS) Object Explorer tree, we see that the next major category deals with replication.

SQL Server Management Studio’s (SSMS) Object Explorer tree


Many of the administration-like features of Oracle can be found under Management.

administration-like features of Oracle can be found under Management

To start with, Maintenance Plans are quite similar to procedures you would call within a job or schedule in Oracle. For example, say you have a requirement to purge or archive data in an Oracle schema. The stored procedure defines the “what” with respect to what is to be done when the procedure runs. The archive and purge job example could be defined via a plan in MSSQL as well. You could also easily define a plan that takes care of your backups (although not everyone thinks a maintenance plan is the best way to go about running backups, but Microsoft thought it was useful, so that’s why it can be done). Many common “ash and trash” tasks are already pre-defined in MSSQL. Right-click Maintenance Plans and start creating a new plan via the wizard. In the bottom left corner of the design pane, you can see a list of those tasks.

SQL Server Maintenance Plan Tasks

Many of the tasks are things DBAs did quite often in older versions of Oracle because there was no automated or built-in mechanism in Oracle that would do them. The updating of statistics is fairly well automated starting with release 10g, and MSSQL does it too, but it can be done more often if need be (especially after significant changes, so no need to wait for a nightly maintenance window to open).

Two of the tasks shown above are contention-related tasks in the Oracle community. By contention, I’m referring to some historical and vehement discussions on various forums and blogs related to the efficacy of rebuilding or reorganizing indexes. Are there times (in Oracle) where you should do one of these tasks? Yes, but for the most part, there is no need to do so. However, in MSSQL, you definitely want to perform index maintenance routinely.

Whether you reorganize versus rebuild depends on the degree of fragmentation. There are guidelines as to when to do each (5 to 30% fragmented is one task, more than 30% is the other), and your version of MSSQL also comes into play (if a task can be done online or not).

As a tip, the first thing you should install once the MSSQL instance has been created is the performance dashboard. What Oracle provides in Database Control pales in comparison to what Microsoft gives you.

The next subcategory concerns SQL Server log files. If you’re thinking alert logs, you would be mostly correct. Because we’re on Windows, you also have to be concerned with Windows-level events that may be logged there but not within MSSQL (mostly related to startup issues, as in, why didn’t SQL Server start?).

Double-click the Current log and from the log viewer, you can get to other logs if need be.

SQL Server Log File Viewer

If you like the brute force approach, you can also view log file information directly via Explorer.

view log file information directly via Explorer

Activity Monitor (next subcategory) is very handy in terms of looking at current sessions.

SQL Server Activity Monitor

It is a bit harder to track down current SQL statements, but as far as being able to filter information and monitor blocking and waits, this is the place to be. In MSSQL 2008 the interface to launch Activity Monitor is still present, but in a different place. So, in terms of what you would see in Toad’s session monitor while connected to Oracle, Activity Monitor is pretty close.

The second optional item to install or configure in a new instance is Database Mail. You’ll need SMTP server information, along with an account (profile) on MSSQL who is your “mailman.” Use the mailman to send email to whoever needs notification. All jobs, for example, have options as to what takes place notification-wise for run, success and failure. In addition to mail, you can also send pages.

SQL Server Database Maiol Configuration Wizard

The comparison in Oracle is to create a generic “send mail” procedure that can be called by another procedure. You still have the one time setup of SMTP information, plus in newer versions of Oracle, just being able to create an email has been greatly simplified. The mail setup in MSSQL is practically a no-brainer in comparison.

With respect to the Distributed Transaction Coordinator in MSSQL, consider the following statement:

At the application, a distributed transaction is managed much the same as a local transaction. At the end of the transaction, the application requests the transaction to be either committed or rolled back. A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

Does that read any different that what you expect to see in Oracle? Nope, and it is pretty much the same thing in both systems. You’ll see references to this feature as MSDTC. Looking at its properties in Services, you see the following:

Oracle Database Distributed Transaction Coordinator Properties

Ever have a problem uninstalling Oracle on Windows, and see a reference to msdtc.exe? This is the culprit. The fix is to stop the service and then continue with what you were trying to accomplish with respect to installation. The overall setup and configuration of MSDTC is a bit involved, and it includes the use of clusters, sounding almost RAC-like.

The next item in Management is Full-Text Search, Oracle’s counterpart being Oracle Text. The text-searching feature is quite useful when what you’re looking for cannot be (easily) handled by normal predicates. Typically, a text or string search is based on “where string = ‘some text’” or “where string like ‘%something else%’.” But, what happens when you need to find words close to one another, or variations of a word? This is where full text search comes in handy. Both systems create catalogues (datastore) for full text indexing, and the details of that are easily found in documentation. Did you know that Oracle Text is an included feature in all editions?

The last subcategory is a somewhat cryptically named Legacy item. The best way to describe this category is to say it is a placeholder for older functionality. As you can see, that functionality includes maintenance plans, DTS and mail.

a somewhat cryptically named Legacy item

These features worked differently in older versions, and given how instrumental they can be and were, being able to use these features in newer versions without having to jump through major hoops to upgrade them is probably the genesis of Legacy. I wouldn’t say there is a clear cut mapping back into Oracle. An Oracle setting or parameter that probably comes closest is the COMPATIBLE initialization parameter. Even then, within Oracle, setting this can be a one-way journey, that is, once set to a high enough setting, there is no going back to something lower. Another close analogy would be the use of a deprecated feature. The feature still works (for now) but is slated to not be present in a future release.


In the next part of this series, we’ll finish the mappings from SQL Server into Oracle.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM