In November 2022, the Drupal community and the Drupal Security Team will end their support for Drupal 7. By that time, all Drupal websites will need to be on Drupal 8 to continue receiving updates and security fixes from the community. The jump from Drupal 7 to 8 is a tricky migration. It often requires complex transformations to move content stuck in old systems into Drupal’s new paradigm. If you are new to Drupal migrations, you can read the official Drupal Migrate API, follow Mauricio Dinarte’s 31 Days of Drupal Migrations starter series, or watch Redfin Solutions’ own Chris Wells give a crash course training session. This blog series covers more advanced topics such as niche migration tools, content restructuring, and various custom code solutions. To catch up, read the previous blog posts Custom Migration Cron Job and Migration Custom Source Plugin.
This blog uses the 8.x version of the sqlsrv module. For Drupal 9+ implementations follow updated documentation from the module.
Most often in Drupal 8, your migration source will be a CSV file, JSON file, or another Drupal database. In some cases your Drupal website needs to coexist in a larger infrastructure. Thankfully, there are various modules for synchronizing Drupal with tools like Salesforce, Bynder, and GatherContent. However, not everything is as clean as those user-friendly modules. This article will dig into migrating data from a Microsoft server using Transact-SQL into a standard Drupal 8 website.
As with any database in Drupal, it starts in the settings.php file. The basic setup for a Transact-SQL database looks like this:
$databases['YOUR_DATABASE_NAME']['default'] = array (
'database' => '',
'username' => '',
'password' => '',
'prefix' => '',
'host' => '',
'port' => '',
'namespace' => 'Drupal\\Driver\\Database\\sqlsrv',
'driver' => 'sqlsrv',
);
“YOUR_DATABASE_NAME” is the key Drupal will use to reference this database, but it does not need to match the actual database name. The other credentials such as database, username, password, prefix, host, and port, need to be filled out based on your specific setup and server, but the last two keys are more general and refer to the type of database.
By default, Drupal uses a MySQL database, so the “driver” field is typically set to “mysql.” However, Drupal by itself does not know how to communicate with a Transact-SQL database, so just setting the “driver” to “sqlsrv” will throw an error
To provide that support, first install and enable the SQL Server module (sqlsrv). But the “drivers” folder in the SQL Server module needs to be accessed at the Drupal root folder (usually called “web” or “docroot”). There are two ways to do this:
- Manually copy the “drivers” folder from the SQL Server module (modules/contrib/sqlsrv/drivers) into the Drupal root folder.
- Create a symbolic link (symlink) to the “drivers” folder from the Drupal root folder with a command like this “ln -s modules/contrib/sqlsrv/drivers/ drivers”. The symlink allows the module to update without manual adjustments.
With the proper credentials and connections, Drupal will now be able to read from the Transact-SQL database.
Now the actual migration can be written. There is no core migration source plugin for this, so you will need to write a custom source plugin that extends DrupalSqlBase. Use Drupal’s dynamic query API to get the database's data, ensuring at least one field can be used as a unique identifier for each row. Once the source plugin is written, the rest of the migration will work as usual.