Migration

Digg This!

Page 2 of 2   « previous page

Migrating Data
On the machine that runs SQL Server, install an ODBC driver for the new database, and set up a connection to the new database. Then, do these tasks:

• Create a DSN to export data.
• Right-click on the database and choose All Tasks->Export Data.
• Step through the wizard and on the destination page choose ODBC Data Source as the connection; then choose the DSN you created.
• Choose Copy table(s) and view(s) from the source database.
• Select all the tables (but not the views) and select Next. This is important since there will most likely be some data differences that need to be fixed. Be sure to save the DTS package so you can run it again.
• Run the export and wait until it finishes. Note: When you see that part of the migration is creating tables, you may wonder why you were instructed to do this step. The reason is that DTS table creation often fails to create tables on non-Microsoft SQL Server databases.

After that, correct any copy errors. Some of the tables may fail to copy for reasons such as binary data types being of different sizes. For each of these errors, you will need to:

• Go back into the DTS definition to the page where you chose the list of tables to migrate and click on the Transform button for that table.
• Under the Transformations tab, choose Transform information since it's copied to the destination
• In the text box, enter any necessary scripting code to modify any data incompatible with the new database.

Automated Migration
While Linux' reliability and price-performance benefits are generally acknowledged, the downtime and cost of migrating databases from legacy platforms have deterred Linux adoption. Migrating databases the traditional way (manually) can take days, weeks, even months, and is very prone to error. Fortunately, automated tools are coming to market that yield accurate results, ensure data integrity, and take a fraction of the time (a time reduction of 90%, some vendors claim).

Most automated migration software allows the original system to function normally during migration. When migration is complete, the original and the migrated system are brought into sync and, when a copy of the data is on both the pre- and post-migrated systems, activity stops briefly to switch users from one system to another. Automated systems include a variety of features, some of which you may not need. You'll want to determine whether the tool:

• Creates a standby copy of the primary database to ensure disaster recovery
• Does the migration without interrupting or impacting business processes
• Does the migration unattended (after initial setup, no further operator input is required)
• Schedules migrations to run at a specified time (maps a migration path and sets the program to run unattended based on pre-defined intervals)
• Does the migration locally or remotely over the WAN or Internet from either the source or destination database or both
• Logs the details and the results of the migration
• Handles large amounts of data (some products can accommodate multi-terabyte environments)
• Assists in modifying applications
• Lets the database be modified while migrating
• Lets operators test and validate the database in the new environment before switching over to production mode
• Replicates databases across multiple operating environments and different versions
• Creates an exact replica of the source database so the operator doesn't have to verify that the data in both databases is the same
• Provides an easy-to-use (GUI/Wizard) interface

The performance of automated tools generally depends on the several variables that come into play with most applications:

• The type of connection between the source and destination servers
• CPU utilization on both sides of the connection
• The size of the database
• The complexity of the data structure and tables (the number of tables and columns, the data type in each column, and whether the data is text or binary)

In summary, open source databases running on Linux are in many cases comparable to their proprietary counterparts and the enterprise is taking note. Of course, so are proprietary database vendors like Oracle and IBM whose databases are supported in Linux. However, there's something to be said for being able to modify the source code to fix bugs and add needed features relevant to the organization's needs. And with the right automated database migration tools, doesn't that make the case for migrating that much more palpable? Wishing you good migrations!

Page 2 of 2   « previous page