Synchronize with Oracle database using the MSSQL replication feature

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

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

2. Use synonyms to manipulate Oracle databases.

The specific operation of this method I have written in the previous article, so that you can do a table of the joint query and then do not need each machine to install OracleClient, but there is a disadvantage: inefficient, such as the use of synonyms like to find the required data, If you are directly connected to an Oracle database it may take only 0.1 seconds to find the answer, but synonyms may take 10 seconds, 20 seconds, or longer. Instead of directly passing the query to Oracle for Oracle to perform, SQL Server took all the data to the SQL Servers and analyzed the data side until the query condition was met. In fact, this data on two servers There is also a disadvantage is that once the Oracle server down, even if all of our servers are good, then our program to use Oracle data can not be used where.

3. Direct link Oracle database + synonym + job + abnormal jump mode.

This approach specifically means that our program directly links an Oracle database with OLE DB (OracleClient), while SQL Server servers also establish synonyms pointing to Oracle, creating jobs on SQL Server, Copies the data from the synonym to the table in the SQL Server servers. Once Oracle is down, the exception is caught in the program, and the data in SQL Server is used. This is more complex, the need for a certain programming, efficiency is not particularly high, but the advantages are also detailed, usually linked to oralce servers, abnormal circumstances linked to SQL Server backup data, to ensure the normal operation of the program.

4. Write directly a program that runs all the way through the service or from the start, and writes data from the Oracle database to the SQL Server database at intervals.

This approach is complex programming, with great difficulty.

5. Use the SQLSERVER2005 replication feature to synchronize data from an Oracle database to a SQL Server servers that operate only SQL Server and do not link Oracle databases.

This method does not require any code to be written, because querying only SQL Server makes it efficient to have multiple table links, developers do not need to reload OracleClient, and do not have to fear that Oracle server downtime causes our related programs to be unusable. There are many advantages, then there are no shortcomings pull? Shortcomings or some, mainly to obtain data is not real-time data, Oracle over there data updates, we also may be the old data here.

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

1. Install SQL Server replication feature.

When you install the SQL Server database, select Copy to install the copy feature to the server. Microsoft said that if it wasn't installed the first time, later want to add this feature only need to run the installer will be "copy" selected on it, but say so, I tried a few times, I do not know how to do, in order to install on the copy, so the SQL Server Uninstall, reinstall! This method is very stupid, However, I can not think of any other way, I hope the expert advice.

2. Set the Oracle server-side permissions.

Create a user for replication, granting the following permissions:

CREATE public synonym and DROP public synonym

CREATE PROCEDURE

CREATE SEQUENCE

CREATE session

You must also grant users the following permissions directly (not through roles):

CREATE any TRIGGER.

CREATE TABLE

CREATE VIEW

The user must also have unlimited permissions on the table space that the table is to be published, and set SELECT permissions for each table to be used for synchronization.

Install oracleclient10g on 3.SQLServer server

You must install to link an Oracle server, and it is not recommended to install oracle9i, even if we are linking Oracle to 9i. Restart SQL Server servers after Setup completes. 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, find SQL Server Agent in the services, change its startup user to that user, and restart the service.

5. Configure distribution

Open Managementstudio The Copy node, select Configure distribution, follow the wizard step-by-step, and designate the snapshot folder path as a dedicated shared folder, without modification, to complete the configuration distribution.

6. Add Oracle Release

Right-click Local Publishing-> new Oracle publication, launch the Oracle Publishing Wizard, add an Oracle server, create a new user password entry on an Oracle server, continue to the next step, select the snapshot publication, check the tables you want to publish, select the Create snapshot publication now and Plan to run the Snapshot Agent, schedule the time on your own as required, "Snapshot Agent" select "Agent service Account", publish name to fill in the name you need, such as "Test1", and then click Finish. This completes the release work.

7. Add Oracle Subscriptions

Right-click Local subscription-> the new Subscription Run wizard, select the newly created TEST1 publication, typically select push subscriptions, specify the database to subscribe to (for example, MIS), which is the database to use to store Oracle data. The subscription properties are run with SQL Server Agent user, the agent schedule runs continuously, initializes the time immediately, and then creates a subscription completion.

This time we open the subscription database MIS, and we can see that the Oracle-published tables are added, and that the content is synchronized with Oracle data at a scheduled interval.

To this end our synchronization is complete.

8. Test for Sync success

Write the data to Oracle, modify the data, delete the data, and if the publication is selected every minute to run the Snapshot Agent once, then after a minute, we open the table in SQL Server, we can see that the contents of the same as Oracle to change.

PS: Under "Local Publishing" Select our release, right, "View agent run status" To see if our release is successful.