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.
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.
Summary
In the next
part of this series, we’ll finish the mappings from SQL Server into Oracle.
