The days in which a business would lock itself into a single vendor’s solution are long past. An organization’s enterprise resource planning (ERP) system, for example, may be built on Oracle and run on UNIX, but the customer relationship management (CRM) system might be built on SQL Server and run on Windows 2000.
For IT consultants, it’s no longer enough to be an expert in your chosen technology. You have to at least be familiar with integrating your core technology with others.
One example of this type of integration is distributed transactions, which make real-time updates to multiple databases simultaneously, even though the databases are based on different database engines. Understanding how Microsoft SQL Server and Oracle Server can work together expands the tools in your consulting toolbox. For example, if you have a client that has a production system on one database and a data warehouse on another, knowing how both interact can make you a more valuable resource to your client.
In this article, I'll describe how to set up distributed transactions that update both SQL Server and Oracle at the same time.
You’re likely to encounter these terms while trying to get databases to talk to each other:
- A transaction is a group of SQL statements that must either succeed completely or fail completely. Withdrawing money from an ATM, for example, has two parts that constitute one transaction: The machine gives you the cash, and the bank updates its records. If either of these actions fails, the other must fail, too.
- A distributed transaction occurs when the individual statements in a transaction update different databases. Each database must commit (record) its part of the transaction for the entire transaction to succeed.
- Two-phase commit is the mechanism used to coordinate distributed transactions. Each server involved in the transaction is first asked to prepare for the transaction and to respond when ready. Then, each server is asked to actually commit the transaction.
How SQL Server does it
Before SQL Server can talk to Oracle databases, you must install Oracle client software on the SQL Server machine. The libraries in the client software work with Microsoft’s OLE DB Provider for Oracle to establish a connection using Oracle's networking software (Net8).
Run the Oracle Universal Installer from the Oracle CD, select a folder to contain the Oracle software, and then choose to install Oracle Client. Any of the preset installation types (Administrator, Programmer, Application User) will work, although Application User has the smallest disk requirement at 100 MB.
You can reduce the disk-space requirements even further if you choose the Custom installation type and if you understand Oracle architecture well enough to know which components to select.
After the install, Oracle's Net8 Configuration Assistant will automatically run so you can define where the Oracle database lives on the network. If you have configured Active Directory to include Oracle databases, be sure to select Yes, I Would Like To Complete Directory Service Access Configuration. Otherwise, choose No, I Want To Defer Directory Service Access Configuration To Another Time, and you can enter the database information manually (see Figure A). This information includes a Net8 Service Name, a network protocol such as TCP, a hostname, and a port number.
|Directory Service Access|
You can test the connectivity at this point. Don't worry if you get an invalid username/password message: The default username used to test with (SCOTT) might not be in your database. Click the Change Login button to use a known login and test again. Proceed through the next few information screens, click Finish, and exit the Installer.
Be sure to reboot the server after installing the Oracle client software. Its DLLs sometimes don't load correctly unless you restart.
Configuring SQL Server
Next, you can configure SQL Server to access the Oracle database. In Enterprise Manager, expand the server node in the navigation tree and open the Security folder. Right-click on Linked Servers and choose New Linked Server to bring up the properties page for the new server. You’ll see three tabs: General, Security, and Server Options (see Figure B).
|Linked Server Properties|
On the General tab, give the new linked server a name in the Linked Server box. You’ll use this name in Transact-SQL statements as part of every table name you access, so keep it short. The System Identifier (SID) of the Oracle database is a good choice, because it’s usually four characters and identifies the Oracle Instance.
For Server Type, choose Other Data Source. From the Provider Options drop-down list, choose Microsoft OLE DB Provider For Oracle. (You can also use Oracle’s OLE DB Provider, which was installed with the client software.) In the Product Name box, type Oracle. In the Data Source and Provider String boxes, type the Net8 Service Name you used when installing the Oracle client software.
The Security tab enables you to map SQL Server logins to their Oracle equivalents. You can enter mappings individually by specifying a Local Login (choose from a drop-down list) and the corresponding Remote User and Remote Password for each login. You can also set up a default behavior for logins not in the list so that connections will either:
- Not be made.
- Be made without using a security context (i.e., guest user).
- Be made using the login's current security context (i.e., impersonation).
- Be made using this security context. (Supply a remote user and password to use as the default.)
The default options on the Server Options tab are generally acceptable. Upon closing the linked server properties, an entry for the linked server appears under Linked Servers in the Security folder. You can expand this entry to see tables and views in the remote server.
To use a linked server in a query, simply use the fully qualified name of the table you want to access. SQL Server object names are in the form server.database.owner.object, so the server name is your new linked server. For Oracle, there is only one database per instance, so you can omit the database name and just put a second period.
So because the owner is the Oracle user that owns the table, and the object is the table name, if your linked server name was PROD, you would access Joe's Employee table as:
SELECT * FROM PROD..JOE.EMPLOYEE
SQL Server's system tables store table names in mixed case. Oracle, however, translates all names to uppercase when it stores them in its data dictionary. Be sure to use uppercase names when linking to an Oracle server, as in the example above.
A distributed transaction can update both servers simultaneously. To do so, start an explicit transaction by typing BEGIN DISTRIBUTED TRAN. Next, write SQL statements that modify (INSERT, UPDATE, DELETE) a SQL Server table, followed by similar statements to modify the Oracle table. Finally, commit the distributed transaction with the usual COMMIT TRAN statement.
How Oracle does it
Oracle supports two levels of integration with databases by other vendors: Generic Connectivity (GC) and Heterogeneous Services (HS). Using GC, Oracle databases can query and even update other databases that have either OLE DB or ODBC drivers. However, such updates cannot be distributed transactions, as only one server can be updated at a time.
HS, on the other hand, permits Oracle to perform distributed transactions with other database types. HS has two parts: built-in drivers that give external software the ability to start and commit transactions, and external agents, called Transparent Gateways, that are specific for each brand of database. Transparent Gateways make the foreign system appear to be like Oracle to an Oracle database.
Although GC is included in Oracle 8i and later, Transparent Gateways must be purchased separately and installed on the target machine. The Transparent Gateway for Microsoft SQL Server (TG4MSSQL) is the HS agent to use for SQL Server.
Once the Transparent Gateway is set up, a database link must be set up in the Oracle database via the CREATE DATABASE LINK command. Users can then query or update the remote server by including the database link in the table name using the format tablename@dblink. This is the same syntax as would be used to access another Oracle server, hence the name “Transparent” Gateway.
For example, to access the Employee table on a SQL Server database named HR, a user might type:
UPDATE Employees@HR SET
LastName = ‘JONES’ WHERE LastName = ‘SMITH’
What happens if one side is down?
If one of the two servers is unavailable when a distributed transaction occurs, several things can happen. If the server doesn’t respond to the prepare phase (the first part of the two-phase commit), the transaction will be refused, and all servers involved will be asked to roll back the transaction. If the server goes down during the commit phase (the second part), the transaction will appear to succeed but will be marked as an in-doubt transaction.
SQL Server and Oracle both have ways to deal with in-doubt distributed transactions. On the SQL Server side, a background service called the Microsoft Distributed Transaction Coordinator (MS DTC) manages the process. In Oracle, a similar process called RECO (short for Recoverer) is charged with completing the transaction. In Oracle, for example, RECO checks periodically to see if the remote server is available yet. When the unavailable database comes back online, the transaction is completed and removed from in-doubt status.
Because both Oracle and Microsoft products are commonly found in organizations of all sizes, consultants who increase their knowledge of how each interacts with the other will broaden their ability to help clients. While understanding the relationship between Oracle and SQL Server can be complicated, it’s worth your time to become fluent in both.