Help migrating from MySQL to MSSQL

It's not crazy at all. It's actually pretty easy to export any Drupal database in a format that can be imported in any other database engine. We did that while building a demo during Drupalcon Copenhagen and this type of normalized data import/export could be added as a feature to Drush.

Changed title, component and category, as I think this might be handled in documentation.

While a tool/module to do this is probably the most convenient for a broad audience, it wouldn't hurt to have documentation too. Here is some generic documentation for transfering data from MySQL to SQL Server.

************************************************************
The fastest way to copy the data from MySQL tables to SQL Server tables is to use the SQL Server Import and Export Data Wizard. For connectivity with MySQL server, install MySQL ODBC Connector, which can be downloaded from:
http://dev.mysql.com/downloads/connector/odbc/5.1.html

Here are the steps to perform the data migration using the wizard interface:
1. On the SQL Server machine, create system ODBC data source for MySQL database using MySQL ODBC Connector.
2. Set SQL_MODE to ANSI_QUOTES on MySQL Server.
3. In SQL Server Management Studio, right-click the target database in Object Explorer, click Tasks, and then click Import Data. The SQL Server Import and Export Wizard appears.
4. On the next page, the wizard requests the data source. Select the.NET Data Provider for ODBC and specify the DSN created at the step 1.
5. Next, set up the destination. Select SQL Server Native Client 10, specify your instance of SQL Server and your database, and then click Next.
6. Click Copy data from one or more tables or views, and then click Next.
7. Select source and target tables for the migration. If a target table has an identity field, you can enable identity insert using the Column Mappings dialog box.
8. Execute the created package. If necessary, the package can be saved and reused later.

Validating Migration Results
After the data transfer is complete, you might want to verify that all the data have been migrated correctly. It can be done using the SQL Server linked server mechanism. The following commands illustrate this method. They should be executed in Query window of SQL Server Management Studio:
1. First, you need to create the linked server pointing to MySQL database.
EXEC sp_addlinkedserver '<ServerName>','MySQL','MSDASQL', '<DSN>'
where <ServerName> is name of linked server and <DSN> is the ODBC data source name.
2. Specify the login for this linked server.
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<ServerName>', @locallogin = NULL , @useself = N'False', @rmtuser = N'<User>', @rmtpassword = N'<Password>'
Here <ServerName> is name of the linked server, and <User> and <Password> are MySQL credentials that provide read access to the databases being transferred.
3. The following query returns the invalid rows where differences between MySQL and SQL Server tables exist. Change <database>, <schema>, and <table> so that they specify the location of the table for which you want the migration to be verified.

  (SELECT * FROM <database>.<schema>.<table>
  EXCEPT
  SELECT * FROM OPENQUERY(<ServerName>,'SELECT * FROM <schema>.<table>'))
  UNION
  (SELECT * FROM OPENQUERY(<ServerName>,'SELECT * FROM <schema>.<table>')
  EXCEPT
  SELECT * FROM <database>.<schema>.<table>)
  

************************************************************

This can be tweaked for Drupal 6 to Drupal 7, or Drupal 7 to Drupal 7 as appropriate.

Hope this helps.

The problem is that the content of the database might be slightly different, because different database engines implement Drupal database types and concepts in a slightly different way (for exemple, on SQL Server we have to explicitly filter out the NULL values from unique keys because SQL Server has a NULL = NULL semantic).

The only practical path to do that is to generate a Drupal portable version of the database, and reimport it in the second implementation. That's what the Rails world has been doing for years (in the form of YAMLDB). We have nearly all the code necessary to do that, it just need to be packaged in the Drush module.

Agree with you Damien, the automated tested solution for D6->D7 and D7->D7 is the best option.
Those familiar with SQL Server can use the Export/Import wizard and advanced options to do it manually. The reason to add it here is because the issue has been marked for documentation. :)

Is this issue still needed in the "Drupal 7 driver for SQL Server" queue, or can it be moved to the drush issue queue as a feature request? I can post a new issue if you want to keep this one here.

Are there any docs / records on what you did while building the demo you mentioned in #1? That information would be very useful here.

This issue has a corresponding feature request in the Drush queue: #1052244: DBTNG-format database dump and import.

I just had success migrating my MySQL data to an MSSQL version of the database using the SQL Server Migration Assistant: (Team blog site) http://blogs.msdn.com/b/ssma/

It shows some warnings about keys over 900 bytes, but migrated 100% of my data from MySQL to an MSSQL (2008) database.

I had ODBC driver issues with SSIS, this way was much easier.

If you don't have much time to do this, try SQLyog Enterprise edition, its got an import wizard that works well, I've converted /migrated a Drupal mssql db to mysql with this without errors.

