Migrate from SQL Server to Oracle in .NET using Devart
Steps to migrate from SQL Server to Oracle for Shuts Project.
1. Download and install Oracle Database 11g – Personal Edition – this edition offers the full feature set of Enterprise Edition and is targeted for development/staging.
2. Download and install JDK 7.
3. Upgrade the SQL Developer from 2.x to 22.214.171.124.84
4. Launch SQL Developer and use the Migration workbench to capture an external database.
a. Click tools -> migration -> Create database capture scripts
5. Copy the capture scripts to the target server. Execute OMWB_OFFLINE_CAPTURE.BAT and pass in parameters for username/password and target db.
rem %1 DBA login id
rem %2 password
rem %3 database name
rem %4 database server name
6. Copy output of capture Oracle server.
7. Create a new oracle database ‘orclshuts’ using the Data Configuration Assistant.
8. Create a new oracle database ‘repo’ using the Data Configuration Assistant.
9. Using SQL Developer login to the newly created database
a. Right click on the new connection
b. Expand ‘Migration Repository’
c. Click ‘Associate Migration Repository’ and complete the wizard
10. Using the migration workbench
a. Click tools -> Migration -> Migrate…
b. Click Next
c. For repository set the connection to your new ‘repo’ database.
d. Click Next
e. Set the project name and output DIR (e.g. d:\migratedb) for scripts.
f. Click Next
g. Select source mode as ‘offline’ then browse to output your captured output folder and select the ‘sqlserver2008’.ocp file.
h. Click Next
i. Select the database to migrate from the available databases list
j. Click Next
k. On the conversion options - Click Next
l. On the SQL objects to translate – Click Next
m. Set your target as the ‘dbname' database, click Advanced and under Object Types check ‘Select All’ to include stored procs, functions and triggers -(this will also capture sequences).
n. Click Next
o. For move data, set source to ‘repo’ and target to ‘dbname'.
p. Click Next
q. Click Finish.
11. In SQL Developer open your output DIR and select the dropallschemas.sql file, ensure that SQL Developer is running on your ‘dbname' database and execute script.
12. In SQL Developer open your output DIR and select the master.sql file, ensure that the SQL Developer is running on your ‘orclshuts’ database and execute script. Provider your new user passwords as prompted by the script (should create a user named ‘dbo_MaintenanceShuts’).
13. Install DevArt DotConnect for Oracle.
14. Launch Visual Studio and add a new DevArt Entity Model.
15. Right Click in an empty space within the model and then click on ‘Generate Model from Database’.
16. On the connection details utilize the new 'dbo_schemaname’ user and use the password you used during execution of the master.sql script. Ensure that the ‘dbname' database is targeted.
17. Update the Entity Container Name and Namespace if needed.
18. Update the entity names in the Entity Framework Model to camel casing, instead of full caps as generated from the Oracle schema.
Some gotchas during migration.
The Oracle SQL Developer requires JDK 7.0
The Oracle SQL Developer migration workbench that comes with 11g is not able to access SQL 2008 data and is limited to 2005. So an upgrade to SQL Developer 4.0 was necessary in order to generate the Capture scripts correctly.
The ORM (edml) for entity framework generated by DotConnect generates two distinct namespaces, RTIO.Shuts.Data (the business entities) and the RTIO.Shuts.Data.Store (sql map). Both need to be maintained within the solution and changes to one will inevitably impact the other side.
*The Store and Data are two different things available in designer. After fixing the method call names and adjusting to the new addobject deleteobject functions. I executed the application to find that only one object was being inserted. Sequences were created during migration and direct insertion worked. Each primary key (identity) in both the ORM entity classes and the table (still in ORM designer) must be updated to use the Stored Generation Pattern = “Identity” – then all was well.
*Also all stored procs needed to be right clicked on at the Store and then click on “Create Method” in order to generate the method call and return type in the dbcontext object.
*When using SQL Developer ensure that any worksheet you use – once you are done, to hit commit changes. As all SQL executions are run in a transaction and do not push to the database until the commit is triggered.