Friday, September 28, 2007

MySQL Migration Toolkit

I've been evaluating the latest version of the MySQL Migration Toolkit to work on migrations from SQLServer 2005 to MySQL 5.1

Here are some of the things I found so far:

  1. Ease of use:
    • Very simple and fast to get up and running - I was able to get multiple databases migrated from SQLServer to MySQL without any special setup or configuration changes. Gui is straight forward and intuitive.
    • Took a little work to figure out how to 'customize' the migration though, but nothing crazy.
  2. Speed:
    • Seems relatively fast. I was able to transfer about 6 million rows in about 15 minutes.
    • Would probably be more efficient if I wasn't using an intermediary stopping point (my laptop)
    • Would benefit if I used bulk loading options, but I wasn't able to determine that right out of the box quickly.
  3. Issues:
    • Needs a 'Migrate Data Only' option for just moving data
    • Better options to handle case sensitivity issues (had to modify each table/object manually for that)
    • The handling for stored connections was not that great, and I had to keep re-creating the stored connections.
    • Should have an option to just do a bulk load from a datafile if necessary
All in all, a great shortcut to porting databases over to MySQL and moving data from one data center/location to another (Prod -> Test -> Dev, etc).

Very Slick....

Wednesday, September 05, 2007

SQLServer: Tracking Down Stat Issues

The old debate of auto update statistics. I've had issues with it in the past, and coming from an old UNIX / Oracle background, I've always been of the opinion that I know best ( probably not, but it's fun to believe that).

The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not, before the tables/indexes are scanned for updating stats.

Given this, we can then get a look at distribution changes in order to determine how often we should be updating stats on different tables indexes.

The following script gives a look at how many changes have been done since statistics on an object were last updated:

SELECT a.rowcnt AS total_rows,
a.rowmodctr AS rows_updated,
object_name ( AS table_name, AS stats_object
FROM sys.sysindexes a
WHERE object_name ( IN ( [table name],[table name],...)

Then just keep track of how fast the rows_updated grows, and use that to get an idea of how often stats should be updated.