With Sitefinity 15.0 release the support for MySql and Oracle database platforms has been deprecated, refer to progress.com release notes Sitefinity 15.0.8200 

What are important things to consider to migrate a Sitefinity site utilizing MySql database to MS Sql Server database?

The approach to take when planning migration from MySQL to SQL Server is what tooling is to be used.

A tool for the job is SQL Server Migration Assistant with details in microsoft.com migration guide mysql to sql server https://learn.microsoft.com/en-us/sql/sql-server/migrate/guides/mysql-to-sql-server?view=sql-server-ver16 and idea of the tool is to allow one to one mapping of the MySQL table to SQL Server tables. Once the mapping is done in table by table manner the tool will copy all table data from source to target.

General differences

The next thing to consider are database differences between Sitefinity MySQL database and Sitefinity site database on MS SQL Server.

There are noticeable differences in a Sitefinity database in mysql and sql server. Aside from the differences between the core SQL Server and MySQL 5.7 (specifically) the naming of tables and columns' in the database will be different.  An example is:

 

Sitefinity MySQL table names	Sitefinity MS SQL Server table names
sf_blog_posts_published_translations sf_content_items_published_translations sf_content_ items_sf_language_data sf_content_items_sf_permissions	sf_blg_psts_pblshd_trnslations sf_cntnt_tms_pblshd_trnsltions sf_cntnt_tems_sf_language_data sf_cntent_items_sf_permissions

The differences in tables storing the same type of content is the naming of tables. In MS SQL Server table names use less vowels. The removal of vowels can be observed for other tables as well and those above are just a small sample. This will be typical for Sitefinity database on MS SQL Server database and when planning the one to mapping between MySQL and MS SQL Server is the key.

The ORM software (OpenAccess which is managing the database layer for Sitefinity) will remove vowels from the name of a table to make it fit the limit (example: sf_permission_inheritance_map => sf_prmsn_inhrtnc_mp. And the ORM will always try to make the name as short as possible. This will end up in strange table names which the ORM auto-optimized by removing vowels from the name. 

Ultimately the tables which start with: sf_xxx_xxx and are Sitefinity built-in tables must all be present both in MySQL and SQL server. Just the names in mysql may miss some vowels.

Forms and Dynamic module tables

Tables for dynamic modules and forms (those tables are created based on user defined naming and don`t have prefix "sf_") all those will be name length optimized as well. And since those are not buit-in tables they are dynamically generated once a user creates a dynamic module or a form.

Sample table names: ambassadors_ambassador_countries, mapofgooddeeds_mapofgooddeeds_countries, contact_form

Those tables and table names are listed in table: [sf_meta_data_mapping]. Refer to screenshot below and this table will require attention. Ensure all table names in column [table_name] will match the actual tables names which were migrated from MySQL. As long as the tables names listed in [sf_meta_data_mapping] are present in the database, the related forms and dynamic modules will work.

Other differences

Another difference between Sitefinity MS SQL Server database and MySQL database is the presence of tables:

OAConflicts
OAImportConfigurations
oakeygen
OASyncImportSummary
OASyncLog
SyncSchedul erExecution
SyncTask
SyncTaskExecution
SyncTaskLock

those tables are not present in Sitefinity MySQL database.

Alternate approaches to database migration but using Sitefinity capabilities is to install the SiteSync module in the MySQL site and run it in Migration mode. For enabling and using SiteSync migration mode refer to Sitefinity Documentation, migrate a single site to multisite with SiteSync https://www.progress.com/documentation/sitefinity-cms/migrate-a-single-site-to-a-multisite-instance and create a new empty Sitefinity site in SQL Server and test migrating a small batch to data to check if all transfers with success and completely.

Migrate content and pages from the site
This will work if the MySQL site contains only one site in the Sitefinity instance which will be transferred. If MySQL contains multiple Sitefinity sites it will be able to transfer only one of them so in this case lets use the database data migration.