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.
and include the URL for the page.
