Migrating Database From MySQL to MsSql
Hello,
I just made an attempt at migrating a Sitefinity site using a MySQL database over to MsSql, but I am running into a few issues with key constraints. I am wondering if anyone has any suggestions on how I might be able to complete this migration successfully.
Here are the steps I have followed thus far, along with the issues I am currently experiencing:
1. Install Microsoft SQL Server Migration Assistant for MySQL
2. I used mostly default settings when migrating the MySQL table structures over to an empty MsSql database
3. After the table structures were migrated and synched over to MsSql, I migrated the data from the MySQL Sitefinity database over to the newly created MsSql database. This completed with all items showing 100% success rate
4. Next, I ran through all tables in the MsSql database that the migration assistant created, and updated the following data types (to match what Sitefinity expects to find):
a. varchar(40) --> uniqueidentifier (only on ID columns)
b. binary(1) --> tinyint
5. Lastly, I modified the connectionString found inside /wwwroot/SITENAME/App_Data/Sitefinity/Configuration/DataConfig.config to point to the MsSql database instead of the MySQL database
After all of that was complete, I tried load the site, but ran into the following exception:
The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
Could not drop constraint. See previous errors.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Telerik.OpenAccess.RT.sql.SQLException: The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
Could not drop constraint. See previous errors.
Here's the stack trace:
|
Hi is there any answer to this post? kinf od need it the soonest
Thanks,
Syarif
Hello Syarif,
A migration from one database platform to another might work, but is not officially supported. We haven't tested such a migration, which means doing so could carry potential risks to your site.
One idea of how this migration could be possible is to transfer the data from one Sitefinity site to another via API usage. By creating a Sitefinity site with a MsSql backend database and then, using the Sitefinity API, get data from one site and make it available in the other site. This could be done by connecting the providers for modules and make them available in the MsSql-backed site.
For example, to connect the News module providers between the two sites:
1. Add a connection string to the MySql site by going to Administration > Settings > Advanced > Data > Connection strings and create new connection string "Test".
2. Go to Administration > Settings > Advanced > News > Providers and create new provider, copy the settings from the default NewsProvider and add two Parameters to the new provider:
Key: applicationName
Value: /News
and another parameter:
Key: connectionString
Value: Test (this is taken from step 1)
Restart the application and now the news module will have the data available from the other database.
With this to transfer the data from the other database to this one, by calling the provider. For example:
NewsManager.GetManager("MyNewNewsProvider");