Hi all,
Has anyone moved a project from MSSQL to MySQL or PostgreSQL? What can I expect? Is it safe to assume that most queries would have to be rewritten to work with the new databases? We are scaling out our architecture and need to find cheaper DB alternative; plus, this is a large, data-heavy project, and if there is a lot of rewrite involved we may have to reconsider.
Thanks!
Oscar.
Hi @oscar.salcedo,
I haven’t had any experience migrating from MSSQL to PostgreSQL, but I have migrated from MSSQL to MySQL in the past, and I must warn you the Migration Wizard only migrates tables; you would have to provide the corresponding MySQL code for views, stored procedures, functions, and triggers.
In one of my projects I’ve switched from executing plain SQL queries on my bindings to using Stored Procedures. Since the system.db.* functions are DBMS agnostic, I could easily switch providers without changing a line of code within my project.
If your MSSQL database has a number of views, functions, and/or stored procedures, you should be warned that you would have to manually recreate them on MySQL.
And probably some of your syntax must be changed to comply with MySQL or PostgreSQL, but that all depends on the kind of queries you’re running. If they are “simple” SQL statements, they can easily be executed no matter which DBMS you use, but if you’re using some specific MSSQL functions, you must change them accordingly.
Hope it helps.
If you’ve been using Microsoft-proprietary square-bracket delimiters for identifiers, you’ll have to touch every query. If you’ve been using SQL-standard double quotes for identifiers, you’ll have much less trouble. There are a variety of functions that’ll have to be adjusted regardless (dateadd vs. date_add, f.e.), along with some logical constructs.
Great point for my Best Practices manual. Thanks!