One rare task that many DBAs fear is that of a database migration, say from Oracle to SQL Server as an example. There are many reasons for this discomfort – including overall complexity and possible lack of experience with the new database platform. That’s why possessing robust multi-platform DBA tools like DBArtisan are popular – for DBAs managing many different database types. But can DBArtisan also assist with the database migration itself? That’s a tall order and has many challenges. Let’s see.
We’ll begin with an Oracle database which contains the Peoplesoft software schema named PEOPLE. We want to move that schema over to SQL Server into a database named PEOPLESOFT. Below in Figure 1 we have this initial state. Note that table, index and view counts for the PEOPLESOFT database are all zero.
Figure 1: Empty SQL Server PEOPLESOFT Database
To begin we simply choose the Main menu -> Utilities -> Schema Migration as shown in Figure 2. Remember that DBArtisan supports some 12 database platforms and their most recent versions, including Oracle, SQL Server, DB2 LUW and Sybase ASE. Therefore this utility can assist with any combination of source and target platforms from among all those it supports.
Figure 2: DBArtisan Schema Migration Utility
When we start the wizard we see the screen shown in Figure 3. Note that we can either start a new migration as will be done in this article or we can re-open an existing migration which we can modify and then run.
Figure 3: Migration Wizard Start Screen
The wizard then presents us with choices for the source and target databases for the migration. Note that the databases shown here in Figure 4 are simply the ones we’ve already defined on the data source explorer. In this case we’re migrating from Oracle 11gR2 to SQL Server 2012. While both of these are somewhat older version of their respective databases, the migration process would be identical for Oracle 12cR2 or SQL Server 2016.
Figure 4: Migration Wizard Source & Target
Next for the target we must define the database to migrate the objects into as shown in Figure 5. Note that not all database platforms support the concept of a “database”. So for a different target this screen might display something different. For example had the target database been Oracle this screen would have asked what tablespace to migrate into.
Figure 5: Migration Wizard Target Object Destination
Now we arrive at the critical stage of the migration, the schema and object selection step shown in Figure 6. Here we have selected just the PEOPLE schema and its tables, constraints, indexes and views. The more we select here the more work the wizard will have to do and hence the longer it will run.
Figure 6: Migration Wizard Schema & Object Selection
Finally the migration wizard presents us with a plethora of options to control the migration process. Note that we indicated that the owner should be changed and changed to “dbo” as shown in Figure 7. Keep in mind that different databases often handle the concept of logins, users, schemas and owners differently. In this case the Oracle PEOPLE schema is being migrated to the “dbo” owner.
Figure 7: Migration Wizard Advanced Options Selection
Upon pressing finish we now see the migration wizard progress display as shown in Figure 8. Wow – we migrated roughly 15,000 objects in less than 10 minutes! Yes there were some errors. But anytime one migrates a database from one platform to another some things do not map. For example one error shown is that an Oracle view definition references the SUBSTR function which has a different function name under SQL Server. So yes, there will always be a few things that still must be done by hand.
Figure 8: Migration Wizard Progress Display
After exiting the wizard and returning to our starting point as shown in Figure 9 we now see that there are 4,605 tables, 5,789 indexes and 3,126 views in our SQL Server database now. Thus we’ve migrated the bulk of the objects by pressing a few buttons and waiting under 10 minutes. If we had to do this work manually we’d easily have to spend a week or more to achieve the same results. DBArtisan saved us both time and money. But that’s exactly what good tools are for – making us more productive.
Figure 9: Migration Process Final Results