Databases are now a very essential part of many organizations. They formintegral units for storage and collection of data. However the problem ariseswhen existing systems become unusable or a higher version of database isrequired to meet rising system compatibilities. Manually transferring data is atedious scheme as the size of the data present usually borders from a fewmegabytes to a few hundred terabytes. Also when a human element is involved,the probability of errors occurring is much higher. Therefore the need for atool to successfully migrate the data is required.

Database Migration is defined as the process of translating data fromone format to another when the organization decides to use a new computingsystem or a new database management system that is incompatible with thecurrent system.

The Database Migration is markedly different from data movement. This isbecause data movement involves only movement of data. Database migration on theother hand involves transfer of data along with some change in theapplication. 

The Database Migration tool acts as an interface between the twodatabases and the user. It creates a new database on the destination databasetype i.e. MS SQL 2005 similar to the source database present on MySQL 5.0. Theschema of the two databases has to be almost alike. But the features offered bythe two databases differ from each other in many ways. Thus some manipulationsand adjustments are required to be made by the tool so as to maintain theoverall structure and functionality of the database. Thus the schema may varyat certain areas. 

The tool thus implements various mapping function to derive equivalentdata types in MS SQL Server while creating the tables. Then the constraintsplaced on these tables have to be replicated as it is on the destinationdatabase system. This is followed by migration of other complex objects likethe views, procedures, triggers and functions. This requires some highlyspecialized functions or parsers which can convert the definitions of theseobjects from MySQL syntax to the MS SQL syntax. The final step inmigration  is the  transfer of the data from one database toanother.

Database migration is a complex process and is usually avoided untilthere is no other viable option. This is because databases are usually huge andare complex. Many triggers, stored procedures and functions exist which mayrequire a competent and efficient parser for their migration. Often many issuescrop up, which require personal attention of a user having technical knowledgeof the databases.

Main Migration Steps:

• Decide how you willmap MySQL databases to SQL Server 2005.

• Convert databaseobjects; these are tables, tables constraints, views, procedures, functions,and triggers.

• Map data types fromthe MySQL data type to a SQL Server data type.

• Rewriteyour views, procedures, and functions according to SQL Server syntax.