These are the points i learned when i tried to change my DB from MySQL to MS SQL Server 2014 for a C# asp.net core MVC application on a Windows 10/11 platform x64. There maybe more to it but my application runs without any issues after resolving all these, Not sure about future surprises. Will update this content if i encounter anything in future.
converting mysql to sql server db
1) take mysqldump with –compatible=ansi parameter, there is no mssql parameter available.
2) mysql text is not compatible with sql server text when issueing where condition from application error will thrown, make sure to change text to varchar on required columns
3) mysql lock/unlock table is not available in sql server
4) mysql does not have constraint keyword, so make changes appropriately like this use constraint keyname unique (fieldname) instead of
unique key (fieldname) generated by mysqldump.
5) mysql is more forgiving on brackets , select * from emp where (firstname like ‘%a%’) or (lastname like ‘%a%’) will work in mysql , but retrieve all the records in sql server. so enclose the or in brackets.
6)ifnull should be converted to is null
7)datetime field defaults to 01-01-0001 in mysql but 01-01-1700 in sql server which causes overflow error.
Sansknowledge's Blog 