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:
- Coded a new SQL Server data layer project for my custom features, including converting from MySQL inline SQL to stored procedures.
- Built solution with SQL Server (e.g. "Release" configuration).
- Created an empty SQL Server database, and updated user.config to point to it.
- Ran setup to build out the empty database tables and stored procedures
- 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)
- 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.
- 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)
- Ran the conversion script to populate the dbo tables.
- 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.