Here is an introduction. However, when part of the data is placed on the Oracle database server, this may bring you many challenges. For example, just setting up a connection to the server is not an easy task. Suppose you currently have an online ordering system based on Microsoft .NET, but your quality control and maintenance system still uses an old Oracle database application. When your customer places an order for product replacement during the product warranty period, there will be no charge for the order. At this time, you need to get real-time query results from the Oracle database. By establishing a linked server, you will be able to query customer information in the Oracle database from SQL Server in real time to find out who your existing customers are.
When your data is spread across different SQL Server databases, connecting servers allows you to perform cross-server distributed queries. When all database servers are SQL Server, setting up the connection server is very easy, and everything you need to know is covered in the SQL Server online manual. However, when part of the data is placed on the Oracle database server, this may bring you many challenges. For example, just setting up a connection to the server is not an easy task. You must understand: Even if you want to set up an Oracle connection server in Enterprise Manager of SQL Server, this SQL Server is a client to Oracle. Therefore, you must successfully install and configure Oracle's client software on the server where SQL Server is located. Because the products provided by Oracle only support databases after Oracle 8, I assume that you are using databases after Oracle 8. The Oracle Net8 function library provides the client software required by SQL Server.
Translator’s Note 1:
When setting up the connection to the server, the OLE DB Provider for Oracle provided by Microsoft is used. The Oracle network library used is SQL*Net 2.3.3.0.4 or later versions, but this is provided by the Oracle 7.3 database. In other words, to set the Oracle database as the connection server of SQL Server, the Oracle database only needs to be version 7.3.3.4 or later, and be paired with the appropriate SQL*Net or Net 8 function library.
Source:
1. Oracle 7 Server Getting Started release 7.3.4
2. SQL Server Books Online: OLE DB Provider for Oracle
In the Oracle database, a schema represents a single database (Translator's Note 2) known to SQL Server experts. When connecting to an Oracle database, you must provide the schema name, password, and host string. Each specific Oracle account has an Oracle schema, and there can be only one schema. So the schema name is actually equal to the account name of the schema owner. You can query Oracle's Data dictionary to get more information about the schema.
The Oracle connection string can also be called the service name (service name) or system identification data (Systen Identifier, SID). What we call a SQL Server database instance is called a database in Oracle. Therefore, when installing Oracle Server, the installation program Oracle Universal Installer (an installation program with a graphical interface, similar to the Setup program of SQL Server) will ask you what the SID name is as the name of the Oracle database.
Translator’s Note 2:
There is something wrong with the explanation of the schema mentioned by the original author of this part. Oracle schema can be viewed as a collection of all database objects (schema objects) owned by the same user. For example, the full name of the EMP table created by user scott is SCOTT.EMP, and SCOTT is the schema name of EMP. So the schema name is actually an Oracle database user account. But it must not be compared with the database! Because the database architecture of SQL Server includes data files and log files, but Oracle's schema objects only exist in the tablespace. In order to avoid confusion for some readers, this is explained here.
If you set the SID to Ora817 when installing Oracle Server on a Windows operating system (the same as the example in this article), there will be a service named OracleServiceORA817 in the system.