What to use the
ODBC-import tool for ??
The SQLyog
Migration Tool is equally well suited for these two situations:
-
Migration to MySQl.
-
(more or less)
permanent coexistence of MySQL and other databases within an organisation.
At first
sight, "migration" and "permanent coexistence" may seem as
different as things can be. Migration is in principle a one-time thing.
Permanent coexistence the contrary – it is permanent! However, in real life I
believe it is not two discrete situations but rather a continuous scale with an
unlimited number of "intermediates" possible. Because migration takes
time to complete, it should be planned. A "small scale" pilot-project
will often be done; some subsystems may be implemented and tested before
proceeding to the next subsystem etc. In addition, the organisation must be
trained in the use of new or modified applications, maintenance procedures etc.
Migration
to MySQL:
A general
allegation about the MySQL database is that it is "effective but not very
advanced." You will hear that often among Systems Administrators. For that
reason, you will also often hear that MySQL is "fine for use with the web
– but it does not have the features that we need for our LAN-based
business-critical applications."
It is true
that with older versions of MySQL a lot of organisations and companies missed
features such as appropriate localization features (Time Zone and Charset
management for instance), not to mention features such as Stored Procedures,
Triggers and Views. That is history! MySQL has implemented all that, although
some of it is still only available with MySQL version 5, which is still in
Beta. Many organisations of all kinds have realized that and have started a
migration process from some other database to MySQL. The main reason for this
are the low cost of ownership of MySQL (various licensing and certification
models are available – even a completely free one).
Some - and
not so many - years ago migration would involve exporting data to a
.csv(text-)file and importing to the new database – one table at a time. This
simply is not acceptable any more – people are too busy and it is too expensive
for that kind of work. Organisations and companies need migration methods that
are more efficient!
The SQLyog
Migration Tool has everything you need for a successful migration to MySQL - no
matter whether you intend to accomplish it over a weekend (!) or it is a
year-long process. With The SQLyog Migration Tool you can import your data to
MySQL (that should be obvious since that is basically what the tool is for!),
but you can also create "test cases" with subsets of data, and
perform the migration stepwise using the filtering options. And if should you
happen to have a lot of old data in your old system that is not relevant, you
can use filtering to "clean" the data to import as you like.
Moreover,
it is all implemented in a very intuitive GUI-tool. However if you prefer
handwritten XML-code, you can do that as well.
Permanent
coexistence of MySQL and other databases
For the
(more or less) permanent coexistence of MySQL with other database systems, the
trigger –functionality is particularly useful. In combination with the use of
WHERE-filtering of data, it lets you perform an "incremental import."
The principles of this are described in the SQLyog help-file, and a more
extensive case-study is planned for another article (that I yet don't know
where will appear!), so I shall not go further into this here.
Instead,
let us sketch out a few simple and very realistic situations where
"permanent coexistence" would occur
-
You depend on one or more
applications that run on one database system only (and not on MySQL).
Application(s) that are so special in their nature, that there is no realistic
alternative – at least for some time or at a price that you are willing to pay.
-
You use MySQL for web
(would typically be with some remote web hosting provider) and have no actual
plans of migrating the database servers serving your LAN to MySQL.
-
You want to start a
Migration Process with the least critical or the least complicated applications.
When that is accomplished, you want to wait for some time while deciding if
more applications should be migrated. In addition, you want to be sure that you
and your staff can manage MySQL before migrating the more difficult and
business-critical applications.
-
You sometimes have
employees working "disconnected" (could be from their home address or
while travelling). While disconnected they use
"personal"/office-suite databases like Microsoft Access for working
with data. You want to import that data to your MySQL database at intervals
and/or when possible, no matter if it is new data or data that has been
changed.
The SQLyog
Migration Tool has all the options needed to handle these and similar
situations. Once the appropriate job-files are built, you can execute them
again and again, using exactly the general options, transformation options,
filtering options and triggers to ensure the correct flow of data from
practically any database in the world to your MySQL server. In addition, you have
the option to combine ODBC-import with synchronization of MySQL servers, run
maintenance scripts using the SJA "Notification Services" and
performing automated backups. It is all implemented with the SJA.
MySQL and MySQL – a note
on use across different MySQL versions.
Although
the Migration Tool was primarily designed to import data from other databases
into MySQL, you can also connect to one MySQL database with the MySQL
ODBC-driver from the Migration Tool. When this type of connection is done, this
MySQL-database is just an "ODBC-source" like was it any other
ODBC-compliant database. That is very practical since there are quite a lot of
differences between the various MySQL-versions. Especially versions up to 4.0.x
vary quite a lot compared to later versions (4.1.x and 5.0.x). User management,
implementation of DATE and TIME variable types, implementation of the
concat-ws() function, management of charset/collation and other localization
issues are examples of this. Those differences between MySQL-versions might
prevent you from successfully using the SJA Synchronization Tool and the
"copy to other host" functionality of SQLyog if the MySQL versions
are "too different." Even importing a dump from a MySQL version 5.0.x
server into a MySQL 4.0.x server is not possible unless you edit the dump-file
in an editor. However, with one MySQL-server connected through ODBC as the
"source" and another MySQL-server connected with the native MySQL
C-api (as compiled into SJA code) as the "target" you can overcome
these incompatibility-issues across MySQL-versions.
Conclusion
You cannot
tell a truth too often so let's quote from the introduction!
The SQLyog
ODBC-import tool is the most complete, versatile, intuitive and flexible import
tool that exists and that you can imagine. All the options that the tool offers
are available from the SQLyog GUI. There is no need to edit some 'strange
format' file to transform or filter data during import. It lets you easily
import any subset ("vertically" or "horizontally") of data
and metadata. It can be scheduled, can be run from external applications, can
be run in batch-mode in combination with any other OS/system command, executable
file and any other job type of the SJA. The XML-format of the jobfile control
lets you integrate the full functionality of the ODBC-import tool into your own
applications if that is what you want.
More
Questions? OK, I'll answer this one too! No, unfortunately it does not brew
coffee!
About the author.
Peter
Laursen is a Dane and a Mechanical Engineer. He specializes in Quality
Assurance, Environmental Management, and various IT-issues. He has
experience with some very different types of database systems, primarily (in
the chronological order that he used them): DATAFLEX, IBM AS-400, Microsoft
Access and MySQL. He is a MySQL and SQLyog devotee!
» 