Migrating from Oracle to SQL Server-urgent
-
hello, I'm currently in process of migrating Oracle db and tables to SQL Server 2005. Is there any ideas of best way to do this? My first thought was to somehow automate the db, table, views and stored procs creation. But then how tdo I run those SQL scrips in SQL Server db...since the commands are slightly different in syntax. Second, any data migration tools that will extract and map from Oracle to a SQL Server db? I'm open to any other ideas and thoughts also! Thanks
-
hello, I'm currently in process of migrating Oracle db and tables to SQL Server 2005. Is there any ideas of best way to do this? My first thought was to somehow automate the db, table, views and stored procs creation. But then how tdo I run those SQL scrips in SQL Server db...since the commands are slightly different in syntax. Second, any data migration tools that will extract and map from Oracle to a SQL Server db? I'm open to any other ideas and thoughts also! Thanks
On my last job, my first task was to "warehouse" the data in an Ingres database into an SQL Server database. I wrote an application that got the table and index schema and cobbled up the corresponding SQL Server commands and ran them on the target database. I then had another application that would copy the data over. This later became a Windows Service that copied over any changes periodically, this was not a one-time copy of the database, but an on-going warehousing process. Gotchas: On SQL Server a one-byte value (tinyint) is unsigned, but on Ingres it's signed, so I had to use smallint instead. So check to see what Oracle uses. The range of the Date type on Ingres was wider than SQL Server's, so I had to compare each Date value with SQL Server's range. What I did when I found an out-of-range Date value was log it and replace it with SQL Server's min or max as appropriate. I wound up with the ability to specify range checking for other types as well. Check Oracle's range of Dates. As for stored procedures, you're likely stuck. (Yet another reason not to use stored procedures :-D ).
-
hello, I'm currently in process of migrating Oracle db and tables to SQL Server 2005. Is there any ideas of best way to do this? My first thought was to somehow automate the db, table, views and stored procs creation. But then how tdo I run those SQL scrips in SQL Server db...since the commands are slightly different in syntax. Second, any data migration tools that will extract and map from Oracle to a SQL Server db? I'm open to any other ideas and thoughts also! Thanks
I wouldn't do this based on scripts. Instead I would create a program that reads the schema data from Oracle and then constructs create statements to SQL Server. You could for example first create objects, then load data and then add constraints, triggers etc. This way the data won't get modified on the conversion. Also there are plenty of tools for this purpose. One (free) for example: SQL Server Migration Assistant for Oracle (SSMA for Oracle) [^]
The need to optimize rises from a bad design.My articles[^]