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
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.
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.
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
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.