What are the best methods for migrating an Oracle 10g database to Microsoft SQL 2008 R2? Application uses hibernate

Question

Basically what the name says. In the future, we need to start supporting both database platforms (and start writing migrations accordingly), but we need to make the first initial “port”.

Our database administrators are confident that they can convert the schema, tables, data types, etc., but our developers are not sure that the DAO will "just work." Can someone point us to some resources that we can consider? Ideally common errors to avoid, specific tests to run, etc. Of course, we will run the full set of database tests at the application level, but we want to do as much preparation as possible.

Answer 1

Pay attention and check performance under load . Oracle does some things in a fundamentally different way than other database providers. Tom Kyte's Great Book Oracle Database Architecture expert points out several differences. A couple of highlights:

• Oracle never blocks read-only data. Many other databases do.
• The data writer in Oracle never blocks the reader. A data reader never blocks a writer. Again, many other manufacturers do.

Ignoring such things that can cause big headaches after conversion while fixing surface problems. This does not mean the superiority of one product over another, but simply means that what works well with one vendor product may fail in another case, and custom approaches based on the database may be required.

Answer 2

The same (although on a fairly simple scheme, say). "Just worked." The magic of sleep mode.

I had my peace of mind because we had 100% coverage for the DAO layer. Therefore, when the schema was recreated in MS SQL, and some table and column names were updated in the mapping (don’t remember why, but the database administrators, who are alleged to be naming), we simply run our tests and do not find any unsuccessful ones .

PS I recalled one interesting detail: functional tests were all right. But when PTE started in the MS SQL database, we found that simultaneous access to one particular table was slower than on Oracle due to the spread of locks. We had to redesign this functionality.

Answer 3

I think the first step would be to get an empty MS SQL schema, use hbm2ddl=true and let Hibernate create tables there. Then show it to your database administrators and ask if that makes sense.

Filling the data is not a problem, I would suggest that the queries will be more slippery (especially if you use JDBC in some places). You can also check query plans for frequently used queries and see if they are also understandable.