Featured Database Articles

Setting up Oracle as a Data Source for SQL Server

In this article, well
continue on with the setup of Oracle as a data source for SQL Server. As
mentioned in Part
1, one of the requirements on the MSSQL side is that the version be of the Enterprise
or Developer editions. One major plus of Oracle over Microsoft is that Oracle
software has no expiration date, whereas the trial software from Microsoft has
a shelf-life of 180 days. One obstacle, which may be insurmountable (if doing
this on a personal computer), is meeting the system requirements for the
Enterprise Edition of MSSQL. The specific showstopper is the operating system
requirement, which is server class (2003 or 2008) across the board.

The simple workaround to
this requirement is to use Developer Edition,
which is equivalent to Enterprise Edition in almost every way. Developer Edition
is not free (it was for a while) and it is not available via download (it was
for a while). The bottom line is you have to order this edition and have it
shipped to you. The good news is that the cost is fairly low, and with a little
shopping around, you can find it online for less than what Microsoft lists it
($49.95 there versus $40.99 at Amazon).

The installation process for Developer Edition (as it is for all 2008 versions) involves quite a few more
Next clicks from the user (person installing), and it does seem to take a bit
longer than MSSQL 2005 installations. The end result should have the results as
seen in the screenshot below.

Another setup step is to
attach (within MSSQL) a pre-configured sample database. While youre at it, do
two: the normal AdventureWorks database and its BI or DW version (named AdventureWorksDW).
The installation files for the databases are located at an open
source site, and the 2005 version of the files work just fine with 2008.
The downloaded msi files dont actually install the databases, but what they do
is to place the main database and log files in a Data directory under your SQL
Server installation folder. Once the mdf and ldf files are staged, use SQL
Server Management Studio to attach the files. Pick a database file and MSSQL
figures out the rest for you. At the end, you should see the databases as such:

Before going on with the
steps provided by Microsoft, it is worthwhile to create a linked server (a
database link in Oracle terms) to the Oracle database of interest. More than
likely, your setup is going to consist of an installation of Oracle and SQL
Server on the same computer/PC/notebook. We assume your Oracle installation is
squared away (i.e., you can SQL*Plus your way into the database and connect as
Scott or some other schema). What we dont know yet is if your MSSQL to Oracle
connection will work.

In a new query window in SQL
Server Management Studio (SSMS), run the following commands, replacing items as
shown via the comments. The two procedures can also be executed via the SSMS
interface, but what to use in some of the fields is a bit cryptic, so having a
T-SQL example to start with is useful (plus then you can go back into the
properties and see what was filled in where).

Your results should look
similar to what is shown below (minus changes Ive made in my version of the
EMP table).

Without getting too far into
other SQL Server database administration tasks, you may want to take backups of
the master and msdb databases. If you have problems at the end of the creation
process, removing linked servers (one related to publication, the other related
to distribution) requires some additional steps, and MSSQL forums are rife with
users complaining about not being able to drop a linked server. With the
backups of the two system databases, you will have an easier go of it in terms
of being able to start over.

· Create a
replication administrative user within the Oracle database using the supplied
script.

· For the tables
that you will publish, grant SELECT permission directly on
each of them (not through a role) to the Oracle administrative user you created
in step one.

· Install the
Oracle client software and OLE DB provider on the Microsoft SQL Server
Distributor, and then stop and restart the SQL Server instance. If the Distributor
is running on a 64 bit platform, you must use the 64 bit version of the Oracle
OLE DB provider.

· Configure the
Oracle database as a Publisher at the SQL Server Distributor.

...the last step results in
the following:

After the Oracle Publisher is identified at the SQL
Server Distributor, replication creates a linked server with the same name as
the TNS service name of the Oracle database. This linked server can be used
only by replication. If you need to connect to the Oracle Publisher over a linked
server connection, create another TNS service name, and then use this name when
[creating the linked server].

In this example, with ORCL
as the name of my Oracle database (and is the publication source), I can expect
to see a linked server named ORCL when the setup is finished.

You have two options as to
how to configure an Oracle publisher: use SQL Server
Management Studio, or use Transact-SQL.
The T-SQL reference is fairly useless, so we will use the GUI interface (SSMS).
The next two major steps are to identify the Oracle publisher at the SQL Server
distributor, and create the publication from the Oracle database.

