I was involved in a recent project to migrate an Oracle database to Microsoft SQL Server 2000, and successfully migrated all Oracle objects to SQL Server. This experience has encouraged me to write an article on Oracle to SQL Server 2000 migration.
In this article, I will explain how to transfer Oracle Objects, including constraints and data, to SQL Server 2000.
Step I: Create the Linked Server
Linked servers provide connectivity to external data sources, allowing you to access data and run procedures on SQL Servers other than the one you are currently running on. Moreover, linked servers provide connectivity to just about any data source you can get an ODBC driver for, providing you with a DML interface to Oracle databases, Excel spreadsheets, flat files and many others. In many cases, this makes linked servers a viable alternative to using DTS or BCP to get data from external sources.
I have used link server to migrate Oracle database to SQL Server 2000.
To create a linked server to access an Oracle database instance:
- Ensure the Oracle client software on the server running SQL Server is at the level required by the provider.
- Create an NET8 alias name on the server running SQL Server that points to an Oracle database instance.
- Execute sp_addlinkedserver to create the linked server, specifying MSDAORA as provider_name, and the Net8 alias name for the Oracle database instance as data_ source. Assume that NET8 alias name as OracleDB. sp_addlinkedserver ‘OraLinkServer’, ‘Oracle’, ‘MSDAORA’, ‘OracleDB’
- Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.
This example maps the SQL Server login ‘Bush’ to the linked server defined in Step 3 using the Oracle login and password OrclUsr and OrclPwd:
sp_addlinkedsrvlogin ‘OraLinkServer’, false, ‘Bush’, ‘OracleUserID’, ‘OrclePassword’
In order to test the whether the linked server is properly created or not, execute the following sql statement from query analyzer
SELECT * FROM OPEQUERY(OraLinkServer, ‘Select * From <any known table in Oracle> ‘)
Step II: Creating Tables
Converting Oracle SQL tables, indexes, and view definitions to SQL Server tables, indexes, and view definitions require relatively simple syntax changes. This table shows some differences in database objects between Oracle and SQL Server. The row size in Oracle is unlimited, where it is 8060 bytes in SQL Server. SQL Server allows you to create tables with more the 8064 bytes in row size with a warning message, however it will raise an error message when transfer the data from Oracle to SQL Server if the row size is more than 8060 bytes.
I have not considered the table and index storage parameters in this article. You can modify the scripts for as per your requirement to include them. Some of the data type conversions from Oracle to SQL Server are straightforward, while other data type conversions will require evaluating a few options. I have assumed the data type conversions in the following manner. You can change as per your convenience by changing in the script.
‘CHAR’ –> ‘CHAR ‘VARCHAR2’ –> ‘VARCHAR ‘LONG RAW’ –> ‘IMAGE’ ‘LONG’ –> ‘TEXT’ ‘DATE’ –> ‘DATETIME’ ‘BLOB’ –> ‘IMAGE’ ‘CLOB’ –> ‘TEXT’ ‘NUMBER’: Without precision –> ‘INT’, With Precision and Scale –> ‘FLOAT’
Step III Creating the Table Defaults
Microsoft treats a default as a constraint, where as Oracle treats a default as a column property. Is easy easily migrate the Oracle DEFAULT column property. You should define DEFAULT constraints at the column level in SQL Server without applying constraint names.