We have a reasonably heavy Oracle 10G database (~ 120 GB space occupied). The database consists of tables, packages, materialized views, procedures etc. We are planning to decommission Oracle and migrate to SQL server 2008.
Can you provide your experience on this similar exercise? What is the best approach to undertake this exercise? Are there any tools available with Microsoft to implement this?
Once SQL server is commissioned, is it possible to connect SQL server to Oracle server in downstream applications?
Best Answer
I suspect that the greatest problem will be converting your PL-SQL code.
I have not used this tool, but I would definitely investigate Microsoft's own "SQL Server Migration Assistant for Oracle". 1
You should be able to connect SQL Server directly to Oracle using SQL Servers "Linked Server" feature. That will allow querying the Oracle data from a SQL Server client, without having to make a direct connection to the Oracle instance. If you have a 64 bit SQL Server, be sure that 64 bit database drivers for Oracle are installed and configured on the SQL Server host or the connectivity will not work.