Data Loader


Database Migration v/s Data Migration

data migration

Many people confuse Database migration with Data Migration. Data migration is the process of transferring data from one storage type to another storage type or from one file format to another file format. Whereas, database migration most of the times means migrating RDBMS database from one database server to another database server or between different DBMS types.
Examples of database migrations

Change of Server

You have a database running on one server for a long time and now it has grown many folds since it was launched because of daily transactions occurring. The system, where it is running, is considered to have good hardware specs at the time of implementation when database size was small. But over the years the database size has increased several times and those hardware specs which were thought to be good enough now seems to be inadequate for the current database size. This exhorts the management to go for a system with higher specifications with Gigabytes of RAM, Several Cores of CPU, Solid State Disks (SSD) instead of normal HDD. An SSD has access speeds of 35 to 100 microseconds, which is nearly 100 times faster. This faster access speed means programs can run more quickly, which is very significant, especially for programs that access large amounts of data often like your operating system. This, In fact, has become one of the main reason for migration recently

After setting up of the hardware and Operating System the main challenge would be to transfer the database from old server to new server with least amount of downtime.

Change of Version

As the DBMS vendors releases new versions frequently, some are minor and some major. Minor upgrades are quite easy for DBA's to apply as they have to just run some patches. But major updates require a lot of work and many DBA's tend to postpone applying major updates as much as they can afford. But once DBMS Vendor stops supporting the current version it becomes inevitable to upgrade to the latest version.

Change of DBMS

data migration

Sometimes in rare situations, the DBA's have to switch to an altogether new DBMS, like for example from MSSQL to MySQL or from Oracle to MSSQL or FoxPro to Access. This is one of the toughest migration, because not only the DBA has to migrate the data, but one also has to migrate the Triggers, procedures. The migration of Triggers, Stored procedures are quite difficult because of Syntax Changes, like for example if you are migrating from Oracle to MSSQL, in Oracle the stored procedures would be written in PL/SQL language, whereas, in SQL Server you have to write the procedures in T-SQL.
It's a time-consuming migration and requires a lot of planning and patience. It also requires the use of cross-database migration tools such as Microsoft Export & Import Wizard, MySQL Migration Wizard - part of MySQL Workbench or third-party tools like Data Loader.
Some DBA's take the intermediate path, i.e. they first convert the data from the source database into CSV / Flat files and then upload the data from these CSV / Flat files to the Target database. While this approach looks quite simple, but it takes a lot of time particularly if the database size is too large, Also this approach is not workable if the table contains RAW or LOB, IMAGE columns.

The better choice is to use some tool which can directly read the data from Source database and converts and migrates it directly to the Target database.

Since there are many tools available in the market, please pay attention to the following features while selecting a tool

  1.  Check that it can directly connect to Source and Target databases using the latest OLE DB Providers. Why I am stressing this point is that there are many tools which use only the old ODBC drivers. Although there is no problem in ODBC, they do not support the native Bulk Load method which is several times faster than the conventional insert method.
  2.  See it can load the data into already existing tables and supports column mappings. This is one of the most important features and is not supported by many tools. This feature though looks redundant while exporting the database for only one time, it becomes very handy when the data export of a table has stopped and you don't to resume from the beginning.
  3.  Bulk Load method. Check that the migration tool can use the Native Bulk Load method for loading data into the Target Database. As you must be already aware that, BULK LOAD is several times faster than the conventional insert.
  4.  Check that the tool can also generate SQL dump files from the source database to the target database format.

One tool which meets all the above criteria is Data Loader, you can download it from here Download Dataloader . Besides having the above features, it also supports Synchronization between cross databases. Like it can synchronize a MySQL table with an MSSQL table, or it can synchronize an Oracle table with a MySQL tables.

The best thing is it comes with its own scheduler so that it automatically synchronize the data at frequent intervals.