Thursday, 28 October 2010

Migration Estimation from SQL Developer

Last post we mentioned all the features of 3.0 for migration. This one, we're taking it a bit further and looking at the overview of the migration project itself.

For most users, the main thing they want to know is how much of the migration will be completed automatically. We've introduced a new spreadsheet which gets generated from the repository and gives a birds eye view of your database servers and your applications which are being migrated.

Here we have a number of migration projects in the migration navigator which are Sybase, SQL Server and DB2. We have converted all three and need to know how much of the migration the tools can do.

Very simply, we can right click on the top projects node, or any individual project node and choose to "create an estimation plan". This will generate a spreadsheet with the detail of the project with a high level summary of the state of the project.

On the summary page, there are 4 different graphs. The top two deal with the databases being migrated and how much automation the tool can provide. Specifically, they show how many objects failed as part of the migration and which area the failures occured in. The bottom two show the applications which have been scanned and show what percentage of the files which make up the application have database calls which need to be changed. This example shows the Sybase ctlib sample applications, the most of which have things to change.

This represents the first page of the migration plan. Each of the other pages in the plan break down the database migration into tables, stored procedures, triggers and view detail. The user is allowed to add specifics for their organisation which will help put together a project estimate of how long it will take and how much resource it will take to bring the database migration project to a production state.

Monday, 18 October 2010

Migration Features in SQL Developer 3.0

Since we released 2.1, we've worked with several customers and have added several great new features which have helped these customers increase the automation within their migration and the information they receive about it.

Here's a quick breakdown and we'll delve into these in later posts.

Command line interface
Most customers are looking to use a command line interface to do at least some of the migration for them, These can include all the steps that SQL Developer supports:
  • Capture - Connect to a source database, scan the data dictionary and create an independent model of the source.
  • Analyze - Analyze the model and report on what the source database contains and what issues there might be in it which need manual intervention
  • Convert to Oracle. - Convert the source model into its Oracle equivilent.
  • Generate Oracle Model - From the converted model, the user can generate the DDL for all the new schema in his database including all PLSQL
  • Build new database - The tool can run the sql produced and report on the errors which were found, even correlating between objects in the source and objects in the target
  • Move data - You can move data in two ways, either connected to the target database or you can generate unload files for the source database and SQL*Loader scripts to load this into Oracle

Enterprise Capture
This new feature allows the user to point at a server and capture all the databases in that server. This has been tested on Servers with up on 100 databases, captured and processed in minutes. Its one of the biggest requests we have had over the last few releases and this makes multi schema migration a breeze.

Application Scanning
In 3.0 we have introduced the concept of applications tied to databases. For this release we have focused on ctlib and dblib programs. Today, we can find all sybase calls in any program. We generate details reports on the contents of your application which provide information on size and complexity of the migration problem.

Estimation Reports
3.0 has introduced extensive database reports on the migration. Specifically, the reports include
  • High level object summary
  • High level error summary
  • Detailed error summary
  • Detailed object size summary
  • Detailed comparison between source objects, capture and converted objects and the new Oracle object
  • Temporary table usage
Migration Project Navigator
3.0 has also introduced a brand new project concept for Migration Projects. This is a new navigator which holds all the servers in a project all together. This has the benefit of being able to run several projects together, but report centrally on any one of them at any time with detailed information.

Migration Wizard
In 3.0, we have changed the quick migrate wizard to be a generic multi entry, single source of true path through a migration. This replaces the whole Quick Migrate concept and is now the only way to do a migration with the tool.


Parser and Translation enhancements
A lot of work has gone into fixing issues within the translators so they can perform in a better way and produce more consistent output. We are continuing to improve this with each release.


Copy to Oracle
Lastly, we have a new concept on all the third party navigators which is called "Copy to Oracle". This allows the user to copy tables and procedures from third parties into an oracle schema, without a migration repository.

Saturday, 16 October 2010

SQL Developer 3.0 EA1

After nearly a year in development, we're ready to show you what we have been doing in Sql Developer. You can try it out from here.

Over the next few weeks, I'll be going through some of the features and giving some ideas as to what this can be used for.
B