Migrate from SQL Server to Oracle in .NET using Devart

By -

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 4.0.0.12.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.

 

 

Comments

Post a Comment

Popular posts from this blog

WinDBG on 32Bit Applications

By -
On a 64-bit Windows installation is it possible to make both 32-bit and 64-bit dumps of 32-bit processes. The task manager will create a 64-bit dump, which therefore is often what you end up with users sending you. This is not a problem for native executeables since you can still load it in windbg and use the !wow64exts.sw extension to switch into the 32-bit view. However if your process is a .NET process and you want to use SoS to investigate it then you are out of luck, you'll just get the message "SOS does not support the current target architecture." This extension gets around this by hooking/patching functions in dbgeng.dll so that SoS thinks it's working with a 32-bit dump. ** Usage ** Copy soswow64.dll into the "winxp" subfolder of windbg. Then after loading a 64-bit memory dump of a 32-bit process you can simply load the extension: 0:000> .load soswow64 Successfully hooked IDebugControl::GetExecutingProcessorType. Successfully patched DbgEng!X...
Read more

EXCEL Macro - Compare Column A to Column B

By -
Recently a friend asked me to fix a macro that she was working on to compare two columns in Excel and if the cell in Column A did not exist in any cell of Column B, then add it to Column C. You can make check both columns for miss matches by copy/pasting Column B to Column A and vice versa to check both lists and maybe someone will find it handy =) Sub Subset() Dim Acell As Range 'Acell is a range of 1 or more cells Dim Bcell As Range 'Bcell is a range of 1 or more cells Dim Ccell As Range 'Ccell is a range of 1 or more cells Dim Index As Integer 'Index is the current row we are saving to in the Ccell Index = 1 'Start the Index at 1 Dim found As Boolean    'Remove the hidden space 160    Range("A1:A1000").Replace What:=Chr(160), Replacement:="", LookAt:=xlPart    Range("B1:B1000").Replace What:=Chr(160), Replacement:="", LookAt:=xlPart    For Each Acell In Range("A1:A1000")     Acell...
Read more

Powershell Script to Automatically Deploy Sharepoint WSP Packages

By -
Recently I wanted to automate the retraction, removal and deployment of WSP packages in a folder. So I wrote this powershell script. NOTE: There is a bug in the script that returns a false positive. The property $solution.Deployed returns true even though the object is not finished deploying or is locked! I will update this code or if you do then please send your updated code and I will update my post. $packages = (dir *.wsp | Select-Object name) $currentDir = (Get-Location).Path Add-PSSnapin Microsoft.Sharepoint.PowerShell -ErrorAction "SilentlyContinue" Write-Host "Started package installation" foreach ($i in $packages) { Write-Host "Retracting: " + $i $solution = (Get-SPSolution | where-object {$_.Name -eq $i.Name}) Write-Host $solution.Name if ($solution -ne $null) { Write-Host "Solution Found..." if ($solution.Deployed -eq $true) { Write-Host "Uninstalling..." Uninstall-SPSolution -Identity ...
Read more