Mapping SQL Server Management Features to 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.

Replication

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

Management

Many of the 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.

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.

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

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

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.

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:

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.

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.