Maybe my previous question was not the correct question to ask.
I am in the middle of making an application upgrade which includes DB structure upgrade with keeping Data "data loss is not accepted here".
I have a complex DB structure in my application which depends "in some cases heavily depending" on database specific features.
I use SQL Server "2005/2008" , Oracle "10g" mainly, but MySql is also used.
current modifications include:
* new IDs created and modified, some of these IDs have foreign keys ,related to specific Field names that are new created ...
* modifications of Field types, lengths , charsets ...
here are notes about my case:
* some tables are partitioned. and some of them include LOB data.
* some tables are used as archived data "almost 100M records in some tables"
* data has to be moved as smooth as possible with NO LOSS.
My Questions are:
- Is there a best practice scenario to do this upgrade?
- as mentioned before , my application is depending on a lot of database specific features , how can migration plan be optimized to work with major 2 DB engines "SQLSERVER / Oracle"?
- Is upgrading better or creating new DB Schema and applying new structure modified? then migrate this data? in such case how can i handle sequences , ... to make sure no errors occur in runtime?
thanks in advance
sorry for the stray ,,,
have a good day all.