The distributor is nothing
more than a database within the SQL Server instance where were going to have
the replication data from Oracle published into. This process creates a
distribution database, named interestingly enough as distribution. The name
of this database can be changed later. The role of the distributor is to manage
the replication agents.

In the list of instructions
on how to identify the Oracle Publisher at the SQL Server Distributor, there is
no Distributor Properties option available after right-clicking the
Replication folder. What we do know, however, is that we want to configure a
distributor, create an Oracle publisher, and then create a publication from
Oracle (where we pick the articles or tables we want to subscribe to).

Configure Distribution

Right click Replication and
click Configure Distribution. Click Next on the Configure Distribution Wizard
starting page.

Well use the same instance as the distribution repository, so click Next.

At the next window, have the
wizard configure the SQL Server Agent to start automatically. More than likely,
this will be the case anyway after a default or typical installation, but if
the agent is not set to start, then the running of scheduled snapshots (the
idea of scheduled refreshes in materialized views covered in Part 1) will not
take place.

The snapshot folder is
analogous to a materialized view log; in this example, the default location
will suffice, but for permanent or larger implementations, you may want to use
a different drive.

Use the default name of
distribution for the distribution database, and use the default locations for
the data and transaction log files (again with the caveat of placing these
files in better locations for a real world implementation).

Click Next on the Publishers
window (not shown), and then at the Configure Distribution window, select the
checkbox if you want a script created in addition to having the setup
performed.

If you elect to generate a
script, another window will appear (not shown) with the location and name of
the file (and you can choose the format of the file). Finally, we reach a
summary page, and click Finish to configure the distributor.

Hopefully, youll see the
same success window as shown below.

Upon completion of the
configuration, the right-click menu items on Replication will be different, and
from here, we can now follow the steps in the How to reference from MSDN. The
first step is to Add Oracle Publisher. From the properties option and selecting
the Publishers page, we get to the picture below.

Expand the Options on the
Connect to Server dialog, and fill in your information (example here is the
ORCL database with the MSSQLDIST user mentioned in Part 1).

In Connection Properties, we have a choice between Gateway and Complete as to the publisher type. You can
read about the differences in the MSDN note, but for now, stay with Complete.

From here, click Connect. If
no errors appear, the properties page is in focus and you will see ORCL (the
server can be a publisher within MSSQL, so thats why you see two entries).

Click OK to dismiss the
properties window. The process executes (shouldnt take more than a minute) and
you are returned to SMSS. The next (and last) major step is to create a
publication from Oracle.

Create a publication from Oracle

Weve already granted SELECT
on the tables of interest to our MSSQLDIST replication user, and in this step,
we actually select the table(s) to be replicated. For simplicitys sake, only
the EMP table (all columns, and no filters applied) will be used.

Continuing with the steps in
the MSDN guide, go through the start page, select the ORCL publisher, and then
decide between snapshot and transactional publication.

In this example, I selected
transactional (screenshot shows Snapshot, which is the default), and you will
have an option later to schedule this as well. In the Articles window, find the
EMP table owned by SCOTT, and expand the selection to see the columns. Note the
warning about the datatype conversion of the Oracle DATE field.

If you want, you can
de-select the HIREDATE column to make this demo as clean as possible. I am not
using any filtering, so Next is clicked in the subsequent window.

In checking the status of
the SQL Server Agent, mine is set to start automatically, and I ensured it has
administrator permissions by having created an MSSQL account and added it to
the Administrators built-in group. In my setup, I selected both options
(create a snapshot now, and then do it every hour on the hour).

In the next window, we have
to configure some security settings (not shown), and if you use a generic
MSSQL account with admin privileges, everything will work. On the other hand,
you can also elect to run this under the Agents settings, so either way, youre
covered.

In Security Settings, I used
the Agent, and upon return to the Agent Security window, the other agent fields
are now populated.

In the next window (Wizard
Actions, not shown), create the publication and optionally generate the script.
Finally, enter a name for the publication in the summary window, and click
Finish.

If all goes will, you will
see the same success window shown below (with the exception of a generated
script if you didnt select that option).

In Summary

In the last part of this
series, well take a look at the aftermath (in a good sense of the word) of all
these steps.