MSSQL replication capabilities for synchronizing with Oracle databases

1. Establish a link to the database operation, SQL Server can use ADO, Oracle can use OLE DB or System.Data.OracleClient (need to add reference to use)

The advantage of this scheme is simple, each write their own database operation code, the disadvantage is that two of the tables in the database can not be directly federated query, link Oracle Each machine must be installed OracleClient to use.

2. Use synonyms to manipulate Oracle databases.

The specific operation of this method I have already written in the previous article, so that one can make the table joint query and do not need to install OracleClient per machine, but there is a disadvantage: low efficiency, such as the use of synonyms like to find the required data, If you have a direct connection to the Oracle database, you may be able to find the answer in just 0.1 seconds, but you might have to wait 10 seconds, 20 seconds, or more for synonyms. SQL Server did not directly pass the query statement to Oracle, let Oracle do the operation, but instead of all the data on the SQL Server servers, the edge of the data edge analysis until the query conditions are met. In fact, this data on two servers also has the disadvantage that once the Oracle server down, even if all our servers are good, then our program used in the Oracle data is not available.

3. Direct link to Oracle database + synonym + Job + exception jump mode.

This approach specifically means that our program links the Oracle database directly with OLE DB (OracleClient), while SQL Server servers also establish a synonym for Oracle to set up jobs on SQL Copy the data from the synonym to a table in the SQL Server servers. Once Oracle is down, an exception is caught in the program, and the data in SQL Server is used. This is more complicated, need certain programming, the efficiency is not particularly high, but the advantages are also detailed, usually linked to the Oralce server, in exceptional cases, linked to the backup data in SQL Server, to ensure the normal operation of the program.

4. Write a program directly, running in a service or self-starting mode, and write data from the Oracle database to the SQL Server database at intervals.

This method is a complex programming, with great difficulty.

5. Use the SQLSERVER2005 replication feature to synchronize data from the Oracle database to SQL Server servers, where the program only operates on server and does not link the Oracle database.

This method does not need to write any code, because only SQL Server query, so the query efficiency can be long table link, developers do not need to install OracleClient, do not have to fear that the Oracle server outage caused our related programs can not be used. The advantages are many, then there are no shortcomings to pull? Shortcomings or some, the main is that the data obtained is not real-time data, Oracle over the data updated, and our side may be old data.

The previous four methods I do not have to talk about, this time is mainly about the fifth method of concrete implementation:

1. Install the SQL Server replication feature.

When installing SQL Server database, select "Copy" to install the "copy" function to the server. Microsoft said that if the first installation of the time did not install, and then want to add the feature only need to run the installation program will be "copy" selected on it, but said so, I tried a few times no, I do not know how to do, in order to install the copy, so the SQL Server uninstalled, reinstall! This method is stupid, But I can not think of other ways to achieve, I hope the master guidance.

2. Set permissions on the Oracle server side.

Create a user to use for replication, and grant the following permissions:

CREATE public synonym and DROP public synonym

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SESSION

You must also grant the following permissions directly to the user (not through the role):

CREATE any TRIGGER.

CREATE TABLE

CREATE VIEW

The user must also have unlimited permissions on the table space for the table to be published and select permissions for each table to be used for synchronization.

Installing oracleclient10g on a 3.SQLServer server

The Oracle server must be installed before it can be linked, and it is not recommended to install oracle9i, even if the oracle we are linking to is 9i. Restart the SQL Server servers after the installation is complete. Test with Plsql or other tools to see if you can link to an Oracle server.

4. Set SQL Server Agent permissions

Create a new user on the SQL Server servers, add the user as an administrator, locate the SQL Server Agent in the services, and then restart the service by changing its startup user to that user.

5. Configure distribution

Open the Managementstudio right-click the Copy node, select Configure distribution, follow the steps of the wizard, designate the snapshot folder path as a dedicated shared folder, and no other modifications to complete the configuration distribution.

6. Adding Oracle Publishing

Right-click Local Publishing, New Oracle release, launch the Oracle Publishing Wizard, add an Oracle server, enter the new user password on the Oracle server, continue to the next step, select the snapshot publication, check the table that needs to be published, select the Create snapshot publication now, and Plan to run the Snapshot Agent, schedule the time you want to set, "Snapshot Agent" select "Proxy service Account", publish name fill in the required name, such as "Test1", and then click Finish. This completes the publishing effort.

7. Add an Oracle Subscription

Right-click on "Local Subscription", "New subscription" Run Wizard, select the newly created Test1 release, generally check "push subscription", specify the database to be subscribed (for example: MIS), the database to be used to store Oracle data, The subscription attribute is run with the SQL Server Proxy user, the agent schedules run continuously, initializes the time immediately, and then creates the subscription completion.

At this point we open the database mis for the subscription, and we can see that the Oracle published tables are added, and the content is synchronized with Oracle data at the scheduled interval.

To this our synchronization is complete.

8. Test whether the synchronization is successful

Write data to Oracle with Plsql, modify the data, delete the data, and if the publish time is selected to run the Snapshot Agent every minute, then after one minute we open the table in SQL Server, and we can see that the content is changed like Oracle.

PS: Under "Local Publishing", select our publication, right-click, "View Agent run status" To see if our release was successful.

MSSQL replication capabilities for synchronizing with Oracle databases