STEP 1)
First you use MySQL Workbench to migrate only the tables over from mssql to MySQL (skip the data migration).

STEP 2)
then you use SQLyog to migrate the data over from mssql to MySQL.

STEP 3)
do this only AFTER step 2 is complete.
To make the migrated (from MSSQL to MySQL) database work properly you will have to look at the dump file from the newly created MySQL database, locate all tables with '__pk'

then for each of those tables , drop the '__pk' column as follows:

Example:

ALTER TABLE `DBNAME`.`authmap` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_authname`, 
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`bean` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_vid`, 
  DROP COLUMN `__unique_delta`;

ALTER TABLE `DBNAME`.`bean_type` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`block` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_tmd`;
ALTER TABLE `DBNAME`.`block_custom` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_info`;
ALTER TABLE `DBNAME`.`contact` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_category`;
ALTER TABLE `DBNAME`.`date_formats` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_formats`;

ALTER TABLE `DBNAME`.`fe_paths_config` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_machine_name`;
ALTER TABLE `DBNAME`.`field_group` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_identifier`;
ALTER TABLE `DBNAME`.`file_managed` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_uri`;
ALTER TABLE `DBNAME`.`file_metadata` 
  DROP COLUMN `__pk`, 
  DROP INDEX `__pk`;
ALTER TABLE `DBNAME`.`filter_format` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`honeypot_user`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY;

ALTER TABLE `DBNAME`.`i18n_path` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_set_language`; 

ALTER TABLE `DBNAME`.`image_styles` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`jwplayer_preset` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_machine_name`;

ALTER TABLE `DBNAME`.`ldap_servers` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`linkchecker_link` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_urlhash`;

ALTER TABLE `DBNAME`.`linkit_profiles` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`media_oembed_provider` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`media_restrict_wysiwyg`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`media_view_mode_wysiwyg`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`metatag` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_temp_key`;

ALTER TABLE `DBNAME`.`metatag_config` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_instance`;

ALTER TABLE `DBNAME`.`node` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_vid`;

ALTER TABLE `DBNAME`.`page_manager_handlers` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`page_manager_pages` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`password_policy` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;


ALTER TABLE `DBNAME`.`password_policy_force_change`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`path_breadcrumbs` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_machine_name`;
ALTER TABLE `DBNAME`.`print_page_counter` 
  DROP COLUMN `__pk`, 
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`redirect` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_hash`;

ALTER TABLE `DBNAME`.`role`   
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`rules_config` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`taxonomy_index`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`taxonomy_vocabulary` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_machine_name`;
ALTER TABLE `DBNAME`.`users` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;
ALTER TABLE `DBNAME`.`views_view` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_name`;

ALTER TABLE `DBNAME`.`webform_component_localization`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`webform_submissions` 
  DROP COLUMN `__pk`, 
  DROP COLUMN `__unique_sid_nid`, 
  DROP COLUMN `__unique_nid_serial`;

ALTER TABLE `DBNAME`.`workbench_moderation_transitions`   
  DROP COLUMN `__pk`, 
  DROP PRIMARY KEY,
  DROP INDEX `__pk`;

ALTER TABLE `DBNAME`.`fe_paths_config`   
  DROP COLUMN `__pk`,
  DROP COLUMN `__unique_machine_name`;

and so on and so forth.

Our db had these tables that contained the '__pk' field , drop all of the ones you find and you'll be good.
the __pk and __unique fields are junk that MSSQL creates to handle tables that don't have primary keys. MySQL doesn't need these and should not have them.

Converting/migrating to MySQL worked great for us. Good luck.

ESF Database Migration Toolkit can do this job in 3 steps:

https://www.easyfrom.net/articles/mysql_to_sql_server/

It is quickly and simply.

I've had success with SQL Server Migration Assistant (SSMA) for MySQL. Current version 7.7: https://www.microsoft.com/en-us/download/details.aspx?id=54257

I use use plain SQL connections within SSMA for MySQL and MS SQL Server. After setting up MS SQL server and MySQL/MariaDB correctly with appropriate users and permissions, within SSMA project:

1. MySQL - Load data/refresh from database
2. MS SQL - Synchronize with Database (optional)
3. MySQL - Convert Schema
4. MySQL - Migrate data

This seems to handle field conversions seamlessly. Drupal versions 8.4 and 8.5

We saved 300,000$ in high availability cluster annual license fees by converting our Drupal database from MSSQL to MySQL and were able to re-utilise our licenses for other applications. Ended up creating a MySQL cluster using a master-master-slave configuration although this is overkill for most organizations.

We also put to use the autoslave module https://drupal.org/project/autoslave for automatic failover.

This also increased our compatibility with contrib and allowed us to use the native drivers in Drupal rather than the less popular sqlsrv drivers.