mojoPortal database platform conversion

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
3/25/2022 11:08:15 AM
Gravatar
Total Posts 1204
Proud member of the mojoPortal team

Help support mojoPortal!
Add-on modules

mojoPortal database platform conversion

For anyone who is interested, it's still in test mode, but I believe I have successfully converted our mojoPortal installation's database from MySQL 8 to SQL Server 2019.

Here is the outline of the process as I did it:

  1. Coded a new SQL Server data layer project for my custom features, including converting from MySQL inline SQL to stored procedures.
  2. Built solution with SQL Server (e.g. "Release" configuration).
  3. Created an empty SQL Server database, and updated user.config to point to it.
  4. Ran setup to build out the empty database tables and stored procedures
  5. Used Microsoft SQL Server Migration Assistant for MySQL to convert the production MySQL database into a separate schema in the empty database (e.g. mojoportal versus dbo)
    1. This step was needed because SSMA can't directly convert the GUIDs stored as strings in MySQL to the UNIQUEIDENTIFIER data type in SQL Server.
  6. Built insert...select statements to convert every table from the mojoportal schema to the equivalent dbo schema, using CAST for UNIQUIDENTIFIER fields. e.g. CAST ([SiteGuid] AS UNIQUEIDENTIFIER)
  7. Ran the conversion script to populate the dbo tables.
  8. Lots of testing and fixing of my custom features, but mojoPortal itself worked flawlessly!

Currently it's running in our test environment and hopefully being pounded on by our web technician and content admins. Assuming testing is successful, I'm hoping to do the production conversion within the next two weeks.

Joe, for what it's worth, I think the only discrepancy I found between the two databases was there was a single field on one table that was named differently (single in MySQL, but an S on the end in SQL Server). I should have made a note for you, but it was such a small thing I just fixed it in my table copy script and moved on.

If anyone is interested in doing this, I'd be happy to answer questions or provide the more detailed steps I followed.