Using the Migrate module with MS SQL Server

This is officially the kickoff article for my blog. It has been something I wanted to start in the past, but have only found the motivation to do so recently. Now to the good stuff.

The topic of this article covers a project I'm involved in where we have to migrate data from a couple MS SQL Server DB's into our Drupal 7 install. We've decided to go with the Migrate module to knock this out. No big deal, right?

Upon a deeper dive, I've discovered in the documentation a subtle detail that Microsoft's sqlsrv module only functions on a Windows based Drupal install, which is not going to work for us. After tinkering around for a couple of days, I've come up with a rough workaround that I hope saves some time for others.

After some search, I was able to find an extremely helpful blog article that will gets the process started:

http://grover.open2space.com/content/use-php-and-pdo-connect-ms-sql-server

Read it carefully and follow the instructions up until the code examples, those won't totally help us, but I suppose they don't hurt.

At this point one additional modification will need to be made to freetds.conf. You should be able to find this in your /etc/freetds folder. When I started migrating the data I found that ntext, nvarchar(max) and varchar(max) were not compatible with freetds out of the box. So, we need to modify this conf in the global settings I add the following:

# TDS protocol version

tds version = 7.0

client charset = UTF-8

Now, let's go get the sqlsrv module, I'll do so via drush:

drush dl sqlsrv

Then copy the sqlsrv subfolder (not the module folder, but the child folder with the same name) to drupal/includes/database/ directory. This is where I'm going to be a bit lazy and retain the same name of Microsoft's module, so everything going forward will use the same name.

Next up, pop open your settings.php file to add your connection details:

 

$databases['default']['my_source_db'] = array(

 'driver' => 'sqlsrv',

 'database' => 'mydb',

 'username' => 'myuser',

 'password' => 'mypass',

 'host' => 'myhost',

 'prefix' => '',

);

Now, we have to update the database.inc file in the includes/database/sqlsrv/ folder to get this to work.

First change is in the $options array on line 52 to correctly apparent the port number for our OS:

 

   // Build the DSN.

   $options = array(

     'host=' . $connection_options['host'] . (!empty($connection_options['port']) ? ':' . $connection_options['port'] : ''),

     'dbname=' . $connection_options['database'],

   );

Then, directly following that, the first argument of the parent constructor needs to be modified to make use of dblib instead of sqlsrv per the previously linked article:

         

   // Launch the connection to the server.

   parent::__construct('dblib:' . implode(';', $options), $connection_options['username'], $connection_options['password'], array(

     /*PDO::SQLSRV_ATTR_DIRECT_QUERY => TRUE,*/

   ));

Don't forget to also comment out (or delete) that PDO::SQLSRV_... constant above.

One more change later in the file, around line 402 for me at this point. I comment out the section of code there that references the getColumnMeta function. I'll come back and update this if I come up with a better fix, but right now I don't have time for a real fix so binary columns are not going to bind correctly:

 

   /*

   $null = array();

   for ($i = 0; $i < $statement->columnCount(); $i++) {

     $meta = $statement->getColumnMeta($i);

     if ($meta['sqlsrv:decl_type'] == 'varbinary') {

       $null[$i] = NULL;

       $statement->bindColumn($i + 1, $null[$i], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

     }

   }

   */

This should complete your setup to begin using your Migrate module. There is one more point of interest here that was a gotcha for me. I got an error on the import as it couldn't locate the mapping tables on the source server b/c we are running against a different server (duh?). So, you will need to set the 'map_joinable' option in the MigrateSourceSQL constructor as such:

 

$this->source = new MigrateSourceSQL($query, $query->getFields(), null, array('map_joinable' => false));

Hopefully, someone else finds this helpful for them! Feel free to leave any comments, questions, or corrections.

Thanks for reading!