What is database migration/ conversion?

Often, for a number of reasons , it may be necessary to move data from one database vendor to another, or to upgrade the version of database software being used.

The most trivial case of Database migration is illustrated below.

Simple Database migration scheme

The manual database migration, especially for heterogeneous databases is a difficult, expensive and time-consuming task.

Actually modern database engines have a similar set of capabilities. But their data types, internal structure and SQL syntax are not identical. The aim of automated database migration software is to convert database structures, relations and data from source db to target correctly to preserve data integrity.

Specific data types may be absent in target database or their length and precision may be different for various databases. So mapping corresponding data type equivalents between source and target databases accordingly is another job for database migration tool.

Database views, stored procedures and triggers cannot be migrated as is. The idea of our software is to translate them from one SQL dialect to another automatically with no SQL knowledge.
Here are the sample views converted with our migration tools.

MySQL View

SELECT `InstanceName`, `ReportID`, `UserName`, CASE RequestType WHEN 1 THEN CONVERT('1', BIT) ELSE CONVERT('0', BIT) END AS `RequestType`, `Format`, `Parameters`, `TimeStart`, `TimeEnd`, `TimeDataRetrieval`, `TimeProcessing`, `TimeRendering`, CASE Source WHEN 6 THEN 3 ELSE Source END AS `Source`, `Status`, `ByteCount`, `RowCount` FROM `ExecutionLogStorage`

MS SQL Server View

SELECT [InstanceName], [ReportID], [UserName], CASE ([RequestType]) WHEN 1 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END AS [ RequestType ], [FORMAT], [Parameters], [TimeStart], [TimeEnd], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], CASE ([Source]) WHEN 6 THEN 3 ELSE [Source] END AS Source, [status], [ByteCount], [RowCount] FROM [ExecutionLogStorage] WITH (NOLOCK) WHERE [ReportAction] = 1

Find more information about Views and queries translation

comments powered by Disqus