Monday, September 14, 2009

My Experiment With Truth

You must go through the link below before reading this article on SSMA.
http://migratethrussma.blogspot.com/

In the below article I am going to walk you through various snapshots for one of the actual migration that I performed through SSMA from oracle to SQL SERVER 2005 database.


Step1 : Create Database from Sql Server Mgmt Studio wrt schema you are migrating from Oracle.






Step 2: Right Click on the source schema and - > click on “convert schema






During Conversion Process you might see the status something like below at the bottom of your SSMA screen.


Starting conversion...
Analyzing metadata...
Converting table BOBJAUD.OBJ_A_APPLICATION ...
Converting table BOBJAUD.OBJ_A_EVENT_DETL ...
Converting table BOBJAUD.OBJ_A_EVENT_LOG ...
Converting table BOBJAUD.OBJ_A_EVENT_NAME ...
Converting table BOBJAUD.OBJ_A_OBJECT_TYPE ...
Converting table BOBJAUD.OBJ_A_SITE_LOG ...
Converting table BOBJAUD.OBJ_X_DOCUMENTS ...
Converting index BOBJAUD.OBJ_X_DOCUMENTS.PKX_DOCUMENTS ...
Converting table BOBJAUD.UNV_AUDIT ...
Converting index BOBJAUD.UNV_AUDIT.AUDIT_PK ...
Converting table BOBJAUD.UNV_CLASS ...
Converting index BOBJAUD.UNV_CLASS.CLASS_PK ...
Converting table BOBJAUD.UNV_CLASS_DATA ...
Converting index BOBJAUD.UNV_CLASS_DATA.CLASS_DATA_PK ...
Converting table BOBJAUD.UNV_CONTEXT ...
Converting index BOBJAUD.UNV_CONTEXT.CONTEXT_PK ...
......


Step 3: Then Right click on the converted schema in target server window “Synchronization with database










Press "OK" on the above screen.


You would see something like below in your SSMA screen.


Synchronizing objects with database...
Analyzing metadata...
Preparing schema pdnjdwcf.dwadmin...
Preparing category Datatypes of schema pdnjdwcf.dwadmin...
Preparing category Functions of schema pdnjdwcf.dwadmin...
Preparing category Procedures of schema pdnjdwcf.dwadmin...
Preparing procedure pdnjdwcf.dwadmin.ACT_CD_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACT_CD_PROD_DAILY...
Preparing procedure pdnjdwcf.dwadmin.ACT_CD_PROD2_CF...
Preparing procedure pdnjdwcf.dwadmin.ACT_CD_VAR_RT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACT_CDCT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACT_CF_CALC...
Preparing procedure pdnjdwcf.dwadmin.ACT_CT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACT_DT_BANKRPT...
Preparing procedure pdnjdwcf.dwadmin.ACT_ID_APPL_UPDT...
Preparing procedure pdnjdwcf.dwadmin.ACT_PURCH_SUB...
Preparing procedure pdnjdwcf.dwadmin.ACTD_CALC_CT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACTD_CD_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACTD_CDCT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ACTD_CT_PROD...
Preparing procedure pdnjdwcf.dwadmin.ANEX_CF_PROC...
Preparing procedure pdnjdwcf.dwadmin.APP_CD_PROD...
Preparing procedure pdnjdwcf.dwadmin.APP_CD_PROD2...


Step 4: Verify from the SQL SERVER MGMT STUDIO for the actual migration of objects that took place.






Step 5: From the source window Right Click on the source Schema -> “Migrate Data








You can always see the status of the progress from below status bar:





Migrating data...
Analyzing metadata...
Preparing table BOBJAUD.UNV_JOIN...
Preparing table BOBJAUD.UNV_AUDIT...
Preparing table BOBJAUD.UNV_CLASS...
Preparing table BOBJAUD.UNV_RELATIONS...
Preparing table BOBJAUD.UNV_DIM_OBJ...
Preparing table BOBJAUD.UNV_OBJECT...
Preparing table BOBJAUD.UNV_OBJ_TAB...
Preparing table BOBJAUD.UNV_TAB_OBJ...
Preparing table BOBJAUD.UNV_CONTEXT...
Preparing table BOBJAUD.UNV_CTX_JOIN...
Preparing table BOBJAUD.UNV_TAB_PROP...
Preparing table BOBJAUD.UNV_JOIN_DATA...
Preparing table BOBJAUD.UNV_PROP_DATA...
Preparing table BOBJAUD.UNV_PROPERTY...
Preparing table BOBJAUD.UNV_UNIVERSE...
Preparing table BOBJAUD.UNV_CLASS_DATA...
Preparing table BOBJAUD.UNV_DIMENSION...
Preparing table BOBJAUD.UNV_PROP_TAB...
Preparing table BOBJAUD.UNV_OBJECT_DATA...
Preparing table BOBJAUD.UNV_CONTEXT_DATA...
Preparing table BOBJAUD.UNV_JOINCONTENT...
Preparing table BOBJAUD.UNV_UNIVERSE_DATA...
............
............
--Summary--
Data migration complete.
31 table(s) successfully migrated.
0 table(s) partially migrated.
0 table(s) failed to migrate.








Please go through the below sites for further information on some SSMA limitations :

1. Migration of Oracle sequences to SQL Server 2005/ 2008

http://dbbest.net/blog/?p=91

2. Troubleshooting the SSMA Data Migration Performance

http://dbbest.net/blog/

3. How to convert Oracle aggregate functions, which contain PARTITION BY and ORDER BY clauses

http://dbbest.net/blog/?p=205

4. How to convert Oracle external C functions to SQL Server

http://dbbest.net/blog/?p=165

5. SQL Server Emulation of Oracle DBMS_PIPE subroutines

http://dbbest.net/blog/?p=176

6. Noise Words and Migration from Oracle Text to SQL Server 2005

http://dbbest.net/blog/?p=142

7. How to Convert Oracle Text statements to SQL Server 2005

http://dbbest.net/blog/?p=125

8. Conversion of Oracle REF CURSOR used as OUTPUT Parameter

http://dbbest.net/blog/?p=186

9. Migration of Oracle SQL*Loader Tasks to SQL Server

http://dbbest.net/blog/?p=150