In my previous post, we looked at the first steps to accessing Oracle data in SQL Server: creating a Replication Administrative Schema User, and then granting the Replication Administrative Schema user SELECT permissions on the tables you want to migrate to SQL Server.
Next, you need to install the Oracle client networking software and the Oracle OLE DB provider on the SQL Server Distributor, so that the Distributor can make connections to the Oracle Publisher. You should install the most recent version of the Oracle client software. You may choose to install different options depending on your needs, but these are the steps that I followed when I installed the software.
I chose the Administrator option to ensure all the components I needed were installed. The Oracle Universal Installer will guide you through the rest of the steps to install the client software. I also installed the networking software which didn’t start automatically after I installed the client software. You can start this wizard by navigating to Start | All Programs | Oracle – OraClient11g_home1 | Configuration and Migration Tools | Net Configuration Assistant.
When I opened the Oracle Net Configuration Assistant, I chose the Local Net Service Name configuration option.
To access an Oracle database across the network you use a net service name. The net service name is usually the global database name. On the Net Service Name Configuration, Service Name page, you need to enter this name. I selected the option to use TCP to communicate with the database over the network. On the Net Service Configuration, TCP/IP Protocol page, you need to enter the name of your Oracle server, and you can change the port number if you want, but I kept the default.
On the Net Service Name Configuration, Test page, you should perform a test. When I performed this test, I got an error. To get around the error, I had to change the login to the Replication Administrative Schema user I created. Then, the test succeeded.
For the rest of the wizard, I accepted the defaults, and then restarted the computer.
After completing the wizard, there are a few more steps to migrate Oracle data to the SQL Server, which I will talk about in my next post.
Posted Wednesday, March 31st, 2010 at 11:21 am by peterl
4,586 views